How do I change data for multiple reference rows from a row in another table?

I’m stuck on trying to change data on one table based on changes from another. For example, I want to make a row in the Filters table become Inactive based on when a row in a reference row (Properties) becomes Inactive. I created an Action Data: execute an action a set of rows with the formula: SELECT(Filters[Property ID], [Property ID] = [_THISROW].[Property ID]), but it doesn’t work correctly.

Also, there can be multiple Filters referenced to a single Property. And I want all of them to be inactive when the Property becomes inactive.

Any suggestions are helpful.

You need to specify a List of key values for the Filters table. Is [Property ID] the key column in the Filter table?

By the way, the FILTER() expression is very similar to SELECT(), I prefer to use it whenever appropriate, such as this case.
SELECT(Table[key column], …) is equivalent to FILTER(Table,…)

1 Like

FYI: the FILTER expression is actually a port of the select expression. When you use FILTER, it’s just another way of writing SELECT() - when AppSheet is computing the formula, the FILTER() syntax is transposed into a SELECT. Just fyi. :nerd_face:


Also another note to @Andrew_Hock:

In your post you put a sample SELECT():

While AppSheet does not require that all column names be unique, there are times when it could (perhaps it should enforce them, but that’s a discussion for another time), I have seen problems with some formulas not working solved by making column names different among tables.

For instance:

If I had an invoice table, with Invoice_Details, Invoice_Notes, and other child tables, I wouldn’t call the reference column in each of the child tables “Invoice_Link” or “Invoice_ID”; instead I would individualize each of them (Invoice_Notes_Invoice_Link, Invoice_Details_Invoice_Link, etc.).

AppSheet has no limit to the length of a column name (that I’ve found), so you can get really detailed with the column names. Not only will this help you to be able to read your formulas and understand exactly what’s happening (without needing to know where it lives, context it’s used, etc.), but also any queries run on the back end will pan out correctly.

3 Likes

While the following aren’t direct examples of how to do what you are looking to do, they will give you some examples of how you might accomplish updates of child records; the system and mechanisms necessary are the same, so these can give you some hints and ideas of how to accomplish things.

3 Likes