Automation Work Flow

AAL · 10/10/23

Hello Smartsheet Community,

I've implemented an automation where changing the End Date field automatically sets the Status to "Completed" and updates % Complete to "100%."

Here's the issue: When I update the End Date for line 42, it correctly updates the status to Completed and % Complete to 100%. However, line 41, which is its parent line, also has its End Date updated. This, in turn, marks the parent line as Completed, even though there are several child items under it that are still In Progress.

I'm looking for a solution to this problem. Is there a way to ensure that the parent line's status accurately reflects the status of its child items? Your assistance is much appreciated. Thanks!"

Paul Newcome · 10/11/23

How exactly are you rolling it up to the parent row?

AAL · 10/11/23

Hi Paul,

Look at line 20, 21. Line 20 is the line 21's parent row, see below

Once I update the end date on line 21, it will automatically update the status to "Completed", and % Complete to 100% and the whole line item will turn GREEN, see below. However, Line 20 is the parent line of line 21, so it will update the End date, so once update the End date, it also trigger the automation I set up which is update the status to "Completed" and % Complete to 100% which is I don't want it cos actually there are 3 more items under this parent have not yet completed. Please see below.

I'm looking for a solution to this problem. Is there a way to ensure that the parent line's status accurately reflects the status of its child items?

Your assistance is much appreciated. Thanks!

Paul Newcome · 10/11/23

Right. I understand that. But how is the End Date on row 20 being populated?

AAL · 10/11/23

Hi Paul,

I think it may be the dependency enabled. I can't put the date on row 20 because it's a parent row. Once I update the end date in the child row, the parent row end date will be updated.

Paul Newcome · 10/11/23

You shouldn't be able to change cells via automation in a dependency column.

Either way, try inserting a helper checkbox column and using this formula:

=IF(COUNT(CHILDREN([Task Name]@row)) = 0, 1)

Then include a condition in your automation to only run on rows where this box is checked.

Automation Work Flow

Answers

Categories