I’m working with an Airtable setup where I have a table that looks like this:
Employee Name (Text) | Manager (Link to "Employee Name")
------------------- | ------------------------------------
Alice Johnson | `Bob Wilson`
Bob Wilson | (Empty)
Carol Davis | `Dave Brown` `Emma White`
Dave Brown | (Empty)
Emma White | (Empty)
Frank Miller | (Empty)
I want to build a view that filters records using this logic:
WHERE {Manager (field)} is empty
OR {Manager (column)} does not reference {Employee Name}
The expected output should look like:
Employee Name (Text) | Manager (Link to "Employee Name")
------------------- | ------------------------------------
Alice Johnson | `Bob Wilson`
Carol Davis | `Dave Brown` `Emma White`
Frank Miller | (Empty)
I’m trying to combine two different conditions. First, I need records where the Manager field is blank. Second, I need records where the person’s name doesn’t appear as a linked record in anyone else’s Manager field.
Basically, I want to show people who either have no manager OR are not serving as someone else’s manager. Can this be done with Airtable’s filtering options?
Airtable’s native filtering can’t accommodate your requirement to check if a record is referenced in other linked fields. This is a limitation of the platform’s built-in filtering options. I’ve faced a similar situation where a direct filter for the second condition isn’t possible. You might consider creating a formula field labeled ‘Is Manager’ using IF(COUNTA({Manager}), 'Yes', 'No'). However, this won’t solve the issue of determining if a person is referenced in others’ Manager fields. The most reliable solution involves setting up an automation or script that periodically updates a separate checkbox field indicating whether the individual is serving as a manager. You can then filter records based on that checkbox being unchecked and simultaneously having the Manager field empty. Though it’s not a perfect method, it’s a functional workaround for this type of query.
Unfortunately, standard Airtable filtering can’t handle this directly. The tricky part is checking if an employee’s name shows up in other records’ Manager fields. I struggled with a similar org chart setup and found a helper field works best. Add a rollup field called ‘Direct Reports Count’ that counts how many times each employee appears as a manager elsewhere. Then create a formula field: IF(OR({Manager}="", {Direct Reports Count}=0), "Include", "Exclude"). Filter your view to show only ‘Include’ records. This catches employees who either have no manager or aren’t managing anyone - exactly what you want.