Automation - Moving Grouped (parent/child) Rows

Nancy Heater

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!

Genevieve P.

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

Genevieve P.

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

Andrée Starå

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!

Nancy Heater

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:

https://app.smartsheet.com/b/publish?EQBCT=98259ee80f37420b8a39b7238a8664d9

Thanks for your help!

Genevieve P.

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

Nancy Heater

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.

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

Nancy Heater

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.

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

Nancy Heater

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.

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

Nancy Heater

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

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

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

Nancy Heater

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.

No problem! Glad I could help 🙂

Nancy Heater

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 = <> ""