How to update the status from the Reference Table in the data

[Manish_Jain11]

I have 3 Tables - Product, Design and Orders ( Images Attached)

Now I want to get the Design status of the Order from the Product Table and Design Status of the Product from the Design Table . But whats more crucial is to update these status as and when they are changed in the reference table.

For Eg. Whenever I Change the Design status of a Particular Product from The Design table, the Design Status Field in Product Table should get updated. Likewise ,Product Design Status in the Order Table should also get updated.

How is that possible ? Plz refer the attached images

Design Table

!

Orders Table

!

Products Table

[Rafael_ANEIC-PY]

Hello @Manish_Jain1, if your design status column are just references like [Ref_column].[Status] you will only be able to change the status directly from the design table itself, by editing the row directly or using actions, and when you do, as you already know, all those references won’t update on their own, since those rows were already saved before the change, for automating this you have a few options:

1. Make your design status column on your “Orders” and “Products” tables a virtual column, so the status is updated whenever it is changed on the “Design” table, this is the easiest approach, and given the efficiency of using references, it will be decently fast too.

2. Set up a grouped action that will change the design status in the design table, and also change a value from any column (could be a helper column) of the child rows belonging to that record, so that the referenced columns are re-evaluated by AppSheet, and therefore refreshed.

3. I’m not really seeing much of a difference between your 3 tables, you probably made those just for illustration purposes, but if they are so similar in real life i would just merge them hehe

[Manish_Jain11]

Rafael_ANEIC-PY:

Make your design status column on your “Orders” and “Products” tables a virtual column, so the status is updated whenever it is changed on the “Design” table, this is the easiest approach, and given the efficiency of using references, it will be decently fast too.

This One seems the best option but I was struggling to make the Virtual Column as a Ref Column as Virtual Column Expects an App Formula… After many trials, In the Design Status Virtual Column inside the Products Table , I gave the Ref to Products and [Product Code] in the app formula and it somehow is working now… However, I do not understand the logic behind that .

Rafael_ANEIC-PY:

I’m not really seeing much of a difference between your 3 tables, you probably made those just for illustration purposes, but if they are so similar in real life i would just merge them hehe

Yeah , Its just for reference mate… The actual data is pretty large.

[Rafael_ANEIC-PY]

Manish_Jain1:

This One seems the best option but I was struggling to make the Virtual Column as a Ref Column as Virtual Column Expects an App Formula… After many trials, In the Design Status Virtual Column inside the Products Table , I gave the Ref to Products and [Product Code] in the app formula and it somehow is working now… However, I do not understand the logic behind that .

The thing is you’re not gonna use the virtual column as a ref column itself, you’re using it to dereference the status, for example:

[Ref_column].[Status]

Would pull the Status column directly from the table to which the ref column references, and that’s the only formula you need in this case.