Can Filters "Prune the tree" in views with hierarchical self-relations?

I have a database called “Task” that has a self-relation field that I use to create hierarchy (parent Tasks and sub Tasks). I have a table view that is Grouped by Parent Task (recursive) to visually show this hierarchy.

It’s pretty cool to have a tree view.

Is there a way to combine formula fields to create hierarchy-aware filters on my table view? For example, if I want to “prune” the tree to include any Parent Task (which by definition has at least 1 sub-task) that is In-Progress, and include ALL descendant subtasks of these)".
I think I can accomplish the first part of that filter, but I don’t know if it’s possible to include all sub-Tasks, because filters don’t natively see hierarchy?

For example, if I have this:

  • Parent Task 1 (Active)
    – Sub Task 1 (Active)
    – Sub Task 2 (Done)
    – – Sub Task 2.1 (Done)
    – Sub Task 3 (Waiting)

  • Parent Task 2 (Waiting)
    – Sub Task 4 (Active)
    – Sub Task 5 (Done)
    – Sub Task 6 (Waiting)

My filter should ideally return this:

  • Parent Task 1 (Active)
    – Sub Task 1 (Active)
    – Sub Task 2 (Done)
    – – Sub Task 2.1 (Done)
    – Sub Task 3 (Waiting)

But currently I don’t know how to specify that all descendant sub tasks should be included, so I can only filter down to Active Parent Tasks, and I get this instead:

  • Parent Task 1 (Active)

Thanks for any suggestions.

1 Like

I think you could create a recursive formula to determine if any parent/grandparent/greatgrandparent/… is ‘Active’.
For example:

Active Parents = Parent.State.Name = "Active" OR Parent.[Active Parents]

Then you can use this in a filter for the view.

Note: you’ll need to create the formula with the correct data type, before it can refer to itself. So start with a simple formula named Active Parents (e.g. Parent.State.Name), save the formula, and then update it to the above formula.