Linked Field Lookup Options

[Wendy_Yelsik]

In my training base, my tables are “Tasks,” where I assign their program. The linked field, “Elink” looks at the table “Employees.” The primary field in the employee’s table is the four-digit code. I have their full name listed in a separate field. How can I see the employee’s code & name in the Elink field when I look it up in the “Tasks” table? Wendy

[Nathaniel_Grano]

A common convention in this kind of scenario is:

• Move your ID codes to a new field that isn’t the primary field
• Make the primary field a Formula field that concatenates several other fields together. The formula would be something like:
  {EmployeeID} & ": " & {Employee Name}

You’ll need to replace {EmployeeID} and {Employee Name} with the names of your fields.

• My example formula above will result in values like “8213: Jane Doe” in your Elink field.
• Doing something like this also means that when you type into an Elink cell you can search the existing records by either ID number or Name and both will result in matching records!

[Wendy_Yelsik]

Thanks, Nathaniel. That will work and I didn’t know it would search by name or code! But, I copy and paste records into that table using the linked field (i.e. 1234,5678,9999,etc). If I change the primary to a longer format will I lose the capability to copy and paste, or import, using only the four-digit code?

[Nathaniel_Grano]

Indeed, not only will you no longer be able to paste the 4-digit value to create new Employee records you also won’t be able to paste to create records at all as Airtable does not have a way to parse your value into the fields that make up the formula. Pasting will still work as a way to link existing records but only if you refer to them by their full record name (i.e. ‘8213: John Doe’).

Sometimes, I’ll temporarily adjust the structure of my table to do a pasted import:

• duplicate the primary formula column
• change the primary formula column back into a numeric field
• copy the IDs from the ID column into the primary column
• perform the import/paste
• then change it back to use the formula again

For me, my usage of the table depends on which way I set it up:

• If I am more commonly reading values or adding them one-by-one or linking existing records, I’ll use the formula field
• If I am more commonly wanting to paste in values to create new records, I will leave it set up as you had it originally, but include the Employee Name as a lookup field in the Tasks table right next to the Elink field

[Wendy_Yelsik]

Thank you for your detailed responses! - Wendy