Skip to main content

Clickable / linked lookup field for many to many relationship based on ID

  • June 5, 2020
  • 3 replies
  • 47 views
E

Hi there,

I just got started with Airtable and have imported a couple of records that make up a many-to-many relationship. The base structure is fairly simple:

User (user_id = 5) <–> join table (join_id = 124 with user_id = 5 & group_id = 33) <–> Groups (group_id = 33)

So users & groups each carry a primary ID / key and are connected via the foreign keys / IDs in the join table.

I linked the records together fine and can add lookup fields like “user name” in the group table but those are merely text fields and not clickable – instead the ID field that ties the records together is the clickable one.

How can I choose which field is used for switching between tables / triggering the popups and pulling in the data from other records?

The client I am building this for won’t accept an ID field to have to click upon and I want a clean and logical interface…

Or do I have to get rid of the imported IDs since Airtable uses its own unique IDs and tie the records together based on the desired clickable field (f.ex. user name)? If so then how do I do that and how does Airtable prevent errors with duplicate names or similar pitfalls?

Thanks for your input!

P.S.: I did study the docs already but maybe I missed a crucial part? :slightly_smiling_face:

3 replies

ScottWorld
Forum|alt.badge.img+35
  • ScottWorld
  • Genius
  • June 5, 2020

Welcome to the community, @E-VANCE!

Airtable is a more “simplistic” database tool than what you’re used to. Think of Airtable as more of a spreadsheet with some cool database features layered on top.

Airtable uses an internal Record ID, which you can see with the formula function RECORD_ID()

But you cannot choose which fields you want to create linked relationships between. Every linked relationship to another table is always a relationship to an entire record itself in that other table — not a specific field value in the record.

Also, there is no automatic linking in Airtable (with 2 exceptions). The vast majority of the time in Airtable, all linked records must be explicitly & manually chosen by the user.

Exception #1: When you convert a text field to a “linked record” field, that is one of the few times when Airtable will “automatically” link records between tables for you — as long as the contents of the text field match the PRIMARY FIELD VALUE in the other table. But that is just a “one time only” automatic linking during the conversion process.

Exception #2: The only other automatic linking that you will experience is if you paste (or type) data into a “linked record field” and the value you just pasted exactly matches A PRIMARY FIELD VALUE in the other table. Then, an automatic link will be created to that other record. (And one cool trick is that Airtable will automatically create records in that other table if your pasted value doesn’t match any existing records.)

Hope this helps!! Airtable is a lot of fun!! :slightly_smiling_face:

If this answers your question, could you please mark this comment as the solution to your question? This will help other people who have a similar question. :slightly_smiling_face:

Justin_Barrett
Forum|alt.badge.img+21

Welcome to the community, @E-VANCE!

Airtable is a more “simplistic” database tool than what you’re used to. Think of Airtable as more of a spreadsheet with some cool database features layered on top.

Airtable uses an internal Record ID, which you can see with the formula function RECORD_ID()

But you cannot choose which fields you want to create linked relationships between. Every linked relationship to another table is always a relationship to an entire record itself in that other table — not a specific field value in the record.

Also, there is no automatic linking in Airtable (with 2 exceptions). The vast majority of the time in Airtable, all linked records must be explicitly & manually chosen by the user.

Exception #1: When you convert a text field to a “linked record” field, that is one of the few times when Airtable will “automatically” link records between tables for you — as long as the contents of the text field match the PRIMARY FIELD VALUE in the other table. But that is just a “one time only” automatic linking during the conversion process.

Exception #2: The only other automatic linking that you will experience is if you paste (or type) data into a “linked record field” and the value you just pasted exactly matches A PRIMARY FIELD VALUE in the other table. Then, an automatic link will be created to that other record. (And one cool trick is that Airtable will automatically create records in that other table if your pasted value doesn’t match any existing records.)

Hope this helps!! Airtable is a lot of fun!! :slightly_smiling_face:

If this answers your question, could you please mark this comment as the solution to your question? This will help other people who have a similar question. :slightly_smiling_face:


There’s a third exception: if you group by a linked field, adding a new record to the group will automatically populate the link field with the same link, because having that link is required for the record to be in that group.

ScottWorld
Forum|alt.badge.img+35
  • ScottWorld
  • Genius
  • June 6, 2020

There’s a third exception: if you group by a linked field, adding a new record to the group will automatically populate the link field with the same link, because having that link is required for the record to be in that group.


Nice! :blush: That is also our “auto-enter data” trick in Airtable for editable fields.

Justin_Barrett
Terms & ConditionsAccessibility statement