Filtered Assignees Formula

I’m trying to create a project-level formula that shows all assignees for the project’s tasks but only for the tasks that are not yet completed. I feel like this should be possible, but am struggling to figure out how to do it. The use case is a cross project list view task board where I only want to show the project if you have an open task inside of it. Thanks in advance if anyone knows a solution.

Just for clarity, do you hope to end with a list view, a board view or something else?

List view.

Most of our task tables are project specific. But I want a ‘my task’ overview list so everyone can see only their tasks. But I don’t want the top level (project level) to show if the person viewing doesn’t have any open tasks inside of that top level.

The built in filters for assignees will filter that second level of tasks but not the top level (project level). So if employee-A once had an open item in project-A, but now does not, that project will still pop up and note urgency based on other people’s now active tasks inside that project.

I don’t know if I am quite getting it, but have you thought of doing this:

  • add a formula field to the Project database that filters only active Tasks, something like this:
    Tasks.Filter(State.Name != "Done")
  • add a lookup on this field that gets the Assignees

Then you can create a hierarchical list and apply the necessary filters:


Alternatively, you might want to use smart folders in a similar way…

Perfect, thank you Chris! In hindsight this is so obvious, really appreciate the help.