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!
