Update fields from a different table than the source collection

I need help to know (1) if this is possible and (2) any examples or hints on how to do it.

I have two tables PRODUCTS and CATEGORIES. Its 1 to Many, so a single product can have 1 or more categories. I setup the relationship by creating a column in PRODUCTS from text listing out all the related category row ids. I am using the choice drop down (unless someone has a better way) to select all the categories when i edit a product. This works perfectly. HOWEVER, when i am on the categories screen / collection i want to be able to edit the related Products. Seems like the edit forms only let me write to the Categories table and NOT to the Products table from this screen.

In the Products table, make sure you split the Categories column into an array using the Split Text column.

Your relation from Product to Categories should use that array > Category ID in the Categories table, return multiple matches.

Create a relation from Categories to Product as well, basically a reverse setup of the relation above. You should get all related Products of a Category.

Add a collection of related Products in the Categories details view. Add an action to open the edit screen for those products.

1 Like

I think i understand the solution. For clarification, are you using an Array type or do you just mean make a split column separated by commas.

Also, the way I understand your solution, you aren’t using the choice collection, instead you are displaying the products collection and limiting the edtiing of categories by an extra click into each product (drilling down one at a time), rather than a single screen where you can just check or uncheck related products on a single edit screen for the category.

A Split Text column converts a comma delimited list of values into an array that is then useful for creating relations.

Thanks Jeff, I have that already in my tables, thanks for clarification.

1 Like

Sounds like I didn’t cover this part. In my opinion, your relation should only just be one-way. If you want to edit it two ways, then there’s no easy solution because the comma-delimited lists are only stored on one side (the Products table, as it seems).

Whilst there might be a way to: say, every time you edit a product’s related categories in a choice component, on the Categories table you edit the categories’ rows with the related product’s IDs, it would be too complex to build.

Okay i see. It doesn’t seem complicated to do with SQL queries but i get how GlideApps doesn’t let me do it easily.