LinkedIn
Twitter
Hi.
My primary data table references other tables.
When I generate a new entry, the app returns data from the correct column of the referenced tables, but then it saves the row key(s) from those tables in Google Sheets when the form is submitted.
How do I get the app to save the relevant column from the referenced table rather than the key?
Here is a dummy app showing the issue.
It's a basic project diary.
The user selects the project by name.
The form then Auto Computes the project number based on the project name, by referencing the Projects table that contains the project number.
The form also filters the Stages table based on the project name, so that the stages of a particular project are retrieved and displayed as an enumerated list.
This all works fine on the user experience form.
But when the user submits the form, the project number key and the stages keys are saved, not the project number and stages themselves.
You can see this with the last record below for project name, and last few records below for stage names.
I'm sure there is a simple answer, but I just can't find the documentation.
Please note that I can't simply make the stage names and project numbers the keys in the Projects and Stages tables as there will be duplicate stages and duplicate project names.
Thanks in advance!
Solved
1 ACCEPTED SOLUTION
Thank you for the reponse, Suvrutt_Gurjar.
Looking at the Row labels and keys sample app, it appears that there is no way to save the row label in the Form table instead of the key.
This makes sense, because it's the unique key that relates the Form table record to the Person table record.
The label can't be saved to the Form table instead of the key, because labels are not necessarily unique.
With that in mind, I settled on the following solutions in my dummy app.
For project names, I set the project name as the key in the Projects table and stipulate that project names must be unique.
The best option that I could find to return the Stage label in the Diary table was to add a new column to the Diary table that returns the Stage labels as an Auto Compute based on the Stage keys listed in the row.
This was quite simple, as my Stage keys were a concatenation of the project number and the stage number.
I used an Auto Compute spreadsheet formula that removed the the project number and left the stage number.
See screenshot of the table and formula below. New column in red.
If you know of a better way to achieve this outcome, let me know.
Thank you for your help.
Thank you for the reponse, Suvrutt_Gurjar.
Looking at the Row labels and keys sample app, it appears that there is no way to save the row label in the Form table instead of the key.
This makes sense, because it's the unique key that relates the Form table record to the Person table record.
The label can't be saved to the Form table instead of the key, because labels are not necessarily unique.
With that in mind, I settled on the following solutions in my dummy app.
For project names, I set the project name as the key in the Projects table and stipulate that project names must be unique.
The best option that I could find to return the Stage label in the Diary table was to add a new column to the Diary table that returns the Stage labels as an Auto Compute based on the Stage keys listed in the row.
This was quite simple, as my Stage keys were a concatenation of the project number and the stage number.
I used an Auto Compute spreadsheet formula that removed the the project number and left the stage number.
See screenshot of the table and formula below. New column in red.
If you know of a better way to achieve this outcome, let me know.
Thank you for your help.
