Update multiple tables from a value of one table

[Neeraj_Malik]

Looking for solution on how I can update the values in multiple tables from the value of one (source) table. This is to deal with following sceraio

There are 4 tables

Buyer_PO
Exporter_PO
Importer_PO
Customer_PO

Key to all these tables are common with column name Purchase_Ref. All these tables also has another common column named as Shipping_Status

Adding a row to Buyer_PO adds rows to all other other tables with same key and shipping status

Now I want to create an Action/Bot that if the Value of Shipping_Status is changed, then a Bot/Action should update the value of Shipping_Status in all the tables.

Any suggestion to help me out pls @Steve @Marc_Dillon @WillowMobileSys @Suvrutt_Gurjar 

Thanks in advance. 

[Aurelien]

Hi @Neeraj_Malik 

You may benefit from using virtual columns.

Such columns allow a calculation on the fly, and will avoid you to build any update mechanism.

So, if your column Purchase_Ref comes from a table with a status, you use this expression:

[PurchaseID].[status]

 to retrieve its status in your virtual column.

For reference:

Use virtual columns - AppSheet Help

[Neeraj_Malik]

Thanks @Aurelien . However I don't think virtual column would help in this case as it's not about retreiving the info but about updating this one column with same name in all the tables.

I can create separate actions for each table. But I would like to update all these tables with single action/bot.

Not sure if I'm able to explain the scenario that well🙂 

[Tommy62]

Schaue dir mal dieses kurze Video an, diese Methode könnte die Lösung Ihres Problems sein:

Aktion um Tabellen zu aktualieren 

[Suvrutt_Gurjar]

---

@Aurelien wrote:

Such columns allow a calculation on the fly, and will avoid you to build any update mechanism.

---

@Aurelien has pointed in the right direction. An update mechanism across multiple tables could be cumbersome , both with actions and automation.

If [Shipping_Status] is a real column, then I believe you could build a pair of reference actions, one pair for each table and then a combined group action to include those three pairs. So there may be as many as 7 actions to update 3 tables.

[Neeraj_Malik]

Thanks @Suvrutt_Gurjar . [Shipping_Status] is a real column in multiple tables that needs to be updated in all of these tables. I'm sorry I couldn't understand your explanation around pairing of refernce points. Request if you can help by describing in more detail pls

[Suvrutt_Gurjar]

Hi @Neeraj_Malik ,

I mentioned 'reference actions".  These actions always work in pairs - two actions are needed.  Reference actions can be executed on table A to change some columns in related rows of table  B.  So you will in general need 3 pairs of actions to change status in 3 tables from 1 table.

Please take a look at the sample app below to understand reference actions. Also please carefully read the description of the app at Info --> Properties --> App Documentation -->Purpose

Reference Actions - AppSheet

One thing you may want to mention is

A. will the status be FIRST change in a specific table say Buyer_PO and then you would like it to change it in other three tables? Or

B. would you want that status can first change in any one of the four tables and then it should reflect in other 3 tables?

If it is scenario as in point B, then I am afraid the implementation will become more complex.

If it is scenario as in point A above, then you could create 3 pairs of reference actions, each starting from Buyer_PO and changing status in remaining three tables.

[Sirfyaad]

To create an Action/Bot in updates the value of the Shipping_Status column in all tables when it is changed

SELECT(Exporter_PO[Key Column], [Purchase_Ref] = [_THISROW].[Purchase_Ref])