Keeping Parent with Children when Filtering

Art Schneiderheinze

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?

Paul Newcome

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.

PeggyLang

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

Paul Newcome

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

Andrée Starå

Hi @PeggyLang @Paul Newcome

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

PeggyLang

@Paul Newcome @Andrée Starå

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?

Paul Newcome

Which level is the actual assignment being made on?

PeggyLang

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.

Paul Newcome

@PeggyLang In that case, give this a try:

=IF(COUNT(CHILDREN([Task Name]@row)) = 0, INDEX(ANCESTORS([Project Manager]@row), 1), [Project Manager]@row)

PeggyLang

@Paul Newcome THANK YOU!!!

This worked!

Paul Newcome

Happy to help. 👍️

PeggyLang

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

Paul Newcome

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