How can I filter Airtable records to show unlinked entries and those not referenced elsewhere?

Other Questions Airtable
1

Hey folks, I’m scratching my head over an Airtable problem. I’ve got this table with people’s names and their partners. Some have partners, some don’t. What I’m trying to do is make a new view that shows:

  1. People who don’t have any partners listed
  2. People who aren’t listed as anyone else’s partner

Here’s a quick example of what my table looks like:

Name         | Partner
-------------|----------------
Alex         | Sam
Sam          | (empty)
Chris        | Pat, Jamie
Pat          | (empty)
Jamie        | (empty)
Taylor       | (empty)

I want my new view to show Alex, Chris, and Taylor. Alex has a partner but isn’t anyone’s partner. Chris has partners but isn’t a partner. Taylor has no partner and isn’t a partner.

Is there a way to set up filters to do this? I’ve tried a few things but can’t quite get it right. Any ideas would be super helpful!

2

I’ve dealt with a similar issue in Airtable before, and I found a workaround that might help you out. Here’s what I did:

First, create a rollup field in your main table. Let’s call it ‘Partner Count’. Set it to count the number of times each person’s name appears in the Partner field across all records.

Then, create a formula field. Name it ‘Unlinked Status’. Use this formula:
IF(AND({Partner}=‘’, {Partner Count}=0), ‘Unlinked’,
IF(OR({Partner}=‘’, {Partner Count}=0), ‘Partially Linked’, ‘Linked’))

Now you can filter your view to show only records where ‘Unlinked Status’ is either ‘Unlinked’ or ‘Partially Linked’.

This method catches both people without partners and those not listed as partners. It’s not perfect, but it should get you close to what you need. You might need to tweak it a bit depending on your exact setup.

3

hey, i think i got a simpler way for ya. try this:

make a new field called ‘partner check’. use this formula:
IF(OR(Partner=‘’, NOT(FIND(Name, ARRAYJOIN(Partner)))), ‘Show’, ‘Hide’)

then just filter ur view to show only records where ‘partner check’ is ‘Show’. should work!

4

I’ve encountered this issue before, and there’s a straightforward solution using Airtable’s lookup and rollup fields. Here’s what you can do:

Create a lookup field in your main table, linked to itself. Name it ‘Referenced As Partner’.

Set up a rollup field to count the occurrences in ‘Referenced As Partner’.

Finally, create a formula field called ‘Unlinked Status’:
IF(AND(ISBLANK({Partner}), {Rollup Field} = 0), ‘Unlinked’, ‘Linked’)

Filter your view to show only records where ‘Unlinked Status’ is ‘Unlinked’.

This approach should capture both unpartnered individuals and those not referenced elsewhere, meeting your requirements efficiently.