Automation - Moving Grouped (parent/child) Rows
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
-
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))
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Answers
-
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.
-
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!
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
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?
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
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
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
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 ... ?
-
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))
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
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!!!
-
@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" ??
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
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!!!
-
No problem! Glad I could help 🙂
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!