Linking tables on non-primary columns

[Currently_Jason]

Hi

I have two tables that I want to link together via a non-primary field. So, I have a contracts that is linked to a User table by name (primary field) and I want to link it to another table “Contract Rows” by a unique field that is contained in both.

How can I do this please?

[JonathanBowen]

Hi @Currently.Jason - I’m not quite sure what you are asking here, but links in Airtable are always based on the primary field of the table (which might be constructed from other fields using a formula).

I would think about this base in the following way:

A User has many contracts and a Contract belongs to a User (or could a Contract belong to many Users?)
A Contract Row belongs to a Contract and a Contract has many Contract Rows
A User has many Contract Rows through Contracts

In Airtable, this would lead me a to a base with 3 tables - Users, Contracts and Contract Rows. You could show the User on the Contract Rows table as you can link to the Contract, then lookup the User (or show the Contract Rows, across many Contracts, linked to a User on the Users table).

JB

[Sharon_Watson]

Thanks for the reply Jonathan,

The problem I have is thus:

User Table
Contracts
Contract Rows
All these tables are linked via the Username

UserA has 2 contracts (ID1 and ID2) in the “Contracts” table. There are multiple lines for these contracts in “Contract Rows”

We then want to cancel contract ID1 on the “Contracts” table so we change the status to Canceled.

The lookup field on the “Contract Rows” will set all rows for UserA as Canceled as it is not looking at the specific contract. It is linked on the Name so will change all rows regardless of contract.

Hope that makes sense.

[Sharon_Watson]

Any takers before I give up on this being possible and try other options please?

[M_k]

Hi

I would just wait a bit, since the holidays are coming up, I think that a lot of people are a little bit busy.

Mary Kay

[JonathanBowen]

Hi @Sharon_Watson - if I understand you correctly, you’ve got a set-up something like this:

Note, my fake column where I’m assuming a contract row is related to a contract, but you just don’t have that link in place, yes?

So when you cancel a contract, you mark a User as cancelled, which then ripples out across all their rows.

I can’t see any way around your issue tbh. I think the answer is to create a link between a contract and a contract row, then delete the link between the contract row and the user (it will still be linked once removed via the contract). I don’t know if this is possible for you - this might be many records and very manual, but I think this would give you a better base design if you can make the change.

JB

[Currently_Jason]

|### Jason Martin jason.martin@zensiblesolutions.com|Thu, Dec 19, 11:47 AM (23 hours ago)||

to Airtable , bcc: 6380111

|

Yep, you pretty much got it Jonathan but I need to keep the link to the user for other stuff to work.

In SQL it would just be a case of creating a join based on the non-primary fields and then updating records based on that join. I guess we are not there yet with Airtable but I really appreciate your time and effort. I’ll have to look at a convoluted process using Parabola or Integromat if possible.

Cheers

[Justin_Barrett]

Maybe I’m missing something, but I think that what you want is doable if you link to both the user and the contract on the [Contract Rows] table. When canceling a contract on the [Contracts] table, you then look up that canceled status in [Contract Rows] via the contract link, not the user link. The cancellation should only reflect on rows for that contract, even if the same user has other non-canceled contracts.

[Andrew_Palmer]

Not sure if this would help in this scenario, but for a similar situation to the Contracts example. I create a separate field called “MostRecent?” as a Checkbox. If that record/contract/name is the most recent, I check the box, otherwise I leave it blank. I then create a formula field called “xyz-ForLookup” as an IF. IF({MostRecent?}=1, {Status},""). Then I set my Lookup in the other table to point to this field.

Not sure if this would help, but it is a workaround for some things.