Limit Link to another record by a second field

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

Grid view - Airtable

Explore the "Grid" view on Airtable.

Project Number

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

AlexWolfe wrote:

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

Grid view - Airtable

Explore the "Grid" view on Airtable.

Project Number

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

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