Filtering Parent Rows and Including All Child Rows

I have a contact list where an organization name exists as the parent row and all contact names exist below as child rows. I want to be able to filter parent rows by any one of the other values on that row (i.e. program, contact owner(s), or partner type) and also include all child rows beneath even if they don't match that criteria.

Currently my workaround is to just copy the data to the child rows, but this looks messy and redundant.

Is there a way to include these child rows via formula if there is no native setting for this?

Find more posts tagged with

Accepted answers

All comments

Kelly Moore

Hey @MarkF

One approach is to add a helper column that places the Parent Org name on every Child row. Depending on how your sheet is set up, you may need to use the Hierarchy level instead. For instance if you have parents, grandparents ,child rows and just plain rows, you may need to use the hierarchy level. A screenshot would be helpful (or mock up to mask sensitive data). Assuming a row is only a parent or a child, try this in the helper column. This helper will give you a column that can be pulled into filters or reports.

=IF(COUNT(CHILDREN()) > 0, [your primary column]@row, PARENT([your primary column]@row))

Be sure to replace the name of the primary column placeholder in the formula with your real column name.

Does this work for you?

Kelly

cmore04

Hi Kelly!

I tried your formula but it doesn't work. It is saying Unparseable even when I change primary column name with the parent name.

Does the primary column name have to be the actual sheet's primary column?

Also, do I put this formula in the child rows or the parent row?

Thank you!!

Genevieve P.

Hi @cmore04

Can you post a screen capture of your sheet, but block out sensitive data?

You can replace the [your primary column]@row reference with any column reference... the column you want to bring back the value for.

=IF(COUNT(CHILDREN()) > 0, [Column Name]@row, PARENT([Column Name]@row))

This would go in every single row, as a Column Formula.

Cheers,

Genevieve

cmore04

Hi @Genevieve P. !

Thank you for such a quick reply, it worked! But now I realized I actually need to show the ancestor name in the Phase column as well so I can filter out everything for one phase.

For example in the formula now it only shows the direct parent name, but how do I include all parents in the phase column? I know using Join might help, but not sure?

I'm hoping to put the grandparent name and parent name and child name in the phase column everytime:

Grandparent - Parent1 - Child1

Thank you so much for any help you can provide!

Genevieve P.

Hi @cmore04

JOIN is exactly what I'd suggest!

Try:

=IF(COUNT(ANCESTORS([Phase/Task]@row)) = 0, [Phase/Task]@row, JOIN(ANCESTORS([Phase/Task]@row), " - "))

Cheers,

Genevieve

cmore04

It worked, Thank you so much!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.