lookup field grabs records not already linked in another linked field

[jadeadele]

Hello!

I have a base with 3 tables:

• a Contacts table,
• an Organization table, and
• a Membership table. 

In the Organization table, I have a linked field to the Contacts table called "All organization contacts" where I link all Contacts records associated with that "Organization" record.

In the Membership table, I have a linked field to the Organization table called "Organization" where I link all Organization records associated with that membership. 

In the Membership table, I have a linked field to the Contacts table called "Membership seats" where I link all Contacts records that have a seat on that Membership. 

In the Membership table, I'd like to add a lookup field called "Other organization contacts" that pulls in any Contact record linked to the Organization linked to that Membership record that is NOT already linked in the "Membership seat" field. 

I don't mind doing this with either an automation or some sort of combination of fields, but I want it to be an actual link to the contact table and not just a string. 

[TheTimeSavingCo]

Hm, you could try using an automation that will trigger when a checkbox field is marked or something, and its action would be to find all the records in "Contacts" where the "Organization" is none of the organizations linked to that `Membership` record:

Link to base

[jadeadele]

Thanks Jupiter for your response. 
Unfortunately I don't think I explained myself well enough. All contacts would be from the same organization. 

Let's say I have an Organization A, we have three contacts at that organization (1, 2, and 3) but they pay for a membership with only 2 seats, which they've attributed to contacts 1 and 2.

I'd like to link all three of those contacts to Organization A, and I'd like to link the seats for contact 1 and 2 to the membership, and then I'd like to automatically pull in contact 3 in the membership tab, so I know who else at the organization can be contacted. 

[TheTimeSavingCo · Accepted solution]

Ahh, I see, apologies.  Yeap this is doable, just need to swap the automation conditions a bit:

Link to base

[jadeadele]

I thought I had found a solution where I had a rollup field in the organization tab that rolled up the organizational contacts whose member seat fields were empty, and then had an automation that updated a linked field in the membership tab that linked to the contact tab with the contact name that was rolled up in the rollup field, but if any of my contacts don't have unique names, this is a major problem. 

https://airtable.com/shrv7axMTrcDNOVuV

[jadeadele]

Thanks Jupiter. I think this will do it!!

[jadeadele]

Sorry to get back to this Jupiter, but I'm trying to set my table up with this right now and running a snag with the automation as you have it. The automation returns an error if I put in "member of does not contain membership name" I assume that is what it's supposed to read. Could you clarify how you've set up your automation?

[TheTimeSavingCo]

> The automation returns an error if I put in "member of does not contain membership name" I assume that is what it's supposed to read.
Yeap that's right.  Weird that you're hitting the error.  Could you create an invite link to your base and private message it to me so I can take a look?

[jadeadele]

Thanks for the reply! 

I figured it out. I had to add a conditional to filter out for instances where a field was blank!! 

Thanks so much for your help.