I know this is kind of a unique case, but I’m wondering if anybody else has ran into this or has thought. The ability to have a choice component with a Display As value and a separate value written to the sheet is great and I’m glad we have that option now. What I’m wondering is if anybody has ever had the case where they would like to store both values in a sheet. In my case I have several different lesson Lengths that can be selected from when adding a lesson. Right now i store that actual Display As value to the sheet and then obtain the associated billing rate for that lesson length. I display the actual value that you see in the choice component because that is what I later display to the user along with the related billing rate for that lesson. The problem I have now is that if the billing rates ever change, then all of the old lessons will be updated. I could write the actual billing rate to the sheet instead so it is permanent, but then I don’t have the text of the Lesson length to display to the user. In my case, I’d like to store both. I could easily work around this by concatenating the Display As value with the Rate in the hidden Value and save that to the sheet where a formula will split it apart, so I’m not limited by this, but thought it would be simpler to save the both values to the sheet right away when the form is submitted. Just curious what others thought.
Yes. I had thought about this too. Recent scenario is to display times but write the time value. I get around it by doing a relation to the choice list and then looking up the value that I want. It doesn’t appear in the sheet, but it’s instant data.
Jeff, I’ve dabbled with this only in a superficial way. With a recent app, I tried to create a pin pad of sorts with two separate choice components 0-4/5-9. I then tried to use these numbers to fill the 123 component. Sadly, didn’t get anywhere. Only could store “one” single digit. If only it allowed us to store several values, it would not only solve your immediate problem, but could also help us build makeshift number pads.for entering pin codes.
Sorry, I don’t have an immediate solution but I am at it.
Only a 2-step form would work in my opinion. Write the choice to a user-specific column, have a single lookup to write the other value to another column, then have a form button writing both of them to the sheet.
Within a Form is very difficult, the Forms are a kind a sandbox and there are few options to use. My idea using a relation needs to be out of a form.
Are you able to carry out your case in an edit view and later use a form?
Saludos Jeff
Another use case. I have a ratings choice component. I want to write the numerical value AND the stars at the same time. Now, I have to do a vlookup in the sheet to get the numerical value to display in the sheet. I can do a relation/lookup in the data editor to get instant results. Double the work.
Right now I write the Lesson Length to the sheet from the form. In the sheet I’m using an arrayformula and a vlookup to obtain the matching rate. I could do this within Glide with a relation and lookup, but for various reasons and current glide limitations, I need the rate in the actual Google sheet to help calculate billing totals within a certain date range in other sheets in the spreadsheet. I’m slowly working to optimize and get as much as possible into glide with glide functions. The 2-step method is definitely an option to consider. I think what I might actually do though is create a template of the rate and lesson length, display only the Lesson Length text in the choice component list, but write the template Rate/LessonLength value to the sheet. Since I’m already performing formulas in the sheet, it shouldn’t slow anything down to perform a split on the value that’s written to the sheet and split out the rate and the lesson length text into separate columns. It’s at least a good enough temporary solution. In my case, most of this doesn’t require timely results and I’m ok with a bit of lag from the sheet.
Like I said before, my main goal is to have the rate value set in stone on all existing lessons that have been written to the sheet. Right now if I ever update the rates, then it will retroactively update all of the old lessons and screw up all of the billing. It hasn’t been an issue yet, but will be someday if i don’t do anything now.
I appreciate all of the feedback. It’s giving me some ideas.
This feature would be such a welcomed add on.
How about having a script that whenever a new row is added, copy the value from the VLOOKUP column to a new “set in stone” column so that it won’t ever be changed?
That’s a possibility too. I have a custom rate field that can be entered by the user and overrides the rate determined by the vlookup. I’ve considered a script to “lock in” the rates for a user before the rates are updated, by copying all of the vlookup rates into the custom rate column. I haven’t really decided how I want to handle of it yet. The situation hasn’t come up yet to see how I want to do it.
I am assigning costs to projects according to an hour cost per worker. Any modifications in the cost hour rate will also change historic projects.
Same with product prices and historic orders.
Storing 2 variables in forms would be perfect. In the meantime… scripts.
I find myself needing this feature in most apps these days. If an app owner needs the data presented in the google sheet, the only option is still (I think?) to write a vlookup formula or script, which isn’t ideal.
It would be great to be able to write 2 values to the sheet simultaneously when a choice is made in a form.