How to create a filtered, referenced dropdown list to choose from?

Rogerio_Penna · July 28, 2023, 12:17pm

there is a simpler way

Filtering possible values in reference lists

opened 11:51PM - 17 Feb 22 UTC

closed 04:33AM - 27 Apr 22 UTC

Sometimes, one would like to create a reference but only enable the selection of… a subset of that reference. This becomes particular useful if the list is otherwise very large. It would be nice, if one could assign a filter to that list first, so hat the entire list gets shorten. Example: In the table project, I would like to assign one or more managers to a project. I create a "Managers" column and make a reference list which points to my employee table. However, in that table, all employees and their different roles are listed. Obviously I do not want to assign Bob, the 17-year-old intern, to become the manager of a project. So what I need is to say Create a reference list from the table employee show name, but only if in table employee the column role is "manager".

" You can accomplish that today by adding to the Employees table another column, say, Name_If_Manager with a formula like $Full_Name if $Role == "manager" else "". This will duplicate the Full_Name column but only for managers.

Then in the Projects table, set your Managers column to point to table Employees and for “SHOW COLUMN” select Name_If_Manager. This will limit the dropdown to non-empty values, which happen to be only the managers."

so here is your first table.

check the formula… when there is a date and check is 0, then it is the email, else is blank.

SO only where your conditions are ok, it will repeat the email.