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.