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

[Scott_Brasted]

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

[Chester_McLaugh]

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

[Scott_Brasted]

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

[Chester_McLaugh]

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.

[D_Rouse]

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.

[Chester_McLaugh]

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.

Airtable - Support

Formula field reference

For an overview of formula fields, please refer to the Guide to Formula, Lookup, Count, and Rollup fields. Formulas may involve functions, numeric operations, logical operations, and text operation...

[Charles_Pendlet]

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-

[Wendy_Crews]

Chester_McLaughlin:

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

Hi Chester. I am trying to use your formula and clicked on the link to your table to see how the formula works. Unfortunately, the link is closed. Can you please re-send it so I can see your table and see how the formula works? Thanks!

[ScottWorld]

There are at least 3 solutions here:

1. Use a formula field as your primary field in the linked table. You can right-click on the primary field’s column header to change the primary field to another field.

or

2. Create lookup fields or rollup fields to display the data that you want to see from the linked table. I discuss formulas and lookup fields in my free Airtable training course, which you can take for free by signing up for a free 30-day trial with LinkedIn Learning.

or

3. If you’re just looking to search and match on other fields in the linked record table, you can use Fillout’s advanced forms for Airtable, which lets you search and match on ALL THE FIELDS YOU WANT from the linked record field!

Fillout offers 2 types of forms:
1. A form to update existing records in Airtable.
2. A form to create new Airtable records.

Both forms give you full control over your linked record fields… you can search and match on any linked record field that you want, and then, after you make the selection, you can display lookup fields, rollup fields, formulas, etc. on your form. Fillout also offers about 100 other advanced form functions that you can’t get from Airtable.

p.s. If your company has a budget for your project and you’d like to hire an expert Airtable consultant to help you with any of this, please feel free to contact me through my website: Airtable consulting — ScottWorld