How to create automation that only works on parent rows.

Alicia D

I am trying to create an automation so that when the parent row is marked as complete, then the parent row and all of the children move to another sheet. I am just not able to figure out how to set the automation so that it ONLY moves if the parent row is selected and not the children rows. I did a formula so that if the parent row is selected, then it selects all of the children rows. But is there a way to do a helper column so that it only triggers the automation once a parent row is selected?

Hollie Green

=IF(AND(Complete@row = "Complete", COUNTIFS(CHILDREN(Complete@row), @cell = "Complete") > 0), 1, IF(COUNTIFS(ANCESTORS(Complete@row), @cell = "Complete") > 0, 1, ""))

This will check the parent row and all the children if the parent row is marked complete but will not mark if the parent row is not marked complete

Hollie Green

=IF(AND(Complete@row = "Complete", COUNTIF(CHILDREN(Complete@row), >0) >= 0), 1, IF(COUNTIFS(ANCESTORS(Complete@row), @cell = "Complete") > 0, 1, ""))

Hollie Green

Just in case someone finds this post later looking for a way to only check the box if all parent and children are marked complete. You can do the below.

If you add an additional helper column, I called it Parent you can use the below formula in the Parent column.

=IF(PARENT([Task Name]@row) = "", [Task Name]@row, PARENT([Task Name]@row))

Then in your checkbox column use

=IF(AND(Complete@row = "Complete", COUNTIFS(CHILDREN(Complete@row), @cell = "Complete") = COUNT(CHILDREN())), 1, IF(AND(COUNTIFS(ANCESTORS(Complete@row), @cell = "Complete") > 0, COUNTIFS(Parent:Parent, Parent@row, Complete:Complete, "Complete") = COUNT(Parent:Parent, Parent@row)), 1, 0))

Mr. Chris

You can create a helper column that determines if the Primary Column is a Parent with an IF.

=IF([Primary Column]@row = PARENT(), "Parent", "Child")

Then either build on this formula, or reference the formula to determine how to handle Parent, Child cells.

Alicia D

@Mr. Chris , thank you for your response! When I do this, it doesn't seem to pick it up correctly. For instance, there is a child row labeled as a parent row, and some parent rows labeled as children rows (blue are parent rows and white are child rows)

Alicia D

@Mr. Chris it is seeming that if something is marked as In progress, it is calling it a child row, and if it is blank, it counts it as a parent row.

Hollie Green

=IF(AND(Complete@row = "Complete", COUNTIFS(CHILDREN(Complete@row), @cell = "Complete") > 0), 1, IF(COUNTIFS(ANCESTORS(Complete@row), @cell = "Complete") > 0, 1, ""))

This will check the parent row and all the children if the parent row is marked complete but will not mark if the parent row is not marked complete

Alicia D

@Hollie Green amazing, thank you so much! This did the trick.

Alicia D

@Hollie Green , I may have spoke too soon! This worked for all of my existing data, but when I added new info to test the automation, and marked the parent row to complete, it only checked all of the child rows but not the parent row as well.

Hollie Green

It's because none of your child rows are marked as complete. Let me see what I can do if they child rows aren't marked complete to get it to still work.

Alicia D

@Hollie Green ahh okay that makes sense. Thank you, that would be great!

Hollie Green

=IF(AND(Complete@row = "Complete", COUNTIF(CHILDREN(Complete@row), >0) >= 0), 1, IF(COUNTIFS(ANCESTORS(Complete@row), @cell = "Complete") > 0, 1, ""))

Alicia D

@Hollie Green this is perfect, thank you!

Hollie Green

Just in case someone finds this post later looking for a way to only check the box if all parent and children are marked complete. You can do the below.

If you add an additional helper column, I called it Parent you can use the below formula in the Parent column.

=IF(PARENT([Task Name]@row) = "", [Task Name]@row, PARENT([Task Name]@row))

Then in your checkbox column use

=IF(AND(Complete@row = "Complete", COUNTIFS(CHILDREN(Complete@row), @cell = "Complete") = COUNT(CHILDREN())), 1, IF(AND(COUNTIFS(ANCESTORS(Complete@row), @cell = "Complete") > 0, COUNTIFS(Parent:Parent, Parent@row, Complete:Complete, "Complete") = COUNT(Parent:Parent, Parent@row)), 1, 0))