Lookup values on one table and update value in another if contition is true

[Peter_S1]

Hi,

Wondering if someone could suggest the best way to accomplish the following.

I have a Projects table and a Tasks table. Projects table references the Tasks table and each project has multiple tasks. Both, the Projects and Tasks tables have a Status column to track the status. I would like the Project Status column to be automatically updated with “Completed” when all tasks have been marked “Completed”.

Would some sort of a lookup function work for this? Also, could this be done by placing an expression in the Formula field under Data -->Project Table → View Columns?

[Suvrutt_Gurjar]

If status is real column in Projects table, then you could use reference actions to update the parent table column based on changes in the child table column values. Please take a look at the sample app below. The app changes child table column based on parent table. You will need some tweaking.

https://www.appsheet.com/samples/This-app-shows-how-to-use-reference-actions?appGuidString=e76d2e73-...

If the status column in the Projects table can be VC and if the status column in the child table always has a nonblank default value such as say " In Progress", then you could use an expression something like below in the Projects table 's status VC

IF(IN(“In Progress”, [Related Tasks][Status]), “Completed”, “In Progress”)

[Peter_S1]

I sort of have this working after spending some time on setting up the reference actions. I also set up a workflow to fire off the actions on updates to the tasks table. I need some help with evaluating the Status column in Tasks table to see if all of the referenced rows have a value of “Completed” then only at that point trigger the actions to update the Project status to Completed.

[Suvrutt_Gurjar]

In general, the setting up of reference action is more complex as actions need to be set in two tables. You have also added a workflow which could be possibly achieved by using an event action on Task table form view. Also , how have you set up the status column will matter in solution. Thus, you may wish to post all relevant screenshots of actions and workflow and type of status columns in the two tables.

In general, you could use the same expression shared in earlier posts to update the status column in the parent table through reference action ( set the values of some columns in this row) set up in parent table.

IF(IN(“In Progress”, [Related Tasks][Status]), “In Progress”, “Completed”)

The above will ensure that the parent status column will update to “Completed” when all the related child records have a status of “Completed”

[Peter_S1]

Thank you, Suvrutt. I have included screenshots below. The Status columns are both real columns in both tables: Driver Onboarding and Onboarding Tasks (each driver will have multiple onboarding tasks).

First is the parent table where action is set to change status to completed.

This is the reference table where the reference column is defined and reference action is linked back to parent table.

This is the workflow that should trigger upon updates only to the tasks table. This is where I’m struggling with the condition. Status column of the Tasks table should not equal to In Progress or Not Started for the workflow to fire off. I tried using the NOT() expression in the condition, but it does not work. I’m not able to use your If() expression as the condition requires True/False expression. Please help.

[Suvrutt_Gurjar]

Thank you. I had also requested to post [Status] column setting. Assuming it has default status of “In Progress” and the only other status possible being “Completed”, please try following

1. Action: Update Driver Onboarding Status to Completed

Set these columns Status
Use expression : IF(IN(“In Progress”, [Related Tasks][Status]), “In Progress”, “Completed”)

2. Action: Update Driver Onboarding Status to Completed 2
Referenced Rows
LIST([Ref Column Name that refers the Driver Onboarding Table in Onboarding Tasks Table])

3. Workflow
Condition
AND( [_THISROW_BEFORE].[Status]<> [_THISROW_AFTER].[Status], [_THISROW_AFTER].[Status]=“Completed”)

[Peter_S1]

Thank you, Suvrutt. I must have misunderstood your request for the details on [Status] column. Please see below.
1. Action: Update Driver Onboarding Status to Completed
There are two Status columns and see their possible values below:
a) Driver Onboarding → Possible values: In Progress, Completed
b) Onboarding Tasks → Possible values: Not Started, In Progress, Completed

Given point b above, would you be able to show how to use the IF expression to evaluate for Not Started and In Progress conditions? I played around with IFS expression but could not get it to work properly.
Use expression : IF(IN(“In Progress”, [Related Tasks][Status]), “In Progress”, “Completed”)

2. Action: Update Driver Onboarding Status to Completed 2
Referenced Rows
LIST([Ref Column Name that refers the Driver Onboarding Table in Onboarding Tasks Table])
Currently this is what is have for Referenced Rows: onboarding Tasks[Key]
Do you think this would work?
I’m not able to wrap it in LIST because I get an error that it’s the wrong type as I’m using the Key column.

[Suvrutt_Gurjar]

1. Action: Update Driver Onboarding Status to Completed

Please try

IF(OR(IN(“In Progress”, [Related Tasks][Status]),IN(“Not Started”, [Related Tasks][Status])), “In Progress”, “Completed”)

2. Please use the reference type column in the Onboarding tasks table and wrap it by LIST()
   In the example below, the “Child” table is the equivalent of the “Onboarding Tasks” table. In this Chilld table [Parent] is the reference type column that refers the Parent table equivalent of the “Driver Onboarding” table. Please wrap this ref type column name with LIST() ,
   something like LIST([Parent])

If still any challenge, please post screenshots of the error and column list of the “Onboarding Tasks” table.

[Peter_S1]

Thank you, Suvrutt. I’ve made the suggested updates and get no errors but for some reason it does not update the Driver Onboarding Status to Completed when all the underlying tasks have been completed. Please see screenshots of the whole process below.

1) Update Driver Onboarding Status to Completed(Action)
Expression: IF(OR(IN(“In Progress”, [Related Onboarding Tasks][Status]),IN(“Not Started”, [Related Onboarding Tasks][Status])), “In Progress”, “Completed”)

2) Update Driver Onboarding Status to Completed 2 (Action)

3) Upon Completion of All Onboarding Tasks (Workflow)
Condition: AND( [_THISROW_BEFORE].[Status]<> [_THISROW_AFTER].[Status], [_THISROW_AFTER].[Status]=“Completed”)

[Suvrutt_Gurjar · Accepted solution]

Peter_S1:

2) Update Driver Onboarding Status to Completed 2 (Action)

For step 2, I request you to read my suggestion throughly in all previous posts. Please use the reference type column in the Onboarding Tasks table with list , not the reverse reference list column in the Drive Onboarding table. I have shown that in the example screenshot shared wherein the reference (ref) type column [Parent] in the child table is highlighted for use in the referenced rows expression by wrapping with list.

Also as requested please do the following needful

Suvrutt_Gurjar:

If still any challenge, please post screenshots of the error and column list of the “Onboarding Tasks” table.

Also please note that the syntax you have used for Referenced rows expression is incorrect. the column name needs to be included like LIST([Column Name]) and not LIST(Column Name) I request you to pay attention to the syntax part as well.

Edit: or if it is a list column itself then the expression can be a simple [Column Name] It need not be further wrapped by a LIST()

[Peter_S1]

As a follow up question to this: does the workflow not pick up data change if it is not user generated? So now that the Driver Onboarding status updates automatically from In Progress to Completed when all underlying tasks are completed, I’m trying to set a Completion Date to today when the Driver Onboarding is completed. I’ve created a task and a workflow just as I have done before for Onboarding Tasks but it did not work when I marked all tasks completed which in turn updates the Driver Onboarding status to Completed. But when I went in manually and changed the Driver Onboarding status from In Progress to Completed, it worked. Just wondering why that is and if there is a workaround for this?

[Suvrutt_Gurjar]

May I request you to start a new post when you wish to start a new discussion, It will help to get wider attention and a better solution due to that.

Regarding the latest discussion,

Peter_S1:

. I’ve created a task and a workflow just as I have done before for Onboarding Tasks but it did not work when I marked all tasks completed which in turn updates the Driver Onboarding status to Completed.

Please refer post 4 in the below thread.

Trigger workflow Questions

@jujogar10 Workflow triggers work only once. You cannot trigger a workflow by changing a value with another workflow. That’s only doable with using AppSheet API.

For the below requirement,

Peter_S1:

Just wondering why that is and if there is a workaround for this?

Could you evaluate the ChangeTimeStamp column type that can listen to the “Status” column in your requirement?

help.appsheet.com

Tracking Changes Using "Change" Column Types

How to keep track of when and where data is changed in your app.

https://www.appsheet.com/samples/Keep-track-of-when-columns-change?appGuidString=8a1572da-c548-418c-...

[Peter_S1]

Got it, will start new threads going forward. Actually, I used the referenced workflow to accomplish updating and removing completion date as well based on Onboarding Task status changes.
Thank you very much for your help.

[Peter_S1]

It works! I originally had the Ref type column but it was giving me errors so I thought to maybe try reverse reference list (which didn’t produce an error but also did not work).

Thank you very much, Suvrutt! Could not have done it without your help. Really, really appreciate it.