I am just playing around with some fun stuff in the program when I was musing over a basic Glide question.
Is it better on a form to Submit ALL the information or to use a Relation/Lookup later on?
Example, I am working on a Stock Market simulation, I have a Trading Floor where they can buy preapproved stocks.
On the BUY form I will certainly SUBMIT the TICKER symbol and the Price and Number of shares.
(as well as the signed in user email)
Question:
Should I also get the form to submit the Company NAME and LOGO automatically?
or
Should I use the TICKER symbol to do a RELATION and LOOKUP the Name and Logo?
I guess it does the same thing, but what would YOU do?
Wondering.
I’d say it depends on if you need the data to stick forever, or if some of it could potentially change in the future. Take my situation. I use a form to submit lessons for students. I only submit the RowID of the student instead of the name because it’s possible the a spelling error could lead to the name being changed at a later date after the lesson was submitted. RowId will never change, but the name could and that would break any relations in the future. In that case I use the ID to relate to the student sheet and get the name instead of directly storing the name.
On the other hand, there is a charge rate for the lesson. I only want to save the current rate amount and not use a relation to get the rate since it is possible for the rates to change in the future and I don’t want the use of a relation to mess up any past lessons. Past lessons have already been invoiced and billed and if I used a relation to get the billing rate that has changed, all past bills would be wrong when they should never change.
In your case, I would use the ticker symbol and do a relation for the name. It’s very reasonable that a company may alter their name or logo, but the ticker symbol would never change.
Maybe an easy rule: If you are using and have access to any sort of key or ID field for what you are submitting, use it with a relation. If a key or ID isn’t related to the value, or it needs to stay permanent, then save the actual value.
2 Likes
So if it is data that is not going to change, then bring as much over as possible on the submit.
If there is any possiblity that it may change, then go with the Lookup. That makes sense.
So far I just have not given it any big thought, but now I will.
I my instance, I may want to later change the logo (like if I get a set of all similar logos) - so Looking up the logo is the way to go.
I think I just kinda said what you did, but I needed to type it out to think.
1 Like
Agree with Jeff here, when dealing with big datasets, lookups can be quite dangerous when you have too many relations in your data to control, so it makes sense that if data must be hard-coded to the new sheet, then try bringing over as much as possible.
1 Like
There’s a lot scenarios to consider depending on your situation. I’m sure it will make sense as you run into them. In my name vs row id example, I used to have a script that would automatically copy the name to 3 other sheets. I always had a worry that if the name ever changed, then the script would write an extra record to the other 3 sheets and break the relation from the student profile sheet to any existing data in the other 3 sheets. I’ve since restructured things so I don’t need the script anymore and use a form instead. All I save in the form is the row id of the student. That way I can use a relation and lookup to pull in any relevant student info into the other sheets. Another advantage of this is that I can later pull in additional info with additional lookups without having to backfill the existing rows in the sheet that would have otherwise been filled with column values in a form.
One downside is that when I look at the data in the Google sheet, it’s hard to decipher which data belongs to which student, since all I see is the RowID, but that’s a minor inconvenience in exchange future proofing and data integrity.
As with most database logic, it’s best not to have duplicated data throughout the database. If you can use keys or IDs to link data together then it’s better for future proofing.
But, if there is no chance data would ever change in your form sheet, or your need it to remain permanent, then it’s easier to just let the form write all the data you need directly to the sheet without having to use relations and lookups.
1 Like
