Check to see if a row is a Parent
I am trying to check if a row is a "parent". My definition of a parent is a row that has any children or a single row with no children. I have a column, called isParent, that I am adding to each row a 0 for not a parent (so a child row) and a 1 to the parent row
I can get if the row has no children with this formula =IF(COUNT(CHILDREN()) > 0, 1, 0)
but struggling with the row that has no children, I tried doing something with Ancestors, but that didn't work.
Thanks
Answers
-
I'm not sure I follow that part about being a single row with no children. Wouldn't that then cover every row? Are you able to provide a screenshot for context?
-
Here is a screen shot. You see how "Developing Integration Guide" is a 0? I would like it to be a 1, as it doesn't have any children. But I don't want "Expo" or "Next Auth" to have a 1 because they have a Parent. I am using this formula now on the "isParent" column - =IF(COUNT(CHILDREN()) > 0, 1, 0)
-
@nmatviko This will work...
=IF( COUNT(PARENT(Feature@row)) = 0, 1, IF( COUNT(CHILDREN(Feature@row)) > 0, 1, 0))
"If it does not have a parent, then it is a parent row. If it has more than zero children, then it is a parent row; Otherwise, it is a child row."
When you use multiple hierarchy functions in one expression, you have to reference a non-empty cell; otherwise, you get a circular reference error or an unexpected result.
-
Try this:
=IF(COUNT(ANCESTORS()) = 0, 1)
-
That worked - Thank you!
-
Happy to help. 👍️
-
I tried to use this code =IF(COUNT(ANCESTORS()) = 0, 1) to determine parent or child row and it only worked for the first row of the project, for all the rest they were blank the column is called Parent Task? Do you know why this would not work for me?
Thanks,
Caroline
-
Hi @delaurellc
That formula is looking to see if the Count of Ancestors (or the count of parent row) is 0, which would only be the very top row in your sheet since you have multiple layers.
Instead, try using a Text/Number column and simply have the COUNT of the Ancestors without the 1, like so:
=COUNT(ANCESTORS([Task Name]@row))
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
@delaurellc If you are just wanting to flag all parents, you would want to count the children instead of the ancestors.
=IF(COUNT(CHILDREN([Task Name]@row))>0, 1)
-
Hi Paul and Genevieve,
I am trying to give an indicator to what is a parent row and what is a child row. So, we wanted Yes or No but if we need to use numbers I would say if it is a parent row 1 (Yes) and if it is a child Row 0 (No).
Genevieve - I already have the calculation you have set on my task level row to give me the level of the hierarchy that each task resides on.
Maybe I read the post wrong but thought this was to determine what was a parent and what was a child.
Caroline
-
Hi @delaurellc
Thanks for clarifying! If you have multiple levels, can you clarify what you mean by Parent and Child? Do you want any row that has children to have a "yes" regardless of what level?
If so, you could try something like this:
=IF(COUNT(CHILDREN([Task Name]@row)) > 0, "Yes", "No")
If the count of Child rows for the Task Name row is greater than 0, meaning it has child rows, then it's a generic Parent row so it gets a "yes".
Does that help?
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
That was exactly what I wanted, and the code worked perfectly!
Thank you!
Caroline
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 462 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!