How to create automation that only works on parent rows.

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?


Tags:

Best Answers

  • Hollie Green
    Hollie Green ✭✭✭✭✭✭
    edited 03/07/24 Answer ✓

    =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
    Hollie Green ✭✭✭✭✭✭
    Answer ✓

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

  • Hollie Green
    Hollie Green ✭✭✭✭✭✭
    Answer ✓

    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))

Answers

  • Mr. Chris
    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.

  • @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)


  • @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
    Hollie Green ✭✭✭✭✭✭
    edited 03/07/24 Answer ✓

    =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 amazing, thank you so much! This did the trick.

  • @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
    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.

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

  • Hollie Green
    Hollie Green ✭✭✭✭✭✭
    Answer ✓

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

  • @Hollie Green this is perfect, thank you!

  • Hollie Green
    Hollie Green ✭✭✭✭✭✭
    Answer ✓

    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))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!