Sorry to take your time. The thing is, I have a relation column but Glide says ‘the sheet doesnt have a relations column’, so I can’t choose the lookups I see in my Glide Sheet through the relation. That’s where I’m puzzled.
Mmm I don’t think I understand. But let me not waste more of your time!
@Robert_Petitto @Jeff_Hager switching to multiple relations did the trick, thanks a lot!
But I don’t understand the reason, because I need only one relation 
Well whatever! Thank you!
2 Likes
You shouldn’t need the lookups. If your relation is a multiple relation, then you should be able to build the collection by pulling the columns out of the relation as opposed to the lookups.
You are trying to plug values from a single column in a single row, into a component that’s designed to work with multiple rows.
The important thing is to understand database design. Each table should serve a specific purpose, or hold just the information that needs to be unique to each row. One you start to get into on-to-many relations, then you need more tables, because the related data can encompass multiple rows that are related to a single parent row in the parent table.
For example, I have an app with a list of students. Each student has multiple lessons, multiple tests and multiple competitions. The number lessons, tests, and competitions is open ended, so I have separate tables to store each lesson/test/comp for each student in separate rows. This design allows for limitless scaling of data. If I tried to store everything only in the student table, then it would be a nightmare. It might seem like a simple no-brainer in the beginning, but for the long term, it’s better to understand how to properly lay out the data.
I think database design is something Glide should have better documentation on. Sure you can plug in any sheet and get something out of it, but it might not be a good long term solution.
1 Like
Thanks again!
My db knowledge is clearly not enough!
1 Like
@Jeff_Hager I’m almost there
One piece of knowledge I lack.
So one business idea had bit of data in 4 sheets.
How do I make a form that shoots answers to 4 different sheets with keeping the input connected by idea name?
1 Like
I’m not saying that your one pager idea is bad, but like I said, if you are trying to use a component that’s designed to work with multiple rows of data, then the data needs to be in multiple rows and each item in a collection will refer to each row (in your case, the relation itself needs to refer to multiple rows). An array from a single row may look similar to a lookup array based on rows, but they are fundamentally different in how they are built. And glide has said in the past that an array built from sequentially numbered columns is essentially a hack and a throwback to when we didn’t have a data editor and had to do everything in the google sheet. Now we have a split text column to split a delimited list of items into an array, but it’s still based on a single row. It would be great to display an array as a list, but for now that’s not an option
Database design doesn’t have hard set rules, but generally these are some guidelines:
- Don’t duplicate data. If you are putting the same data into multiple rows, then you most likely should be splitting the data into separate tables linked by a single or a small number of key values. For example, I only store student names in the student table, along with a unique id. All other tables store the student’s id, and I use a single Relation/Lookup to retrieve the name for use when displaying the data in the app. This prevents issues if two students happen to have the same name, and if a name changes, I only have to update it once in the student table and it automatically displays correctly everywhere else On the flip side, I can create multiple relations in the student table to link the student id to the multiple rows of data in the related tables. This allows me to display multiple rows of related data within the student details screen.
- Storing similar data in multiple columns isn’t necessarily wrong, but it doesn’t scale well and requires constant database layout changes if you ever intend to add more columns that are similar. In this case, it’s better to transpose those columns into separate rows in their own table. Then it can easily grow and scale if you later decide to show 4, 5, 6+ benefits and faq’s per item. Or in case you have only 1 benefit and faq to show.
- As pointed out above, make sure you have reliable key values to link tables of data together. Make sure it’s a value that can never change, otherwise you risk breaking the relation and leaving data hanging out there with no matching parent row in a parent table.
- Think long term. A datebase layout may look fine with a small dataset, but what would it look like with thousands of users adding data. If you would ever have to make a change to the database, how much work would it be in the future to allow your existing data to still function with a different database layout. How much work would it be to migrate data to new tables. What are the things that you could potentially change in the future?
- Don’t be afraid to make changes to your layout when you are still developing. Several times I’ve thought that I had my database set up perfectlyy, only to find out later that it’s not going to work out. I’ve had to redesign things as I’ve discovered the true flow of my app or changed my mind on things. My original app is nearing 3 years old and I’m still making changes to the database as I discover better ways to improve it.
3 Likes
I agree.
I think what Jeff tries to state is called Database Normalization when you work with DBMS formally.
Many people have learnt a little about it by accident on the way thanks to Glide’s tools/design but sometimes, when the APP is complex and handles too much data, we need to carry out a Normalization on our desktops before starting our development.
A better explanation about this key concept can be read here:
Feliz día!
2 Likes
A native form can only reliably write data to a single table…especially if you want all of the data linked to the parent table and if it uses RowID. That’s why a lot of us have gone down the path of custom forms. A native form doesn’t write the data to the table until you submit the form. It can make it hard to create links to other tables if a parent row doesn’t exist yet. You can do it with an custom action on submit of the form, but it really depends on what you intend to have for your key values that are used to create the relations between tables. A custom form gives a little more flexibility, because you can set these keys beforehand and the data the user enters is written to the table in real time. Custom forms typically use a table with user specific values that are only meant to be temporary placeholder until you click on the button to write that data to other tables.
I guess my first question would be, what do you intend to use to link the parent table to the other 4 tables? Is it a row id, a unique id, simply the name the user entered?
1 Like
Thanks for your explanation, I have it now spread across 4 sheets!
1 Like
The name of the idea is what I use now, but that could be unique id if needed of course. I’ll look into a custom form!
The reason I ask is because you might still be able to use the native form. I prefer to use rowID or Unique ID for relation. I would think that the name of the idea could easily be duplicated, which would cause problems with your relations down the road.
- What I would maybe do is figure out which table contains the form button (what table drives the detail screen that contains the form button).
- Assuming you have user profiles set up and only signed in users can submit the form:
- Create a template column that returns the email of the signed in user’s user profile email.
- Create a single relation that links the template email to the email in the user profile table.
- Create a new column in the user profile table that will hold a unique ID value.
- OR, If you do not have user profiles, or a user is not signed in, then you will need a separate table with a single row.
- Create a column to hold a dummy value such as ‘X’. This will be our key value for a relation.
- Create a user specific column to that will hold unique ID value.
- Then like above, in the table that contains the form button, create a template column with the value of ‘X’
- Create a single relation that links the template ‘x’ value to the new single row work table you created.
- Create a Lookup column to retrieve the unique ID value from the work table through the relation.
- Now your form button will need a custom action.
- The first action will be to set the unique ID special value to the unique ID column through the single relation.
- The second action will be to open the form.
- The form itself should be set to create the parent row in the parent table.
- If you created the unique ID in the user profile, then you should be able to add the unique ID to the form as a User Profile value component.
- If you created the separate work table, then you should be able to add the unqiue ID to the form as Column Value component.
- Create a custom action in the On Submit action.
- Create 4 Add Row Actions…one for each of the 4 other tables you will write to.
- If you created the unique ID in the user profile, then you should be able to add the unique ID to the form as a User Profile value component.
- If you created the separate work table, then you should be able to add the unqiue ID to the form as Column Value component.
Now all tables should have the same unique ID that was generated when the form was originally opened. The next time the form is opened, it will generate a new unique ID for the new set of records. This will allow you to create relations between all of the tables by linking the unique ID.
A custom form would be similar but a little different. You would still have a work table where the form values are temporarily stored, and you would still generate the unique ID when going into the form, but you would have to add some additional clears to those temporary columns when entering the form. The rest of it, such as the multiple Add Row actions, would be very similar.
2 Likes
Thanks again for your time and knowledge, Jeff, appreciated, I’ll look into it tomorrow!
2 Likes