Contextual and Hierarchical Multiple Select


The doc I’m working on has 5 tables:

  1. Departments
  2. Teams
  3. Projects
  4. Project Types
  5. Tasks

I want to be able to filter Tasks hierarchically by Department, Team then filter Projects contextually by Project Type. Using Control LookUp and Control Multiple Select I can filter by Department, Team, Project Type and Project.

However for this to work as I intended I need the Control Multi Select for Projects to be filtered down contextually by Project Type rather than showing all the projects for Department/Team.

So if I have the following as an example:

Project Project Type
New Server Medium Project
Update OS Big Project
New Landing Page Small Project

I would like to select the Project Type ‘Big Project’ to show only Projects of this type. In the above example the Control select list for Projects would show the project ‘Update OS’.

I could really do with some tips on how to solve this problem.

Many thanks

Can you try this instead? Should fix things up for you if I understand your problem correctly. If that is doing what you need it to I can give some more explanation as to why and how I ended up with that solution

Hey @Scott_Collier-Weir,
Thanks for taking the time to help, I very much appreciate it.
Your solution works for how I explained it above, however I don’t think I explained the problem well enough.

For the Controls used in the filter it works as following:
Department: <dropdown of all Departments (Departments table)>
Team: <dropdown of all Teams (Teams table) related to the selected Department(s)>
Project Types: <dropdown of all Project Types (Project Types table) related to the selected Team(s)>
Project: <dropdown of all the Projects (Projects table) related to the selected Team(s) that match the selected Project Type>.

Essentially I’m trying filter in only Projects of a particular Project Type that is related to a selected Team.


@Scott_Collier-Weir – thanks for your input… problem solved now.

After your idea Kristen @ Coda support suggested:

Projects.Filter([Project Type].In([Select Project Types]) AND [Team].In([Select Team]))

Adding [Teams] into the filter.

I didn’t know I could add a filter such as this to the Control Select formula, now I do.


This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.