Automation - Moving Grouped (parent/child) Rows

Options

Hello Smartsheet Community!

We have automations set up to move "completed" rows when they meet certain criteria (status is complete, row is invoiced, etc).

If we have rows that are "grouped" as parent/child rows, and only one of the rows meets the criteria to be moved, will both rows be moved regardless, or will the automation not work until both rows meet the correct criteria?

Example:

Parent Row = complete and invoiced

Child Row = complete not yet invoiced

Thank you in advance for the help!

Best Answers

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓
    Options

    Hi @Nancy Heater

    Ah, thank you, yes this context does make a difference! In this case, we need to check all the child rows and the current row.

    It's also helpful to know you have two criteria, "Complete" and "Yes", versus one criteria of "Completed and yes".

    Try this:

    =IF(COUNT(ANCESTORS([Primary Column]@row)) > 0, 0, IF(AND(COUNT(CHILDREN([Primary Column]@row)) = COUNTIF(CHILDREN(Status@row), "Complete"), COUNT(CHILDREN([Primary Column]@row)) = COUNTIF(CHILDREN(Invoiced@row), "Yes"), Status@row = "Complete", Invoiced@row = "Yes"), 1, 0))

    I'll break it down for you:

    If the current row is a CHILD row, immediately make sure the box is not checked because we only care about top-level rows. This is because moving a Parent moves all the Children as well, so we can ignore these rows.

    =IF(COUNT(ANCESTORS([Primary Column]@row)) > 0, 0, 

    Now our 4 criteria:

    IF(AND(

    If all the Child rows say "Complete"

    COUNT(CHILDREN([Primary Column]@row)) = COUNTIF(CHILDREN(Status@row), "Complete"),

    AND all the Child rows say "Yes" in the invoiced column

    COUNT(CHILDREN([Primary Column]@row)) = COUNTIF(CHILDREN(Invoiced@row), "Yes"),

    AND the current column says "Complete"

    Status@row = "Complete",

    AND the current invoice is "yes":

    Invoiced@row = "Yes),

    Then check the box. Otherwise, don't check the box.

    1, 0))

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • Genevieve P.
    Genevieve P. Employee
    edited 02/17/23 Answer ✓
    Options

    Hi @Nancy Heater

    No problem! You can simply take out that part of the statement and replace it with the other column name and criteria:

    =IF(COUNT(ANCESTORS([Primary Column]@row)) > 0, 0, IF(AND(COUNT(CHILDREN([Primary Column]@row)) = COUNTIF(CHILDREN(Status@row), "Complete"), COUNT(CHILDREN([Primary Column]@row)) = COUNTIF(CHILDREN(Invoiced@row), "Yes"), Status@row = "Complete", Invoiced@row = "Yes"), 1, 0))

    Change it:

    =IF(COUNT(ANCESTORS([Primary Column]@row)) > 0, 0, IF(AND(COUNT(CHILDREN([Primary Column]@row)) = COUNTIF(CHILDREN(Status@row), "Complete"), COUNT(CHILDREN([Primary Column]@row)) = COUNTIF(CHILDREN(Invoiced@row), "Yes"), Status@row = "Complete", [Column Name]@row = "Criteria"), 1, 0))

    So in your case:

    =IF(COUNT(ANCESTORS([Primary Column]@row)) > 0, 0, IF(AND(COUNT(CHILDREN([Primary Column]@row)) = COUNTIF(CHILDREN(Status@row), "Complete"), COUNT(CHILDREN([Primary Column]@row)) = COUNTIF(CHILDREN(Invoiced@row), "Yes"), Status@row = "Complete", [Tracking Number]@row <> ""), 1, 0))

    Cheers,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

«1

Answers

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Options

    Hi @Nancy Heater

    I hope you're well and safe!

    It depends on how the Workflow is structured.

    How would you like it to work?

    I hope that helps!

    Have a fantastic week & Happy New Year!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Nancy Heater
    Nancy Heater ✭✭✭✭
    Options

    Hello @Andrée Starå !!!

    We would like the automation to NOT move the grouped rows until all rows in the group meet the required criteria to be moved.

    I have set up a test sheet with a scenario and automation. Link is below:

    Thanks for your help!

  • Genevieve P.
    Options

    Hi @Nancy Heater

    You are correct! The current behaviour is that if a Parent row is moved, all the children go with it.

    What I would suggest doing is have a different helper column on your sheet that only checks a box in the parent row IF all the Children meet your criteria.

    Then you can set the workflow with a condition that the new checkbox has to be checked in order for the row (and all its children) to move. Does that make sense?

    Cheers,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • Nancy Heater
    Nancy Heater ✭✭✭✭
    Options

    Hi @Genevieve P. !! That makes total sense, however I have more questions.

    If we use the helper column as you suggested, how does Smartsheet know which row is a parent vs a child to set up such conditional/formulas?

  • Genevieve P.
    Genevieve P. Employee
    edited 01/20/23
    Options

    Hi @Nancy Heater

    There are formulas that specifically look at Parent/Child relationships! 🙂

    For example, you can COUNT how many child rows meet a criteria, and check the box if all children have the same value:

    =IF(COUNT(CHILDREN([Primary Column]@row)) = COUNTIF(CHILDREN(Status@row), "complete and invoiced"), 1, 0)

    See: CHILDREN Function and Reference Children, Parents, and Ancestors with Hierarchy Functions

    Cheers,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • Nancy Heater
    Nancy Heater ✭✭✭✭
    Options


    Thanks, @Genevieve P. !!! I added your formula to my test sheet, but its checking all of the child rows, regardless if they have the right info in the right columns for complete and invoiced.

    Wouldn't both the parent and child rows need to be checked when all the criteria is met? So the workflow knows all the rows are OK to be moved? ... or am I just not understanding this one (I'm going go with the latter ... )

    Link to test sheet: https://app.smartsheet.com/b/publish?EQBCT=98259ee80f37420b8a39b7238a8664d9

  • Genevieve P.
    Genevieve P. Employee
    edited 01/20/23
    Options

    Hi @Nancy Heater

    My apologies for not being clear! This formula would only go in Parent rows. Since moving a Parent row moves all its children, there's no need to check off the Child Rows, although we can if you'd like!

    Here's an adjusted formula you could put as a Column Formula, if that's your preference:

    =IF(COUNT(ANCESTORS([Primary Column]@row)) > 0, 0, IF(COUNT(CHILDREN([Primary Column]@row)) = COUNTIF(CHILDREN(Status@row), "complete and invoiced"), 1, 0))

    I also notice in your sheet the value you're looking for is "Complete" not "complete and invoiced" so you'll want to update that in the formula:

    =IF(COUNT(ANCESTORS([Primary Column]@row)) > 0, 0, IF(COUNT(CHILDREN([Primary Column]@row)) = COUNTIF(CHILDREN(Status@row), "Complete"), 1, 0))

    Do you need the Parent to separately say "Complete" as well?

    Cheers!

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • Nancy Heater
    Nancy Heater ✭✭✭✭
    Options

    Hi @Genevieve P. I apologize that I am getting all sorts of turned around on this one ... note to self, no crazy smartsheet formula work on Fridays ... lol

    The actual spreadsheet where this will be used will have single rows and also parent/child rows. I updated our test sheet to have some single rows as well.

    We need the automation to know that if there is a single row, it can't be moved until it has a Status of "Completed" and Invoiced is "yes" AND if there are parent/child rows, none of them rows in that group can be moved until all of them have a Status of "Completed" and Invoiced is "yes".

    Does that help ... ?

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓
    Options

    Hi @Nancy Heater

    Ah, thank you, yes this context does make a difference! In this case, we need to check all the child rows and the current row.

    It's also helpful to know you have two criteria, "Complete" and "Yes", versus one criteria of "Completed and yes".

    Try this:

    =IF(COUNT(ANCESTORS([Primary Column]@row)) > 0, 0, IF(AND(COUNT(CHILDREN([Primary Column]@row)) = COUNTIF(CHILDREN(Status@row), "Complete"), COUNT(CHILDREN([Primary Column]@row)) = COUNTIF(CHILDREN(Invoiced@row), "Yes"), Status@row = "Complete", Invoiced@row = "Yes"), 1, 0))

    I'll break it down for you:

    If the current row is a CHILD row, immediately make sure the box is not checked because we only care about top-level rows. This is because moving a Parent moves all the Children as well, so we can ignore these rows.

    =IF(COUNT(ANCESTORS([Primary Column]@row)) > 0, 0, 

    Now our 4 criteria:

    IF(AND(

    If all the Child rows say "Complete"

    COUNT(CHILDREN([Primary Column]@row)) = COUNTIF(CHILDREN(Status@row), "Complete"),

    AND all the Child rows say "Yes" in the invoiced column

    COUNT(CHILDREN([Primary Column]@row)) = COUNTIF(CHILDREN(Invoiced@row), "Yes"),

    AND the current column says "Complete"

    Status@row = "Complete",

    AND the current invoice is "yes":

    Invoiced@row = "Yes),

    Then check the box. Otherwise, don't check the box.

    1, 0))

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • Nancy Heater
    Nancy Heater ✭✭✭✭
    Options

    Good morning @Genevieve P. - thank you so much for the breakdown explanation. That helped so much!!!

    Now that we have this formula plugged in, I adjusted the automation to only move rows where the "Helper" columns is checked. Even though the child rows don't get checked, with this formula, if the parent row is checked we know the child rows are good to go and can be moved too!! Yay!!!!

    I cannot thank you enough for your patience and working thru this with me! Thank you again for all your help!!!

  • Nancy Heater
    Nancy Heater ✭✭✭✭
    Options

    @Genevieve P. ... a question came up when talking with my team this morning.

    How would we need to change this if we needed to substitute one of the criteria ... instead of invoiced = yes, would we be able to look for tracking number = "is not blank" ??

  • Genevieve P.
    Genevieve P. Employee
    edited 02/17/23 Answer ✓
    Options

    Hi @Nancy Heater

    No problem! You can simply take out that part of the statement and replace it with the other column name and criteria:

    =IF(COUNT(ANCESTORS([Primary Column]@row)) > 0, 0, IF(AND(COUNT(CHILDREN([Primary Column]@row)) = COUNTIF(CHILDREN(Status@row), "Complete"), COUNT(CHILDREN([Primary Column]@row)) = COUNTIF(CHILDREN(Invoiced@row), "Yes"), Status@row = "Complete", Invoiced@row = "Yes"), 1, 0))

    Change it:

    =IF(COUNT(ANCESTORS([Primary Column]@row)) > 0, 0, IF(AND(COUNT(CHILDREN([Primary Column]@row)) = COUNTIF(CHILDREN(Status@row), "Complete"), COUNT(CHILDREN([Primary Column]@row)) = COUNTIF(CHILDREN(Invoiced@row), "Yes"), Status@row = "Complete", [Column Name]@row = "Criteria"), 1, 0))

    So in your case:

    =IF(COUNT(ANCESTORS([Primary Column]@row)) > 0, 0, IF(AND(COUNT(CHILDREN([Primary Column]@row)) = COUNTIF(CHILDREN(Status@row), "Complete"), COUNT(CHILDREN([Primary Column]@row)) = COUNTIF(CHILDREN(Invoiced@row), "Yes"), Status@row = "Complete", [Tracking Number]@row <> ""), 1, 0))

    Cheers,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • Nancy Heater
    Nancy Heater ✭✭✭✭
    Options

    Hey @Genevieve P. -

    Thank you so much for your help with this. Apologies for my delayed reply, as I got pulled on to something else and am just now circling back to this.

    Thanks again and have a great day!!!

  • Genevieve P.
    Options

    No problem! Glad I could help 🙂

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • Nancy Heater
    Nancy Heater ✭✭✭✭
    Options

    Unfortunately, when I am pasting this in as you have it above, for some of the rows, we are getting an "invalid operation".

    Helper1 column is the "original" formula

    Helper2 column is the updated formula where we're using [Tracking Number]@row = <> ""


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!