Help

Re: lookup field grabs records not already linked in another linked field

Solved
Jump to Solution
894 4
cancel
Showing results for 
Search instead for 
Did you mean: 
jadeadele
6 - Interface Innovator
6 - Interface Innovator

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. 

 

1 Solution

Accepted Solutions

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

Screenshot 2023-03-10 at 10.54.24 PM.png

Link to base

See Solution in Thread

8 Replies 8

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:

Screen Recording 2023-03-10 at 9.03.52 PM.gif

Link to base

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. 

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

Screenshot 2023-03-10 at 10.54.24 PM.png

Link to base

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. 

 

Thanks Jupiter. I think this will do it!!

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?

> 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?

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.