Limit Link to another record by a second field

[Kieran_Choy]

Hi, I’m new to Airtable and haven’t been able to work out how to achieve the following:

I have a table set up called “Staff”, with a Multiple Select field for their department (Sales, Accounts, Design).

On another table “Project Number”, I would like to be able to assign a Sales staff to a project number. But I want to limit the list to only people who are part of the Sales team - Link to another record only gives me the full Staff list.

What would be the best way to achieve this?

I can achieve this by creating a third (junction?) table called “Sales staff”, but then there are two further issues:

1. How to group sort the Staff list by department - the junction table means each Staff has a unique department ID
2. How to make this system flexible if I add new departments (is there a way to automatically generate a new table if a department is added?)

[AlexWolfe]

Here are my thoughts after testing a couple options:

1. You can have a ‘Project Number’ table with links to three different tables for each department, ‘Sales,’ ‘Accounts,’ and ‘Design.’ This would “filter” or “limit” the linkable records in your ‘Project Number’ table by department (each would have a separate column) - but, it would break the functionality of having a single table ‘Staff’ where you could view all staff together and group them by department.

2. (This is likely the best option I can think of at the moment). You could change the setup of your ‘Staff’ table to, in a way, brute-force the filtering when linking Staff members on your ‘Project Number’ table.
   Example:
   Make your Staff primary field a formula which combines the ‘Department’ column first, with a dash or some other text, then the Staff member’s name:
   {Department}&" - "&{Name}
   That way, when you are in your ‘Project Number’ table and go to link a staff member, you can just type ‘Sales’ or ‘Accounts’ and it will pull up only those records that begin with the desired department, then you can choose one by name. Are staff members able to be in multiple departments? If not, I suggest changing your Multiple Select field to a Single Select, and grouping your staff list by the department field. You could still group them using the Multiple Select, but if any are in more than one department, it will result in some odd groups. Here are some quick examples:

Staff List

Airtable

Grid view - Airtable

Explore the "Grid" view on Airtable.

Project Number

Airtable

Grid view - Airtable

Explore the "Grid" view on Airtable.

Here if you link a record and type ‘Sales’ all ‘Sales’ members would appear:

3. A third option is to go with option [1] I listed above, and then link all staff members to a unique Staff List table ID that pulls the info for everyone. But I do not think this way is quite right / ends up doubling work as you would create a record in each department table, and in the Staff List table - so this would not be ideal.

Finally, there is no functionality to create a new table when a new department is added. You would need to do this manually if you went that route.

With option 2, your system would adapt to new departments. However, given that Airtable does not currently allow filtering of linked records, I think the options are limited.

Good luck!
Alex

[Tom_Kerswill]

I’d really like this, too. The existing ability to limit record selection to a view is good… But a great extension would be to dynamically set the view.

For example, if I select a particular department to “Sales” then my “Staff Member” field would only show records where “Department=Sales”.

Tom