Write data from two tables to a third

I have three tables. Table 1 is a general list of items. Table 2 is a table of items that users, as members of a group, have favorited. As users favorite an item, a new row is created that add the unique item number and their group name. The table then has fields for them to add additional information for that item. Once an item is added by any member of a group it is part of that group’s favorites. So if John and Pedro belong to Group1, when an item is saved to the second table by say, Pedro, it’s marked for Group1 so that it is also marked for John and other Group1 members.

The third table is a list of groups that users belong to (Group1, Group2, etc) . Every user belongs to only one group.

So that system works fine for individual members. A user clicking an item can write their group name (stored in the user table) to Table 2 and the item number at the same time.

What I’m trying to figure out is the logic for admin to also mark items for one or more groups. So for example, Admin is viewing Table1 and what I want is that when Admin clicks on the item the overlay that appears presents Admin with the option to mark the current item for one or more groups. In other words, they need to be able to create a new row in Table2 and write two values to that table (1) the value from Table1 (the item number from the current item), and (2) the group name from Table3.

I tried a few things. For example, adding a card collection to the overlay that accesses Table3 to display each group name with the idea that you could click on one and attach a workflow to add the group name and other data to table2. That works to an extent, but workflows are limited to workflows that interact with Table3 so I can’t grab any of the data from Table1.

I’m guessing this is complicated but wanted to know if I’m missing an easy, obvious solution

Just so I’m clear…

  • Admin views a list of items
  • Admin selects an Item (show details screen)
  • From there, you want to select a group to “assign” that item to, and add a row to table 3, yes?

All you need to do is modify the action at step 2 above, so that you write the selected ItemID into a column in your User Profile Row, then you can refer to that in the Add Row action at step 3.

Update: In fact, just thinking about that - it shouldn’t even be necessary to do that - if you are already in the context of the selected Item row, then you should be able to access that directly. Maybe you need to explain your flow a bit better… (a video is worth a thousand words)

Thank you @Darren_Murphy for replying. I can try to do a video later because it is more complex than that.

I need to be able to access the values from Table 3 (the same way you would use that table to populate a choice component) but then write it to the new row in table 2. But I also need to in the same action write a value from table 1 (the ID for the item that admin is viewing). So clicking a button (or otherwise executing an action) would grab a value from table 1 plus a value from table 3, create a new row in table 2 and write both of those to the new row.

I think your idea of using the user table as an intermediate table to store the values works conceptually, but the limitation is that in the workflow I can access data from one table (plus the user table), unless I’m overlooking a way to access data from a table that is not the table at the top of the workflow.

At a basic level you could have a form button on the item detail screen, show the form, have a choice component listing the groups, and then submit the form writing the current item number and the chosen group to Table 2? That all could be done within a form without the need for a workflow.

Or, are you trying to use a workflow to prevent using a form and to allow rapid succession of the selection of multiple groups to create rows when each group is selected? In that case using the user table as an intermediary should work. Write the selected item number to the user table when selecting an item. Then when you click on your groups, your workflow would be based off of table 3, but you could retrieve the item number from the user table and ultimately write a row to table 2.

2 Likes

Yes. This is the workflow I was trying to achieve and the first click setting the value before opening the new screen is a great idea. Let me test this. That might work. Thanks to you both.

2 Likes

Ok. This worked! And I know it’s a simple concept but I am so grateful to this community and especially @Jeff_Hager, @Darren_Murphy, @ThinhDinh, @Robert_Petitto (and probably others I’m forgetting) for their always kind and helpful advice and all the others that make this forum work. Sorry to geek out, but as a non-coder doing this to build an app to serve his clients, this was the last step in a rebuild of an app that was bloated and convoluted on the backend and is now going to be so much more streamlined and easy to maintain. This is what makes Glide so great.

3 Likes