Relations...Am I doing this right?

I have two tables. One, I’ll call the main table. In the main table, there is a column with a unique number for each item, followed by numerous columns with information about that item.
The second table is a user favorites table. When the user “favorites” an item from the main table, it writes the unique number from the main table to the user favorites table in a user specific column.

Now I want to display the user’s favorites, but using columns from the main table. So I create a relationship in the user favorites table that matches the unique number in the main table to the unique number in the user favorites table. Next I create a lookup column in user favorites table. In defining the lookup, one has basically two choices: look through the list relations ( listed at the top of the list and shown with an arrow icon) and choose a single column in the related table; or lookup through the table. When you lookup through the table, you can also choose a single column OR you can choose the relation itself. So if my relationship column is called “Rel User Favs to Main Table”. and I choose that column, the result is basically a lookup of all columns in the main table. I need this because the user favorites table is really just the unique number and some user specific columns like notes about that item. I can’t display the details of the item unless I can grab one or more columns from the Main Table.

So now I want to display a list of users favorited items, with data from columns in the main table. The page has as its source the user favorites table (still a little confused that the page AND the collection both have a source, but that may be for a different post). When I add a collection, if I choose the “Rel User Favs to Main Table” as the source, I get only the first item in the user table, not all of the items. If instead I chose the lookup that looks though the relation column, I get all of the favorited items with the related data from the main table, letting me display the detail about the items that is stored in the main table.

is this the right way to achieve what I want? It works and it’s what I’ve been doing, but I’m asking in part because reviewing the documentation on relations and lookups that is not described. The documentation on lookup contemplates looking up a single column; it doesn’t described looking up to the relation itself to get the entire row. The documentation on relations seems to be more about how to show items from a different table that are related to a single item on a detail page. Also, I feel like the interface for choosing the source of a lookup changed a little in the past year or so, adding to my confusion, but I may be wrong on this last point.

Creating a Lookup column that returns a relation just seems odd to me. That’s not something I would have ever considered doing. I’m actually surprised that it works for you. Usually if I’m using a Lookup column, the intention is to return a specific column from a relation. I think it would make more sense to create a lookup column for every column you want to return from the main table.

BUT…If we back up a little bit, since you are writing the main table unique ID to a user specific column in the user favorites table, I am assuming that a user’s favorites don’t need to be shared with anybody else. In that case, wouldn’t be a whole lot easier to get rid of the User Favorites table and instead add a user specific boolean column to the Main table? Then you could place a switch component on the main detail screen for a user to set or unset a favorite. A second collection can then be filtered to only show main table items where the user specific boolean is checked.

1 Like

Jeff thanks for the reply. I thought that question might come up. Without going to deep into the details, the main table is fed with data that is imported daily and that sometimes results in data being added to rows in the middle and thus the approach you suggest would sometimes lead to data in the user column being one or more rows off, as the data in columns to the left shifted.

On the first issue, I agree it seems like not the right approach or intended. Do you think it may have a gotcha down the line? Any thoughts on how one might more appropriately access the the entire row of a relation instead of just one column? Noting my problem above, other ideas on an approach?

Ok, that makes sense.

First of all, I would avoid using user specific columns in your favorites table. It’s not necessary and will just be a headache to deal with. It should just be a normal basic column to store the unique number. Also add an email column to store the signed in user’s email when you add the row. You can apply row owners to that email column or filter by that email column.

Make sure your relation is a single relation and uses that new non user specific unique number column. I think what I would do then is create maybe 3 or 4 lookup columns in your Favorites table that use the relation and return the important details you need to fill in the list details, such as heading, description, meta, etc.

Then on your favorites list, change the action to Show Details → Relation instead of This Item. That way the favorites list will directly open the related main table details when you click on an item, instead of opening the details screen for the favorites table.

1 Like

Ah…Ok. That’s a better approach. I will try that.

This is also helpful but takes me back to to my larger question about lookups and relations. Bear with me. In my case, Table 1 has 60 or so columns and Table 2 (the favorites table) has three or four. A lookup gives you the ability to basically merge the two tables, one lookup column at a time, right? If in various ways of displaying the info across the app, I essentially need access to all 60 columns of Table 1 when working with Table 2, that is a bit tedious to add all 60 one at a time. It would be great if once the relationship was established between two tables, there was a “virtual” table as a source that had all the columns of the two tables available, automatically. That’s not the case. Sooo…

I suppose what would work better here for me is to instead work with Table 1, and add lookups to bring in the handful of columns from Table 2 and then filter Table 1 by the user email address lookup when I want to display only the user’s favorited items. The only downside of that perhaps is that Table 1 has thousands of rows, only a handful of which have been favored by the user. So it’s loading a lot of data that isn’t used, potentially making it slower?

I understand that it would be nice to bring over several or all columns in one shot and have a “merge” table, but that’s not an option in Glide for now.

I guess I don’t completely understand why you feel that you need to bring over all of the columns from Table 1 into Table 2. Assuming you only wanted to create favorites, and nothing else, the suggestion I had was just to bring over a few column to build the list from Table 2, and then change the action so it still takes you to the Table 1 details screen. But, I’m guessing that you must have values in Table 2 that a user can update??? If that’s the case, then like you said, maybe it’s better to just filter Table 1 based on a relation to Table 2 for a user. That does probably make more sense.

First of all, I don’t think you would have any difference in speed. The entirety of Table 1 would still be downloaded to a user’s device. A filter would just determine how many of those rows in Table 1 would be rendered and displayed on the screen. So I think if you went that route, you would be fine, but I don’t think it would make much difference as far as loading speed.

Also, since you would be viewing the details screen for Table 1, and I assume you would need to update values in Table 2, then there is a solution to that. It’s a little more involved though.

  • First, I would create a user specific column to mimic each editable column you have in Table 2.
  • Then create Lookup columns to retrieve any editable values from Table 2.
  • When clicking on a Table 1 list item, I would change the action to a custom action. First, the custom action should do a Set Column action to take those Lookup Values and write them to the user specific columns in Table 1. Follow that by a Show Detail Screen action. This way the values in Table 2 will be written to the user specific columns at the moment you click on a list item.
  • In the detail screen, you would add entry components that point to the user specific columns. The user would then update these user specific values.
  • Then add a button. When a user clicks on the button, it should call a Set Column action to write the user specific column values in Table 1 to the basic columns in Table 2 through the relation. I would probably make that a custom action as well, and add a notification action to tell the user that what they entered into the entry components has been saved to Table 2.

That’s how I would do it. I could see one potential issue, if a user is currently viewing a detail screen from Table 1, and the table is updated and shifts the rows while they are viewing that screen, then it might be possible that those user specific columns values may temporarily become misaligned with the row they belong to. If they left the detail screen an came back in, it would be fine. If you’re not worried about rows shifting like that, then you should be fine. Otherwise, you would have to revert to using the 60+ Lookup columns in Table 2 option.

1 Like

Hi Jeff: I truly appreciate your patience and time so far. This question makes me realize that I probably haven’t explained what I’m asking and why, or said something that got us off track. At risk of trying your patience, I hope you’ll stick with me as I back up a step here.

First, my question does not relate to detail screens, though I appreciate the advice there and will think through how that may help me implement detail screen for this part of the site.

My question is about collections. Specifically, how to display a page made up of a collection or collections of items that the user has favorited. Now, as you have noted, Table 2 has very little info in it. It has the unique number from items in Table 1 and it has the user email address. The user can also add notes to each item that they have favorited and some key words. But that’s it. So I don’t want Table 2’s data to be the source for the collections on this page.

Table 1 IS the app. The app is basically a system for cataloging documents. Table 1 has things like the document name, the author, a description of the document. It has has information that catalogs the occurrence of events related to the document and the date those events happened. When it was drafted. Whether and when it was submitted for review. Whether or when it was edited while in review. Who edited it on that date. Whether or when it was approved. Etc.

Why do I need to access this data in a collection of the user’s favorites? First so that I can display information about the documents that is only in Table 1. And that will vary depending on the page the user is viewing. On one page it may just be a list showing for each document the title, author, and description of the document. On another it may be the document title, the last action that was performed, by whom and the date… In addition, I need to filter this information, for example to show on a given page just the documents with action dates from this week. OR the ones that are due next week. I may want to group the data within a collection, showing user favorited items grouped by “edited” “reviewed” and “approved.”

However, I don’t want to display all of the documents. I just want to display the ones that the user has favorited. As you know when I create a page and add a collection I have to identify a source and the available sources don’t do that. Table `1 is not right because it doesn’t tell me anything about favorites and Table 2 is not right because it doesn’t tell me anything about documents (other than a number). So I create a relationship and possibly use lookups to bring the data together. And that’s where I think I get lost. My approach was to create the relationship and then use a lookup through the table, though the relationship column, which returns ALL of the columns from Table 1.

You have partially answered my question here. But my follow up was “Gee, adding lookups to table 2 one by one is tedious because I need to access a lot of the columns from Table 1, either to display them, to group by them or to filter on them in a collection.”

So…A. How best to display a collection of user favorites stored in Table 2 where all of the useful data is in table 1. And is there a way to do that that doesn’t involve adding lookup columns one-by-one to table 2.

At a very basic level, I would create a relation in Table 1, linking it to Table 2. Then add a collection and filter it where the the relation is not empty. That’s the simplest way to only show a user’s favorites while still having the collection linked to Table 1.

1 Like