Hi, My app has a sheet with profiles sorted by various groups. There is another sheet where monthly data can be added related to each profile. If you access the profile via the member info datasheet (where profiles are stored) and then click the component (link to screen) to enter data for that profile it first takes you to a screen for the datasheet which lists all the profiles, so you have to select the profile again. Is it possible to link the profile from the profile sheet directly the screen on the other datasheet that is used for data entry for that specific profile?
Eric! Youâre a legend. Just the question make me think. So before I had not linked forward to the data sheet with a relation column, only back to the group info sheet. Adding the relation column linked to the data sheet brought up the additional âlink to screenâ option for the relation, which when activated takes it straight through to the relevant member! Solved!
And now for a bonus question before I mark this as such. If you look above at the second datasheet screenshot you can see that each member has a name in english and local language. When I set up the relation column I asked it to pull back the english âMember Nameâ column. But it actually returns the âName Khmerâ column. Why is this? Because itâs the first column of the sheet? I thought it should return the column I request in the relation settings? See my screenshot below for the relation setup, am I doing something wrong?
A relation links to an entire row. Not a single column. It makes no difference which column is shown in a relation column. Itâs just telling you that it found something.
Apologies I misunderstood the bonus question. As Jeff said it is not important what the Glide Editor shows for your relation, only that it displays âsomething.â
Your relation is working properly and Match multiple is likely not necessary for your case. Cheers!
Thanks for the comments everyone, a little confusion over the question I think.
Iâm aware that the relation column matches the row and then data can be pulled out depending on what is needed. Hence my use of the lookup column in my first Glide datasheet. I think I was confused earlier from the documentation and videos (my brain misfiring on review) in thinking that the data that would be listed in the relation column is the return from the specified column. Itâs not an issue, just a query.
While I have a few people in the discussion I do have another more technical question on the relation columns and components.
In my app I have groups and members. With over 1000 members, some of them have the same names. Now I understand normally you would use a unique identifier to prevent mismatching with relations, often this would be an email.
However, our members are rural farmers, many of whom donât have a phone let alone email. My current workaround to this would be to add another undisplayed column with the member name and group name combined to create a unique identifier in the row to link relations correctly.
Is there another more efficient way to do this with relations i.e. by requiring glide to match info from 2 columns before returning a row?
As I understand it unique identifier wouldnât work for this as I have multiple sheets with the same members listed, some of whoâs sheets wouldnât be updated every month. So the unique identifiers would not correlate.
Relations used to always show the first column in the related sheet, no matter what. At some point that changed to what seems like a random column thatâs displayed. I donât think any of us have ever pinpointed which column it chooses now, so itâs seemingly random.
As for your next question, normally I like to assign unique values to a user, and use that unique value throughout the app. You can create a value by adding a Glide Docs, which would automatically populate your members sheet with unique IDâs for each member. There is also a Glide Docs special value column that you can use when adding rows through the add function or through a form. I tend to use unique values like those mentioned because they donât rely on a name or email which could potentially change down the road.
The hard part though is that if you already have sheets with data, itâs hard to properly create those links on the existing date. In my app, which has a list of students, I used to link related data between sheet based on student name only, but I always had concerns if a name ever had to be corrected later. Eventually I ended up adding a Row ID column to my students sheet. Since all of my data resides in google sheet, I then had to go through each of my additional sheets, create an arryformula with a vlookup formula to tie the name to the student sheet, then the vlookup would return the row id for each matching student. Finally I copied that rowid from the vlookup into its own column. Once I had that data, I was able to redo my existing relations to use the row ids from the student sheet to the other related sheets. Now, whenever I add data to the other sheet, I end up passing along the students row id via the Glide Docs. Thatâs the hard way to deal with any existing data, but most future proof in the long run and in my opinion, itâs the best as long as you donât have a need to use Glide Docs, which requires an email address to work properly and secure data only for the intended user.
If you want to stick with your idea of simply joining two columns together, that very easy to do by creating a Glide Docs to join two columns together. With that, you can then use the template columns as your key in the relation column. Just simply join the template column in one sheet to a template column in another sheet.
Thanks for the clarification Jeff. I am always interested in âthe whyâ, not only âthe whatâ, although in this case, Iâm happy to go with random.
I appreciate your input on my other problem. The good news is that apart from the sum total of savings from the previous year all the data resets each January. So if that total is stored in the member sheet I think I would avoid your problem of assigning historic data from other sheets to each member with array formulas and vlookup.
I think I get your explanation of how to link the row IDâs between sheets, I will have a play and make sure itâs clear to me. I would prefer to go this route to be honest, as you say its future proof and really a better solution. Good news is that I am not planning to use row owners (certainly not for this iteration) so thatâs not an issue.
Cheers for the tip on combining column text, itâs a nice simple solution if I go down that route.
