I am looking for guidance to a problem that I am facing in data entr; I have two (2) sheets where
Sheet 1 has Play Title (key), Play Description (One Record / Row)
Sheet 2 Has Play Title (key), Actors (Multiple Records / Rows)
Where the relationship between Sheet 1 and Sheet 2 is one to many.
So, my questions are:
How do I handle that in the Data Entry screen (i.e. One Play Title is entered and then all actors within that Play title are entered in a separate sheet)?
How do I ensure that the Play Title from Sheet one is correctly entered/transferred/copied into Sheet 2?
How do I prompt users to enter multiple-rows in one screen?
You need a third sheet to hold the relations. Then while on the Play detail screen (Sheet 1) you have a Form Button that saves to that 3rd sheet. You can only add one actor at a time however. You could use a choice component to select the actor from a list of actors. You could use a Column component to, pointing to the Play Title (key) and save that along with the Actors key to that 3rd sheet.
Thank you @George_B . I think I understand the solution but not sure how to translate it in Glide. Would it be possible, to have a demo? Thanks in advance and Happy New Year!
Thanks @George_B, truly appreciated.
A sample data was populated in the attached sheet to further demonstrate what I am trying to accomplish. Again many thanks for the assistance. Play Sheet Structure
How do you plan to display the information in the app. What Iām thinking is to maybe create 20 columns for each play (Actor 1, Actor 2, Actor 3, etc.) Then when editing the play details you could create a choice component for each actor column. When displaying the play details, you could use the created array of Actor as a relation to the actors sheets, so you could display a list of all the actors in that play and select each one to cure the actual details.
So to start off I needed to populate the UUIDās with data so I used this online tool to generate UUIDās https://www.uuidgenerator.net/
Next step was to ānormalizeā the data. I linked all the UUIDās and then deleted the columns that had the Play Name in all Sheets except the Play sheet.
There is a short coming of Glide at the moment in my opinion with the Choice Component where you canāt have is show one column but save the data from another column to the destination sheet column. So you canāt fully ānormalizeā the Actors List and have to save the name of the actor instead of the actors UUID.
Anyway then I created a few columns in Glide to link everything up and do a few lookups. Once you copy the app take a look at the Data tab and you will see those within Glide created columns. The start with rel_ (Relation) or lu_ (Lookup)
Thank you ! I truly appreciate taking the time to put together the solution into a demo. I know fully understand the concept of relation and how it can be used interchangeably. Again, thank you and Wish you a happy new year.
Youāre welcome. @david@Mark@Jason This is a good example of why we need the Choice Component to have keyed pairs like this post describes. Choice Component - allow key display pairs
Instead of using the Form component for this app one could have just used plain old adds for any of the various sheets. Then for any and all data elements that have links via keys it would be a simple matter of adding multiple choice components. So again for this example the Actors sheet would only need two columns in the Google spreadsheet, the UUID key for the Play and the Actor. All the other information could be had in the application via Glide created columns for Relations and Lookups. Using something like the actual Actors name for the key is not a good idea in any relational database, which is ultimately what the data is being changed into.
I think we will completely reinvent Choice so that it picks one or more related rows, like a āRelation Choiceā. Then when picking you can show the list of options any way youād likeāas tiles, even a map or calendar.