Including child rows in filters
I have a follow-up question to a question I asked earlier today about including parent rows in a filter. Now I'm wondering: is there a way to create a filter and include the matching rows' child rows (when the children don't match the filter criteria)?
So say I created a filter for all rows matching "teaching/mentoring" but wanted the child rows that contain the specifics of those engagements. Is there a way to do that?
Best Answer
-
You can create a PARENT column, which will add the parent value to each child record, something like this:
=IF(COUNT(ANCESTORS(ID@row)) = 0, "Top", (INDEX(ANCESTORS(ID@row), (COUNT(ANCESTORS(ID@row))))))
Then when you create the filter, use OR to filter if the value is in the primary field, or the PARENT field.
Answers
-
You can create a PARENT column, which will add the parent value to each child record, something like this:
=IF(COUNT(ANCESTORS(ID@row)) = 0, "Top", (INDEX(ANCESTORS(ID@row), (COUNT(ANCESTORS(ID@row))))))
Then when you create the filter, use OR to filter if the value is in the primary field, or the PARENT field.
-
Thank you!!!
-
@James Keuning I'm trying to adapt this formula, but I'm struggling to get it working correctly. For reasons that are not quite clear to me, I've managed to get it working against my Primary column, but when I attempt to apply it to a different column - the one whose value I want to have applied to the children - it doesn't behave the same way.
I'm thinking it's because I'm struggling with understanding how that Index is behaving - would you mind breaking down what it's doing there?
-
@Brian Pitts It should work in other columns. The INDEX basically says, "Make a set of values that is comprised of all of the ANCESTORS for this field. And then the "1" part of the INDEX says, "and tell me the first value in that list."
So if you put JUST the INDEX part in a cell, it will error in the records with the top parent, because that cell has no parent.
Top Parent
=IF(COUNT(ANCESTORS([Primary Column]@row)) = 0, [Primary Column]@row, INDEX(ANCESTORS([Primary Column]@row), 1))Just Index
=INDEX(ANCESTORS([Primary Column]@row), 1)The #INVALID VALUE is because that record has no ANCESTOR
TOP Parent Name Using Index
=IF(COUNT(ANCESTORS([Person Name]@row)) = 0, [Person Name]@row, INDEX(ANCESTORS([Person Name]@row), 1))INDEX using 2 instead of 1
=IF(COUNT(ANCESTORS([Person Name]@row)) = 0, [Person Name]@row, INDEX(ANCESTORS([Person Name]@row), 2))The two INVALIDS are because those have no second position in the index, since they only have one ANCESTOR, there is no 2
The Grandbaby works because the parent of the grandbaby is the second in the INDEX. The parents work because the formula says, "IF this is the top layer, and there is no ANCESTOR, then return the value of the field." The Just Index field errors out here because we do not lead with that IF statement.
-
This is so exceptionally helpful @James Keuning I really appreciate you breaking it down for me (and apologies for my delay in responding - I was out of the office for a bit!)
-
Super helpful I had the same issue and this did the trick!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
