how to change Parent status based on Child row checkbox?

MattB7474

= "Not Started" if NO boxes checked

= "In Progress" if SOME boxes are checked

= "Complete" if ALL boxes are checked

for the attached example I manually changed status based on checked boxes, but I'm hoping to find a way to do it automatically with a formula.

I've only had success with formulas changing status within the same row for individual checkboxes, but I am struggling to work out a formula to decipher between none/some/all

Thanks

prime_nathaniel

@MattB7474

Make a helper column to determine if you are a root parent row, mine is called Ancestory

=COUNT(ANCESTORS([Primary Column]@row))

The yellow columns I have made explicit so you can learn how to do this, you do not need the yellow columns in your solution, it is just for teaching the concept.

Only Status for Parent,
given we have acestory, we know that anything with 0 ancestors has to be a top level parent, and these are the only ones we want a status for, if it isn't a topline parent we can return blank ""

=IF(Ancestory@row = 0, "status", "")

Count off Children with C = true
We can now use the children() formula to get just the children of that current row, then we countif those children to see if C is true. The yellow column runs on ALL rows, but this won't matter once we combine everything. Again this is just so we can demonstrate how these aggregates and hierarchies work.

=COUNTIFS(CHILDREN(C@row), true)

Count off Children We can also get the total number of children so we can compare if ALL, SOME, or NONE of the items are true. Ie this is the total possible items that COULD be true

=COUNT(CHILDREN(C@row))

Return Text Based on Count with C against total possible that could be true
To return text based on any logic we use if() which is if(test, true, false). So if our count of Children with C = Total Count we know that every possible child is done and thus we can return "Complete. For the false of this first if() that is everything that has atleast 1 entry where C is not true, it could be some items or no items are C is true. So we need to add a second if() to check if NO items are true, if we get a true on our test here we can return "Not Complete". Now the false case of that second if() is ONLY possible that SOME items are not C = true so we can return "In Progress".

=IF([Count of Children with C]@row = [Count of Children]@row, "Complete", IF([Count of Children with C]@row = 0, "Not Complete", "In Progress"))

Now that we have all the components we need we can logically assemble them into ONE formula.

if you were to write this out in text,

IF row has no ancestors THEN check if the count of c=true items for that parent are equal to total c items for that parent and return "complete" OTHERWISE check if the count of c=true items for that parent are equal to 0 and return "Not Complete" ANYTHING ELSE mark as "In progress" without doing any more tests.

That translates to:

=IF(Ancestory@row = 0, IF(COUNTIFS(CHILDREN(C@row), true) = COUNT(CHILDREN([Primary Column]@row)), "Complete", IF(COUNTIFS(CHILDREN(C@row), true) = 0, "Not Complete", "In Progress")), "")

prime_nathaniel

@MattB7474

Make a helper column to determine if you are a root parent row, mine is called Ancestory

=COUNT(ANCESTORS([Primary Column]@row))

The yellow columns I have made explicit so you can learn how to do this, you do not need the yellow columns in your solution, it is just for teaching the concept.

Only Status for Parent,
given we have acestory, we know that anything with 0 ancestors has to be a top level parent, and these are the only ones we want a status for, if it isn't a topline parent we can return blank ""

=IF(Ancestory@row = 0, "status", "")

Count off Children with C = true
We can now use the children() formula to get just the children of that current row, then we countif those children to see if C is true. The yellow column runs on ALL rows, but this won't matter once we combine everything. Again this is just so we can demonstrate how these aggregates and hierarchies work.

=COUNTIFS(CHILDREN(C@row), true)

Count off Children We can also get the total number of children so we can compare if ALL, SOME, or NONE of the items are true. Ie this is the total possible items that COULD be true

=COUNT(CHILDREN(C@row))

Return Text Based on Count with C against total possible that could be true
To return text based on any logic we use if() which is if(test, true, false). So if our count of Children with C = Total Count we know that every possible child is done and thus we can return "Complete. For the false of this first if() that is everything that has atleast 1 entry where C is not true, it could be some items or no items are C is true. So we need to add a second if() to check if NO items are true, if we get a true on our test here we can return "Not Complete". Now the false case of that second if() is ONLY possible that SOME items are not C = true so we can return "In Progress".

=IF([Count of Children with C]@row = [Count of Children]@row, "Complete", IF([Count of Children with C]@row = 0, "Not Complete", "In Progress"))

Now that we have all the components we need we can logically assemble them into ONE formula.

if you were to write this out in text,

IF row has no ancestors THEN check if the count of c=true items for that parent are equal to total c items for that parent and return "complete" OTHERWISE check if the count of c=true items for that parent are equal to 0 and return "Not Complete" ANYTHING ELSE mark as "In progress" without doing any more tests.

That translates to:

=IF(Ancestory@row = 0, IF(COUNTIFS(CHILDREN(C@row), true) = COUNT(CHILDREN([Primary Column]@row)), "Complete", IF(COUNTIFS(CHILDREN(C@row), true) = 0, "Not Complete", "In Progress")), "")

MattB7474

https://community.smartsheet.com/discussion/comment/479166#Comment_479166

Incredibly helpful, thank you for the time and effort you put into this

prime_nathaniel

@MattB7474

You are most welcome! anytime!