Feb 23, 2022 11:36 AM
I have a field for employee names created. It’s a single select field with all their names available to choose. Right next to it I have an email field. I’d like it if when I choose a name, their email address automatically populates in the field next to it.
Can someone help me figure out how?
Feb 23, 2022 12:40 PM
Hey @Chris_Schultz! Welcome in!
To achieve your desired end result, we’ll have to look at your base design/schema.
It seems that you have a single record that has data about something.
But in those records, you have fields where you must enter employee information.
That’s where your single-select and relevant email fields come into play.
Here’s a viewing link to the example base I created for this post. Feel free to copy it or poke around and see what’s up.
So, here’s how I would solve this:
Since you have redundant data entry (in this case, you’re having to enter in employee info multiple times), we will actually want to create another table that holds employee records.
Since I’m missing a bit of context on your use case, I’m going to create an example and walk you through something similar.
I’m going to create a base that tracks reports about events that happen during a shift.
Now, with each report, there needs to be an associated employee.
So, we’ll have two record types that we need to keep track of… Employees & Reports.
Here’s what the Reports table looks like.
Here’s what the Employee table looks like.
Doing this allows you to do a few things.
By doing this, we can store data that relates only to that given record.
In our example, the employee record only stores data about the employee, and the reports table only stores data about the report.
This prevents data bloat.
If you have one record with 30 fields, and 10 are for the actual report itself, 10 are for recording the employee information, and the rest are formulas that use the other 20, then you’ve probably doubled the number of fields you need.
Additionally, you prevent yourself from having to fill in the same pieces of data for every single new record.
So, how does this solve the issue of getting employee emails (amongst other data) into the report record?
Well, Airtable at its core is designed as a relational database, with a heavy emphasis on the relational part.
What this means for us is that we can take those two very different records, and define a relationship between the two records.
In this case, we associate the report to the relevant employee.
What’s great about this is that we can use that relationship to selectively pull data through the link.
In this case, we can use a lookup field to show us the employee email in the report record.
You can create a 1:1 lookup field for each field in the Employees table.
Here, I have created two. One for the email address and one for the phone number.
Once you do this, the email and phone number fields will remain blank until I link an employee record.
When you link the employee record, the lookup field will automatically populate with the data from the employee record.
Depending on the size of your team, using the single select field can be a nightmare.
Let’s say you have a team of one hundred employees. That means that you will need to create a single select option for each employee.
But let’s say that your team isn’t that big and it’s really not that much of a concern.
The next layer of problems you encounter is something I briefly touched on before.
You’d have to enter all the relevant employee data for each new record.
Feb 23, 2022 12:55 PM
I tried but don’t know what I’m doing. If you look below, I just want it so that when I choose a name, their email automatically pops in on the field next to it. Is this the process to make that happen?
P.S. I’m an Airtable NOVICE.
Feb 23, 2022 01:03 PM
I recommend that you take a look at the base I linked in my original post.
Go ahead and make a copy of it in your own workspace. From there, you’ll be able to see how it behaves and you will also be able to add/remove data as you wish!
Keep coming back with more questions and I’d be happy to give a more direct step-by-step :blush:
Feb 23, 2022 01:10 PM
Still doesn’t make sense to me. I don’t see how if I creat a “single select” field, and put in a bunch of names as options, that it will populate the correct email address for that person in the “email” field. All I see is that one table will have fields that copy data from another table’s fields.
Feb 23, 2022 01:45 PM
So, let’s start from scratch.
We’re gonna use the same example use case I outlined originally.
Once you’ve done that, it will look like this:
We want to create a link to the Reports table, so we will select the Reports table as our target.
Once the field is created, you’ll have two options:
Enable Allow Linking… and leave the second option alone.
Now, fill in the employee’s name and their email address into the record.
Now, hop back into the Reports base.
You’ll notice a new field has appeared called Employees.
This is our link to the employees’ table.
If you select the new field, you’ll see the single record in the Employees’ table, in this case, it is Jonathan’s record.
Select the record, and it will then appear in the linked record field.
Now, we want to get the employee email from the other table.
So we’re going to use a lookup.
Create a new lookup field.
For the settings, we’ll lookup from the Employees table.
And for the field, we’ll use the Email field.
Don’t worry about any of the other settings.
You will now see the employee email address in the lookup field.
Feb 23, 2022 02:31 PM
That’s not the right answer though. You are showing me how to link the data in one field from a table to different field on a different table. But that doesn’t show me how to automatically have a particular employees email address auto populate. So if I pick “Dean” in the single select field, the email field right next to it fills out with his email address. If I go to the same single select field and choose “Kristi” I want the email field right next to it to change to Kristi’s email address. It’s ok though, I will figure it out.
Feb 23, 2022 11:34 PM
You are using a single-select field.
Single select fields will never automatically populate other fields unless you build a needlessly long, bloated, and inefficient automation or use a service like Zapier/Make/Integromat.
Delete your single select field.
Replace it with the linked record field.
Create the lookup field.
It will work.
Feb 24, 2022 06:35 AM
Thank you for your help Ben, but I don’t think you are understanding what we are trying to accomplish. I do truly appreciate your time.
Feb 25, 2022 01:16 AM
Feb 25, 2022 02:33 PM
@Chris_Schultz - No need to pay me lol.
I’ll build it, explain it, and deploy it for free if you want.
I’m just bored.