Filtering Parent Rows

Alexandra Enriquez

Hello,

I'd like to apply a few filters to my sheet, and bring the children along for the ride. The sheet is generally a project and task tracker.

The conditions I'd like to filter on exist only in the parent rows, but I'd like for the parents' children to not be excluded when the filter is applied.

In the example attached, I'd like to apply one filter that would sort by Priority, and another that filters by Due Date.

Any suggestions on how I can make this happen? I tried included a second condition to my filter that said the "Item Name" row should contain "----," but to my disappointment it brought up all rows that met that condition, even if the parent row didn't meet the initial "Priority" or "Due Date" condition.

David Tutwiler

A few ideas. First, when you put the Item Name condition, did the text on the Filter say "Show rows that match all conditions" or "Show rows that match at least one condition"? That could have been the issue there.

If that doesn't work, you could add =PARENT() to each of the priority and Due Date child fields. That would allow them to match your search criteria, but all be kept up-to-date when someone changed the field in the parent row.

Alexandra Enriquez

David, thanks for your very prompt response and apologies it's taken me a bit to get around to testing this out!

With adding the Item Type condition to the filter, when I "match all conditions" I get no values returned (nothing meets both the Priority and/or Due Date criteria AND the "----" criteria, since the former is only in the parent rows and the latter is only in the child rows). Conversely, when I "match at least one condition" I do get the parents and children together, but I also get a bunch of orphan children whose parents did not meet criteria #1. Hope that makes sense.

Adding =PARENT() definitely did work, however it's not ideal for the way I use the tracker, since sometimes the child tasks under the parent projects have their own unique due dates.

Any chance Smartsheet plans to enhance filters to not separate parents from their children anytime soon? :D

Genevieve P.

Hi @Alexandra Enriquez

What if you had a helper column that we applied a Column Formula to which indicates what the Parent row is doing. You could then include this in your filter, but it would allow the child rows to have different dates/priorities than the parent row.

You would essentially be recreating the filter criteria, but in a formula instead.

Ex:

=IF(AND(PARENT(Priority@row) = "Red", PARENT([Date Due]@row) < TODAY()), "Late and Red")

This would check the Priority for the parent row as well as if the date due is in the past. You could build out different statements depending on the text you want returned, or how many filters/combinations you want to look for.

Then in your Report you would just add in this Helper Column and the text you're looking for. Would that work for you?

Let me know if you need help with the following statements!

Cheers,

Genevieve