Keeping Parent with Children when Filtering
I have a sheet with a list of courses to develop each term. Each row represents a course and modality (online OR blended). In most cases, only one modality is developed for each course.
In some situations, we work on both online and blender modalities at the same time. For these courses, I have a parent with some course information, but then a child for each modality.
If I filter, say see all Online courses, the parent doesnt show up if any children are listed as online.
Is there a way that when filtered, the parent is automatically included if any of its children satisfy the filter criteria?
ROW 1: CUL 110 BL
ROW 2: HOS 234 OL
ROW 3: HRS 328 (Parent)
ROW 4: BL (for HRS 328, Child)
ROW 5: OL (for HRS 328, Child)
Rows 1 and 2 are not parents/children cause they are single courses with only 1 modality. Row 3 is a parent, with two children, because there are two modalities to develop.
In a filter for OL (online) only, the relevant course info in Parent row (row) does not show in results, just Row 5. I would like for Row 3 (Parent) to show too since one of its children do.
Thoughts?
Comments
-
You could include a helper column and in each parent row use a formula (a basic JOIN should work) to list out the modalities of the children rows. You can then filter based on that helper column to show rows that "contain" Online. For ease of use building filters, you could use two helper checkbox columns, one for each modality. You could then sort by if Online is checked or if Blended is checked. Both of those would use simple formulas as well.
=IFERROR(IF(FIND("Online", CHILDREN(Modality@row)) > 0, 1), IF(FIND("Online", Modality@row) > 0, 1))
This will look at the CHILDREN of the parent rows for the word Online in the Modality column. If it is a child row then it would throw an error, so the IFERROR says to just look at the Modality column of that row if there is an error (not a parent row).
I hope that makes sense. If you need a more detailed explanation, don't hesitate to ask. I use something very similar to this to build filters on shared sheets.
-
@Paul Newcome Hello Paul,
I believe I have a similar situation. Large Worksheet with many parent rows and 3 different project managers. I thought to create a filter (I went with report first, however, was unable to display child rows, so switch my thinking to filter) for each project manager, however, I am finding that only the parent rows are showing up in the filtered view. I need all the child rows under the parent rows that belong to each project manager to also show up so that they can manage/update their respective tasks.
In reading the above I believe i need a 'helper' row that I can use in filter as well? Can you confirm that is the route I need to take?
Thank you.
-
@PeggyLang I would suggest a helper column (contact type) with the following formula:
=IF(COUNT(CHIDLREN([Task Name]@row)) = 0, PARENT([Project Manager]@row), [Project Manager]@row)
You should then be able to create a report filtered by user based on this helper column.
-
I hope you're well and safe!
I noticed a typo,
=IF(COUNT(CHILDREN([Task Name]@row)) = 0, PARENT([Project Manager]@row), [Project Manager]@row)
I hope that helps!
Be safe, and have a fantastic week!
Best,
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
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.
-
The formula only populated the Project Mgr rows of the parents though.
Is the idea that the formula would populate all of the children as well?
-
Which level is the actual assignment being made on?
-
Assignment is being made on VERY first Parent row.
In screenshot below line '0001 CHARLOTTE' and '0022 - CHARLOTTE POWER & HVAC PC022'.
There are approx 100 children and/or grandchildren under each top parent.
-
@PeggyLang In that case, give this a try:
=IF(COUNT(CHILDREN([Task Name]@row)) = 0, INDEX(ANCESTORS([Project Manager]@row), 1), [Project Manager]@row)
-
@Paul Newcome THANK YOU!!!
This worked!
-
Happy to help. 👍️
-
@Paul Newcome I have another need with the same smartsheet.
I am trying to build reports for different metrics and believe I have the need to have the location (i.e., 0001 CHARLOTTE) appear in a helper row on EACH of the child, grandchildren, great grandchildren and so on under 0001 CHARLOTTE, and then the same for '0022 - CHARLOTTE POWER & HVAC PC022' and so on.
Is there another way other than painstakingly copying and pasting?
-
@PeggyLang You could use an INDEX/ANCESTORS combo.
=INDEX(ANCESTORS(Location@row), #)
Just change the # to whatever level the location is on. 1 would be the first level of hierarchy (no indentation), 2 would be indented once, so on and so forth.
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives