Update a specific column from a changing column in three apps

[eldderri]

Hi friends,

  In my appsheet app project ,I have a google sheet named Truck inspections 2024 used to create a looker studio report, where I combine data from 3 apps via their tables named CheckList (each app has a table named CheckList).The process is working ,but I have an issue with a column named Truck Status which gets its data from a changing column named Action in those 3 tables.This column changes through workflow from "En attente" to "Bloqué" then to Débloqué" and soetimes to "Annullée".How to capture this change and copy it from the 3 tables to Truck inspections 2024 sheet as soon as it occurs?

Best regards,

[Suvrutt_Gurjar]

Your exact configuration and requirement is not clear. 

But in general, based on the details you shared, you could add the sheet "Truck inspections 2024 " in all the three apps. Then you could use a data change bot in each of the three apps to make changes to the "Truck inspections 2024 " sheet whenever [Action] column changes in any of those "Checklist" tables in the three apps.

[eldderri]

The sheet is already added to the three apps and I have an action that updates this sheet when a record is created ,but the problem is with one column which changes through approval workflow from status to another,.I want the chance in that column reflects in my combined sheet.

[Suvrutt_Gurjar]

Yes, I believe you could do so with the data change bots.

For change in a specific column status  in the three checklist tables, you could use [_THISROW_BEFORE] and [_THISROW_AFTER] status of that column to trigger the bot.

Access column values before and after an update - AppSheet Help

[eldderri]

My update action to the Truck inspection sheet , is set through an action attached to my bot of checklist generation as follows:

and truck status is set by [Action] column .Now ,where shall I add my update automation to my truck status whenever [Action] changes?

[Suvrutt_Gurjar]

Thank you. The action in the bot that you have shown seems to be "adding" a new record. You also seem to be setting the [Truck Status] column through the [Action] column. So you may not be able to track a change in the [Action] column in the "Add" action itself. "Truck inspections 2024 " sheet through changes in the "Checklist" tables. 

In the previous description you mentioned you are changing the "Truck inspections 2024 " sheet through changes in "Checklist" table. However in the screenshots share, you seem to be adding a row to the "Checklist" table only. Could you clarify?

[eldderri]

In my first post , I mentioned that I am combining three sheets into one via appsheet add row action ,but the problem is how to capture change in action column only in the combined data?

[Suvrutt_Gurjar]

Okay, it was not explicitly coming out but now it is clear that you are combining data by adding records. But still it is not clear the bot shows the record is being added to checklist table? 

Now is it correct understanding that

1. You would add the records from three checklists tables into "Truck inspections 2024" sheets table. 

2. Thereafter if any of the source tables of those three checklists changes in its [Action] column, you would like it to be updated in the combined table? So maybe a record from Checklist 1 table was added on 6th December 24 and the [Action] column changed the status on 8th December. Now you would like the [Truck Status] column in the combined checklist table to be changed?

[eldderri]

What you have stated above is exactly what I mean

[Suvrutt_Gurjar · Accepted solution]

Okay, thank you. In that case you could 

1. Add a bot that fires when the [Action] column changes in one of the three input tables. The bot trigger condition will be something like [_THISROW_BEFORE].[Action]<>[_THISROW_AFTER].[Action]

2. You can then use a reference action on the combined table that changes the [Truck Status] column in the combined table.

    A) To identify the row in the combined table that needs to change, you will need to have a column that is common and unique in the three input tables as well as the combined table. 

    B) Assuming this unique and common column is [ID], you can add an expression for identifying the referenced row in the combined table can be something like 

SELECT(Combined_Table[Key column], [ID]=[_THISROW].[ID])

C) The data change action in the reference action that changes the [Truck Status] column can have an expression something like

ANY(SELECT( One_of_The_Three_Tables[Action], [key column]=[_THISROW].[Key Column])) where [key column is the key column of the one of the three input tables.

3. You will need to implement this reference action bot in all the three input tables.

[eldderri]

Is this setup correct:

[Suvrutt_Gurjar]

Yes, it sounds good. In the last data change action that changes [Truck Status], please wrap SELCT(....) with ANY() such as ANY(SELECT( ....))

[Suvrutt_Gurjar]

---

@Suvrutt_Gurjar wrote:

ANY(SELECT( One_of_The_Three_Tables[Action], [key column]=[_THISROW].[Key Column])) where [key column is the key column of the one of the three input tables.

---

Please note above highlighted in the last data change action.

[eldderri]

I have this error message

[Suvrutt_Gurjar]

I request you to see my post carefully

For the referenced rows, I had requested the following expression

For the data change action that changes the [Truck Status],  I had requested the following expression

[eldderri]

So my update action will look like that:

and referenced rows like that:

The combined table is named Checklists and the common id is "ID"

I tested it ,but still no change in truck status column in checklists combined table

[Suvrutt_Gurjar]

Yes, the actions look good.

The reference rows action needs to have a list of key columns.

[eldderri]

I even changed my reference rows to :SELECT(Checklists[Truck Status], [ID]=[_THISROW].[ID]) but does not work

[Suvrutt_Gurjar]

Reference rows only work with key columns.

Please see in test pane what the expression is returning.

I may add that the suggested solution worked perfectly in a similar setup for me.

[eldderri]

I have modified the condition to :[Action] <> [_THISROW_BEFORE].[Action] and I get it working ! Is it correct ?

[Suvrutt_Gurjar]

If it is working means if you mean the bot is successfully run, then yes.

[_THISROW_AFTER].[Action] is optional, it is same as just [Action]

[eldderri]

Thank you so much for your valuable help

[Suvrutt_Gurjar]

You are welcome. It is always satisfying for all involved in the discussion when a solution works after an extended post thread. Please test well.

I may also add that even though I mentioned [_THISROW_AFTER].[Column] and [Column]  is same, there are some edge cases where there is a subtle difference between the two.

Please see the post below and response by @Phil who had designed earlier versions of AppSheet automation and was part of the AppSheet team.

Is [_THISROW_AFTER] actually completely unnecessar... - Google Cloud Community