Is it possible to create an Airtable view that includes records absent from a linked records field?

Other Questions Airtable
1

I have a table structured as follows:

Item Name (Text) | Associate (Link to 'Item Name')
------------------|--------------------------
Alice Johnson      | `Bob Brown`
Bob Brown         | (No Value)
Charlie White      | `Diane Green` `Eve Blue`
Diane Green      | (No Value)
Eve Blue         | (No Value)
Frank Black      | (No Value)

I want to establish a new view for this table with a filter that looks like:

WHERE {Associate (field)} is not null
OR {Associate (field)} does not include {Item Name}

This should yield the following results:

Item Name (Text) | Associate (Link to 'Item Name')
------------------|--------------------------
Alice Johnson      | `Bob Brown`
Charlie White      | `Diane Green` `Eve Blue`
Frank Black      | (No Value)

The difficulty I’m facing is that in the first filter condition, I want to find records with no links in the ‘Associate’ field, but in the second condition, I’m targeting records that are not listed in the ‘Associate’ field of other entries.

Ultimately, I aim to compile a list of records with no associates along with those who aren’t listed as an associate for anyone else. Can this be done?

2

you could try using a formula field to create a flag for items not linked in other records. Then, filter your view based on this flag. It might require some creativity with formulas but shouldn’t be impossible! assessing link mentions in multiple fields can be tricky. good luck!

3

From my experience, a rather creative workaround is using a two-step approach by creating two distinct formula fields. The first formula could count the number of links each item has in the ‘Associate’ field across the table. The second formula could display whether the ‘Item Name’ field appears in any ‘Associate’ list. While it might seem unintuitive at first, combining these fields will allow you to set up a filtered view where you can isolate records with non-linked ‘Item Names’. You won’t have perfect built-in support but manipulating formula fields will enable you to get close to what you seek.