How best to do a User Favorite Column in Pages with changing data

[BLA_68]

In my app made in Pages, in the sheet that feeds the app, the first 10 columns or so are fed from an external source. A script updates the sheet every day, replacing the data in the first 10 columns with updated data. The first column of this data is a unique number which stays constant (a unique item number) and then the other columns have updated info about the item. Items are never deleted, but the challenge I’m having is that items are sometimes added essentially in the middle of the sheet. So on day one, you might have:

1. A-001
2. A-002
3. B-001
4. B-002

and the next day this:

1. A-001
2. A-002
3. A-003
4. B-001
5. B-002

Meanwhile, in columns to the right of this data I have, among other things, a user-specific column, the function of which, is to allow the user to mark or “favorite” an item. This was a boolean that when a button in clicked in the list, sets the value to true or when the button is clicked again, clears the value. This is a custom action that says “if the value is empty, set it to true; if the value is not empty, clear the value”

Back to the data, as those more knowledgable than me can anticipate, when new items are added in the middle of the data on the left that is being fed to the sheet, the columns to the right lose their association with the data on the left. The data on the right does not shift down, essentially. In the addition it seems in the example above, A-003 now has the Glide Row ID that had been assigned to B-001, so now if the user looks at their favorites it shows A-003 rather than the item they had favorited.

Is there a simple solution to maintaining an accurate list for which items have been favorited in the sheet? I’ve explored the technique described in other posts of using an action to set the column value in a different sheet, but that seems to have a limitation for this use, namely that, unless I’m missing something, clearing that value from a button in the main sheet is not as straightforward as setting the value. If I create a “user favorite” sheet, I can set the column value in the second sheet to record the item number and that maintains the integrity of the user list, but I can’t seemingly just “clear” the value in the second sheet if the user wants to un-favorite it.

So, (A) is there a simpler way to accomplish my goal of an accurate user favorite list using a user-specific column that is stored in the main sheet or (B), is there a simple way to clear the value in the second sheet? Or (C ) Some other approach?

[ThinhDinh]

With an external table, you can achieve it. It should be the same as below. Just replace the Places ID by the item ID (not rowID, since you’re replacing rows everyday).

How to send email to a list of user using Zapier in Glide?

I think you just stick to this extra table. Create a relation from the Places table to the Favorites table, and return all the emails through a joined list column. For favoriting/unfavorting: Favoriting: Add a row to the Favorites sheet, containing the signed-in user’s email and the Places ID. Unfavoriting: Add a template column in the Places table containing the email of the signed-in user and the Places ID. Let’s say it would look like: a@gmail.com - ID. Add a template column in the…

However, I would think of another way to “import” your data everyday. Maybe you can find a way to extract just “new” rows in your original database and add it to the end of your Sheet. Changing the item ID everyday makes it prone to error.

[BLA_68]

Thanks for this response @ThinhDinh . I will dig into implementing the unfavoriting step. But first, I seem to be running into a more fundamental problem. I feel kind of dense here.

I’ve created a new sheet called user favorite and when I click the “favorite” button on an item in the main sheet, it writes the value of the item ID to the user favorite sheet. That works fine and the list is stable after testing by adding items to the main sheet.

However, I can’t for the life of me figure out how to display the favorites. I created a relation column in the favorite sheet that relates the Item ID in the favorites sheet to the item ID in the main sheet. I assumed that would allow me to use favorites sheet as the source and display items from the main sheet using the relation. I choose the favorites sheet as the page source and on the collection I choose the relation column that I created in favorites. In the data editor it shows the data from the main sheet is displayed in the relation column. But on the page, it is blank. The editor offers columns from the main sheet, but no items are displayed on the page. What am I doing wrong?

EDIT: I also tried the reverse–relation column in the main sheet relating to the user favorite column. Same result: Data shows in data editor but nothing displays on the page.

[Eric_Penn]

Add a lookup column using that relation

Lookup Column

[BLA_68]

Can you help me a little further? I’m not clear if that’s a solution to displaying a list of favorites? Or a solution to displaying data from the main sheet. Assuming it’s the latter, for some odd reason, the only dropdown field in the Items Data tab that recognizes the Lookup column I created in the is image dropdown…but the lookup values are text. Title, Description and Meta don’t display the Lookup column.

Right now: I can either display exactly one value (the first one) from the user favorites table and that will give me access to any column in the main table for that row. But it’s not a list of all the items, it’s just the first item.
Or, by choosing the relation field in the user favorites table as the source, I can display all of the favorited items, but only the values in the user favorites table. It doesn’t show me any related values from the main table. This is where I can access the lookup column, but only as an image.

Welcome any additional help. This seems to be more problematic than it should be…

[BLA_68]

Ok. Never mind on that. I figured out what I was doing wrong. I had set the relationship up as a match multiple. Making it a single relation and creating a lookup in the same sheet worked. Thanks @Eric_Penn and @ThinhDinh

[Eric_Penn]

If you have a multiple relation you could still use a lookup column and then finally use a split text column with the lookup column as it’s source. My colleagues can correct me if I’m wrong.

[Darren_Murphy]

You can, but… that’s going to give you an array, with one value for each matching row.
If you were after just a single value from a specific row, you’d then need a Single Value column, and you’d need to know which index in the array contains the value you want. If you just want the value from the first matching row then it’s easy enough. But if not, well… good luck with that

Much easier to just use a single relation.

Actually, just thinking about this a bit more the split text column is redundant as a lookup through a multiple relation already returns an array. In fact, I’m not even sure if a split text on that lookup would work, or what it would return. I’d have to try it. Anyway - Single Relation