I’m working with an Airtable setup where I have a table containing employee information with a self-referencing relationship. Here’s what my data looks like:
Employee Name | Team Lead (Link to Employee Name)
------------- | ----------------------------------
Alice Johnson | `Carol Wilson`
Carol Wilson | (Empty)
Tom Brown | `Sarah Davis` `Mike Jones`
Sarah Davis | (Empty)
Mike Jones | (Empty)
Rick Thompson | (Empty)
I want to build a filtered view that shows records meeting these criteria:
WHERE {Team Lead (field)} is empty
OR {Team Lead (column)} does not reference {Employee Name}
The expected output should be:
Employee Name | Team Lead (Link to Employee Name)
------------- | ----------------------------------
Alice Johnson | `Carol Wilson`
Tom Brown | `Sarah Davis` `Mike Jones`
Rick Thompson | (Empty)
Basically I need to display two types of records. First, employees who don’t have a team lead assigned. Second, employees who aren’t serving as anyone’s team lead. The tricky part is combining these conditions since one checks for empty linked records while the other checks if the current record appears as a reference elsewhere. Can this be done with Airtable’s filtering options?
Standard filtering won’t work here since you’re dealing with a reverse relationship query. You need a formula field that identifies records not serving as team leads. Add a formula field called “Reports Count” to your Employee table: COUNTA({Team Lead (Link to Employee Name)} ← Employee Name). This counts how many people list the current employee as their team lead. Then create your main filter formula: OR({Team Lead (Link to Employee Name)} = BLANK(), {Reports Count} = 0). This returns TRUE for employees with no assigned team lead OR employees who aren’t anyone’s team lead (Reports Count = 0). I hit the same issue building project assignment tracking. Airtable doesn’t handle “not referenced elsewhere” queries natively, so you have to count the references first. Once you’ve got that count field, filtering’s easy. Create a view filtered where your formula field equals TRUE and you’ll get exactly what you’re looking for.
Use Airtable’s automatic reverse lookup - it’s perfect for this. When you create a linked field like ‘Team Lead’, Airtable automatically creates a reverse field showing all records that link back to the current one. That’s how you’ll identify team leads.
Create a formula field: IF( OR( {Team Lead (Link to Employee Name)} = BLANK(), COUNTA({Employees (from Team Lead)}) = 0 ), TRUE(), FALSE())
The ‘Employees (from Team Lead)’ field shows up automatically - it’s the reverse lookup displaying everyone who reports to each person. If it’s empty (count = 0), that employee isn’t leading anyone.
I’ve used this approach in client databases before. It’s way cleaner than building complex rollup fields or separate counting systems. Filter your view where the formula equals TRUE and you’ll see employees without a team lead plus employees who aren’t leading others.
I’ve run into this exact problem building org charts in Airtable. You can’t do that kind of filtering with standard views because you’re mixing two different logical operations.
You’ll need a helper field. Add a formula field called “Is Team Lead”:
IF(COUNTA({Team Lead (Link to Employee Name)}) = 0, "No Lead Assigned", "Has Lead")
Then create another formula field called “Referenced Count” that uses a rollup or lookup to count how many times the current employee appears as a team lead in other records.
Actually, skip that - here’s a better approach. Create one formula field that handles both conditions:
IF(
OR(
COUNTA({Team Lead (Link to Employee Name)}) = 0,
COUNTA({Employees (from Team Lead)}) = 0
),
"Show",
"Hide"
)
Filter your view to only show records where this formula equals “Show”.
Here’s the trick: Airtable automatically creates a reverse lookup field when you have linked records. So if “Team Lead” links to Employee Name, you’ll see an “Employees (from Team Lead)” field showing who reports to each person.
I’ve used this setup in several employee management bases and it works great.