Automate Parent Status Rollup based on Descendants' statuses

Marc Shecter

Hi folks — has anyone figured out how to enable users to update statuses manually for child rows, and then have the parent rows’ status update automatically? There are multiple levels of hierarchy.

For example, I have three statuses that a child row can be; In Progress, Not Started, Completed. 

I have figured out the correct combo of “If”, “CountIfs”, “Descendant” functions etc. It works great if I use Row formulas and the user only changes the child rows.The logic is:

1.  if all the descendants are “Not Started” then the parent is “Not Started” or
2. if all the descendants are “Complete”, then the parent is “Complete” or
3. if neither above is true, then the parent is “In Progress”.

However, I have these problems that prevent this from actually working in the real world:

1. Using row formulas in the status column means that if a user manually changes the status in the row, the formula is deleted and replaced with the status. This is ok if the row remains a child and doesn’t become a parent. If the row becomes a parent at some time in the future, it won’t have the formula and won’t be auto update. 
2. Using Column formulas in the Status column prevents a user from manually changing the status on a row.  

I tried using Workflows, but it seems like it’s not possible for a workflow to evaluate the descendants the way I want it to. 

Thanks,

Marc

Marc Shecter

This is solved, and to give back to the community I am sharing our full solution that includes logic, helper columns, formulas, and workflows. You can access it in this Quip Document. Let me know if you have questions.

Regards,

Marc

Darren Mullen

@Marc Shecter you'd have to use a helper column that rolled up the status from the decedents then use an automation of the parent row to change the status on that row based on the helper column.

Marc Shecter

@Darren Mullen -- thank you so much for that tip. I've got it working part of the way, and I'm hoping you can help me cross the finish line.

This is a screen cap of my table:

The problem is that when I change the status on a grandchild like row 43 to In Progress, only the parent picks up the change. I need all the ancestors to pick it up.

For example, in the cap below, I've changed row 43 to In Progress. I would expect and need rows 38 and 23 to change to In Progress within the Helper: Rollup #2 column also. However, only the parent of row 43, row 41, changes to In Progress in the Helper: Rollup #2 column.

This is the column formula I have in Helper: Rollup #2 column.

=IF(COUNT(CHILDREN(Status@row)) > 0, IF(COUNTIF(CHILDREN(Status@row), "Complete") = COUNT(CHILDREN(Status@row)), "Complete", IF(COUNTIF(CHILDREN(Status@row), "Not Started") = COUNT(CHILDREN(Status@row)), "Not Started", "In Progress")))

I'd appreciate any additional guidance you could provide.

Thanks,

Marc

Darren Mullen

@Marc Shecter I didn't give it too much thought yet, but you might want to look at the DESCENDANTS function instead of the Children function or you may need to use a combination of both in your logic.

Marc Shecter

Thanks @Darren Mullen -- we got it working well. Please let me know if you'd like to try to break it. 😀

Marc Shecter

This is solved, and to give back to the community I am sharing our full solution that includes logic, helper columns, formulas, and workflows. You can access it in this Quip Document. Let me know if you have questions.

Regards,

Marc

Carson Kaufman

Hi Marc,

I just wanted to take a second and thank you for creating a sharable document of this process. This is really going above and beyond. I have already shared this with people I am working with, so your work is already helping others.

Thanks again!

axwestlake

Hi @Marc Shecter - Ditto to Carson's comment about going above and beyond. Your document was extremely helpful.

I do have one question. I followed the document and everything is working as expected with one exception. I get an error on Auto Status Rollup 1 automation indicating that "This workflow triggered itself directly or through another workflow. Please modify the workflow so it doesn't cause an infinite loop."

I'm wondering if you've heard of this before and if you have any suggestions for troubleshooting the issue. I've gone back and triple-checked that my formulas and automations are set up as you describe in your document and they are. I do have one additional status of Blocked that I'm using but it seems pretty straightforward on how to add one additional status into the automation.

Any suggestions? Thanks in advance!

Marc Shecter

Hi @axwestlake -- I'm sorry, I've not seen that error.

axwestlake

OK, thanks! Thought I'd try. 🙂

Dan Anzalone

Just adding onto this thread that I loved this workflow, but I am also experiencing the infinite loop error. I am attempting to do some testing to see if i can narrow it down.

So far all I have determined is that for some reason it seems to break at the project level, never at the phase level in my example below.

enterprise.apps31446

https://community.smartsheet.com/discussion/comment/373762#Comment_373762

Thank you SO MUCH for the linked detailed notes. This was very helpful as I am not a Project Manager.

Neon Brooks

@Dan Anzalone and @axwestlake I'm running into the same error. As far as I can tell, autostatusrollup1 is getting triggered on the grandparent row but not on the original parent row, which seems to get rolled up using autostatusrollup2. I can't quite figure out why 1 and 2 are both needed - I can see my rollup helper column updating for both rows, so I'm not sure why the more general autostatusrollup1 (when any field changes) is needed for the grandparent row. Have either of you found a solution to this problem?

Tagging @Marc Shecter in case he can shed light on why autostatusrollup2 can't do the whole job. Thanks so much Mark for your documentation of your solution!!

Neon Brooks

I think the answer may be provided by @James Keuning on the thread linked below. It appears that when I turn off the automation that changes blank statuses to "Not Started", the error does not occur. I'll need to do more testing to be sure.

EDIT: after more testing, it appears turning off the first automation has not solved the problem - I'm still getting errors.

https://community.smartsheet.com/discussion/120628/status-rollup-automation-becoming-invalid

Neon Brooks

Update: sadly I'm still getting the error.

Jose C

Hello, I really appreciate the document shared. For my use case I have tasks status of N/A. Can anyone help me incorporate this status to the formula shared by Marc. I would like the parent row to change to "Complete" if there is a mixture of "N/A" and "Complete" tasks.

=IF(COUNT(CHILDREN(Status@row)) > 0, IF(COUNTIF(CHILDREN(Status@row), "Complete") = COUNT(CHILDREN(Status@row)), "Complete", IF(COUNTIF(CHILDREN(Status@row), "Not Started") = COUNT(CHILDREN(Status@row)), "Not Started", "In Progress")))