Primary Key and Linking Tables

[Mandy_Hutchison]

Ok, I know this is probably a no-brainer for everyone here, but I figured I would ask instead of wonder.

When you “Link” a table to another, it’s really only linking to the primary field of that table, and to reference any other part of that table you have to then use a “Lookup” field, correct?

[Jeremy_Oglesby]

That’s the basic gist of it, @Mandy_Hutchison.

The link isn’t really a link to a particular field, per se — it’s a link to the record. It’s a “doorway” to access all the fields of that record. Airtable happens to give you access to the primary field via the link, but not necessarily in the way you’d think.

Supposing the primary field of the record you are linking to is a “Single Line Text” field, with the name “Jeremy” in it — if you reference your link to that record in a formula (as in, you reference the actual linked record field), it’s not really referencing a “Single Line Text” data type. The data for the name “Jeremy” from that record is actually an array with only a single element in it :: array[‘Jeremy’]. An array with more than one element would look like this :: array[‘Jeremy’, ‘Bob’, ‘Jerry’]. So if you had links to those 3 records in your linked record field, and you reference that field in a formula, you will be dealing with an array, not a string of text. This is true no matter what data type you have in the primary field - dates, numbers, etc.

So, you see, that the link to another record is not really a link to the primary field. If you want to retrieve the primary field from that record as its original data-type, you still need to pull that field in to your table with a Lookup field. And same goes for all the other fields — to have any access to them as their original data-type, you have to pull them in to your table with a Lookup field.

[Mandy_Hutchison]

Thank you Jeremy,

Is there anyway to link to a particular field in a table? When I link a field in a table to another table, it populates with whatever is in the primary field of that table. What if I want the linked field to say something else? Does that make any sense?

[Jeremy_Oglesby]

No, the linked record (the little blue box that shows up in the field) will always show whatever is in the primary field of that record in its own table. Airtable does that because it nudges you into using the primary field as the identifier of a record, and assumes you will (and rightfully so), and this, they want to represent records to you by their identifier.

To manipulate this, I often use a formula in my primary fields that concatenates the most important info I need to see from that record when it is represented to me in another table’s linked record field. Thus, I end up with formulas like this:

IF({Next Eval Completed?}=TRUE(), "✅",IF(IS_AFTER({Next Due},TODAY()),"🔵","❌")) & " " & {Person Initials} & " - " & {Certification} & {ASTM} & IF(OR({AASHTO}=BLANK(),{ASTM}=BLANK()),"","|") & {AASHTO} & IF(OR({AASHTO}=BLANK(),{ADOT}=BLANK()),"","|") & IF(AND({AASHTO}=BLANK(),COUNTA({ASTM},{AASHTO},{ADOT})=2),"|","") & {ADOT} & " (" & DATETIME_FORMAT(Date,'L') & ") " & ID

That represent the primary field of my records like so:

Giving me a summary of important info about that record (status via emoji, initials of technician, test method abbreviation, date, and a unique ID).

[Mandy_Hutchison]

Thank you Jeremy! That’s exactly what I needed to know.

[Janic_Heen]

Hi, Hope I can trail further on this notion, to address something that I’m struggling to achieve in airtable. You wrote:

Jeremy_Oglesby:

No, the linked record (the little blue box that shows up in the field) will always show whatever is in the primary field of that record in its own table.

This means that the linked record holds a link to the actual full record that it is linked to. Therefore it is also clickable as the little blue box. But what if I wanted to make a similar ‘clickable blue box’ - linked to the related record in the same way - but containing a text that represents the content of another field in the related record?
When making a lookup field, that connects with the linked field and pulls out a specific field, this just shows up as text data. No ‘blue box link’ connecting it to the related record.
Is there any way of achieving this?

Any reply and directions, much appreciated.