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?
Solved! Go to Solution.
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
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()
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.
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โ)
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.
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โ
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.
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โ)
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.
Please try
IF(OR(IN(โIn Progressโ, [Related Tasks][Status]),IN(โNot Startedโ, [Related Tasks][Status])), โIn Progressโ, โCompletedโ)
If still any challenge, please post screenshots of the error and column list of the โOnboarding Tasksโ table.
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โ)
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
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()
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?
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,
Please refer post 4 in the below thread.
For the below requirement,
Could you evaluate the ChangeTimeStamp column type that can listen to the โStatusโ column in your requirement?
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.
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.
User | Count |
---|---|
40 | |
34 | |
29 | |
23 | |
17 |