How do I make a field with a filtered subset of the items in a Link field?

256 1
Showing results for 
Search instead for 
Did you mean: 
4 - Data Explorer
4 - Data Explorer

Let’s suppose I have an Employee table and a Team table.

In the Employee table, each row is an employee, and there is a field called “Role” that indicates their role (Engineer, Designer, Manager, etc.)

In the Team table, each row is a team, and there is a field called “Members” of type “Link to Employee” that accepts multiple items. The Members field is where we put all the employees who are members of the team.

I want to add a field to the Team table that contains the engineers on the team — an automatically filtered subset of the Members. And I’m stuck.

I could use a Lookup field to filter the items in the Members field, specifying in the condition that the Role has to be “Engineer”. But Lookup does too much: I have to specify a field of the Employee. So I could get the names of all the engineers, for example. But I want the actual Employee records, just like they appear in the Members field. There doesn’t seem to be a way to specify that I want the records themselves, rather than a field within them.

As a workaround, I could add a Linked field called “Self” to Employee, and fill it in for each Employee with a link to that same Employee itself. Then I could use the Lookup field to get the Self field for the Employees that match the condition. But, of course, it’s silly to have to populate the Self field by hand. Unfortunately, there doesn’t seem to be a way to do this with a formula, either — I can refer to fields of the Employee, but again, there’s no formula expression that returns the Employee record itself.

This seems like an odd oversight. Airtable will happily do something more complex than I want, but I can’t get it to do something simple. Does anyone have any suggestions or thoughts on this?

Thanks so much for your help!

1 Reply 1

Honestly that’s probably your best bet without completely rethinking your linking approach (i.e. have multiple Link fields, one per role). You could use an Automation to run when an “Employee” record’s {Self} field is blank that will copy the value of the primary record into the {Self} column, that way you don’t have to think about it.

Ideally Lookup fields that point to a primary field would display clickable records like the Link field does, but I couldn’t tell you if that’s on the roadmap.