Linked records - can I see field data instead of primary key


#1

Hello, I am brand newt Airtable. So far it has been very good and a pleasant experience. I am a long time user and developer in MS Access. So there is some adjustment. In Access, when you link two tables, you can tell Access that you want to link via one field, but display another.

So, I have a table with a linked field to another table. Airtable is showing me the primary key. Is there a way to show a more informative field? I am so used to using primary auto numbers that I cannot break the habit. So now all I see is a record number. I would like to see say the customer last name that is part of that record.

Thanks in advance for the help.

Best, Scott


#2

If you make your Primary Key a Formula field then you can include any/all fields that you like.

Here’s the formula I used for the Primary Key in the “People” table. With this as the Primary Key, the auto-complete for records in that table provide extremely detailed information by which to search for records.

IF(
	{Member of} = {Manager of},
	{Manager of} & " Manager - ",
	IF (
		{Member of},
		{Member of} & " - ",
		""
	)
)
& {First Name} & " " & {Last Name}
& " ( " & DATETIME_FORMAT({Hire Date},"YYYY MMMM") & ")"

https://airtable.com/shrURox04YHvRdjey


#3

Thanks so much for the help. That did the trick. I hate learning curves. Another question, can I exclude a field in a view? I don’t always need the primary key. Thanks


#4

As this screen shot shows, you can hide any field EXCEPT the first field/primary key—in this case the field named, Slug cannot be hidden in a view, nor rearranged.


#5

I am in a similar (frustrating) situation – coming from many years of Access autonumbering and column choices in dropdowns. The formula above looks more like macro code to me. – In other words, how do I use this formula/where do I put it? I have an autonumber primary key for each of my tables and typical text info in the record (such as in the Items table: PrimaryKey: 1 ; Item field: Blue box / PrimaryKey 2 ; Item field: Red box).
I would like to see only the text in the item field – but can include the PrimaryKey if necessary.
– btw / Airtable’s ease of use and access is amazing. Will certaiinly be my go-to db app once I adapt.


#6

I’m curious as to why you actually need the auto-number column. Regardless, here’s how I suggest “moving” your Auto Numbered Primary Field to a secondary field so that you can use a Formula field for your primary field:

  1. Open your Airtable base in the desktop app or a desktop web browser (not the mobile app).
  2. Right-click your primary field title and select “Insert Right”
  3. Select field type “Auto Number” for this new field
  4. Left-click your primary field title and select “Customize Field Type”
  5. Select field type “Formula” for your primary field
  6. Enter a formula to “calculate” the value displayed in your primary field (hence used for auto-complete in related tables)

Use this link to learn more about how to construct a formula. If you get in a pinch, post a follow-up reply here and be sure to include the exact names of your columns so that we can give you a formula you can copy-paste.


#7

I believe this is a related request; however, if not please advise where best to post it.

I am creating a referral table and I have a column called “Referred By” in the table.

I also have a field called “First Name” and “Last Name” and I use a formula in the primary field to put the two together thinking that it would help…

What I would like to do is if person in row 1 referred a new person in row 2, I wanted to lookup my list of “people”, in this case the person in row 1 from a list to add as the referred by person in row 2 (and 10, 22, whatever.)

Whenever I choose a field type of “Lookup”, and it defaults to “Choose a Field in this file”, none of the above mentioned fields appear in the list. It only seems to be fields from other tables that are already in this table.

Hope this makes sense. If not, please let me know what additional information you would need.
Thanks,
Charlie-