Search code examples
filteringairtable

Can I create an Airtable view containing records not present in a "linked records" column?

I have a table with columns like:

Name (Single Line Text) | Partner (Link to "Name")
----------------------- | --------------------------
John Doe                | `Jane Doe`
Jane Doe                | (Empty)
Bob Smith               | `Mary Smith` `Kevin Smith`
Mary Smith              | (Empty)
Kevin Smith             | (Empty)
Alan Stephens           | (Empty)

I'd like to create a new view for this table with filtering like:

WHERE {Partner (field)} is not empty
OR {Partner (column)} does not contain {Name}

such that the result would be:

Name (Single Line Text) | Partner (Link to "Name")
----------------------- | --------------------------
John Doe                | `Jane Doe`
Bob Smith               | `Mary Smith` `Kevin Smith`
Alan Stephens           | (Empty)

The challenge here is that on line 1 of my filter, I'm looking for "records with no records linked in their Partner column", but on line 2 I'm looking for "records which are not found in the Partner column for any other record".

The overall goal is to generate a list of records with no Partner(s) plus records who are not anyone else's Partner. Is there a way to achieve this?

Solution

  • I discovered that a filter such as this one is outside the available feature set of Airtable. I was able to implement a workaround:

    1. Add a Script block containing the script described in this video (script gist)
    2. Use the Script block to populate a new column on the table (Partner Of)
    Name (Single Line Text) | Partner (Link to "Name")   | Partner Of (Link to "Name")
----------------------- | -------------------------- | ---------------------------
John Doe                | `Jane Doe`                 | (Empty)
Jane Doe                | (Empty)                    | `John Doe`
Bob Smith               | `Mary Smith` `Kevin Smith` | (Empty)
Mary Smith              | (Empty)                    | `Bob Smith`
Kevin Smith             | (Empty)                    | `Bob Smith`
Alan Stephens           | (Empty)                    | (Empty)
    1. Create a new view with a filter like:
    WHERE {Partner Of} is empty