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.
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:
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.
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.
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.