Nov 02, 2016 05:57 PM
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
Nov 02, 2016 06:40 PM
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") & ")"
Nov 03, 2016 02:31 PM
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
Nov 03, 2016 02:39 PM
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.
Feb 08, 2017 09:53 AM
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.
Feb 08, 2017 10:32 AM
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:
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.
Feb 15, 2018 03:38 AM
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-
Jul 16, 2019 06:42 AM
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!
Mar 31, 2024 03:55 AM - edited Mar 31, 2024 04:22 AM
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