Formula to check a box or change status of a parent to complete when all the children are complete

Terri1019

Hello. I would like to have a parent status automatically change to Complete when all of the children rows have a status of Complete. I created a couple helper columns to accomplish this and then was planning to use automation for "when the parent-complete box is checked, change the status to Complete". However, something is off. The Helper for Parent box checks no matter the status on the most indented child row (see row 3 and 6). Part of it seems to work (see lines 5 and 6 in example 2). Any ideas on a formula? Or is there an easier way to accomplish this?

The Helper for Complete formula is =IF(Status@row = "complete", 1, 0)

The Helper for Parent Complete formula is =IF(COUNTIFS(CHILDREN([Helper Column for Complete Status]@row), 0) = 0, 1, 0)

Genevieve P.

Hey @Terri1019

You can skip one of your helper columns and go right to the Parent formula - "Helper for Parent Complete":

=IF(COUNT(CHILDREN(Task@row)) >0, IF(COUNT(CHILDREN(Task@row)) = COUNTIF(CHILDREN(Status@row), "Complete"), 1, 0)

This first checks to see if the current row has a Child row or not. If it does (so if the count of children is greater than 0), then it moves on to the rest of the formula.

The second half of the formula counts how many child rows there are for this current parent based on text in the Task column. Then if the number of child rows is the same as the number of child rows that have "Complete" in the status column (meaning all child rows are complete), then it checks the box!

Let me know if this makes sense and works for you.

Cheers,

Genevieve

Genevieve P.

Hey @Terri1019

You can skip one of your helper columns and go right to the Parent formula - "Helper for Parent Complete":

=IF(COUNT(CHILDREN(Task@row)) >0, IF(COUNT(CHILDREN(Task@row)) = COUNTIF(CHILDREN(Status@row), "Complete"), 1, 0)

This first checks to see if the current row has a Child row or not. If it does (so if the count of children is greater than 0), then it moves on to the rest of the formula.

The second half of the formula counts how many child rows there are for this current parent based on text in the Task column. Then if the number of child rows is the same as the number of child rows that have "Complete" in the status column (meaning all child rows are complete), then it checks the box!

Let me know if this makes sense and works for you.

Cheers,

Genevieve

Terri1019

Thank you so much Genevieve! That is exactly what I needed! I appreciate you taking the time to help.