Autofill Department in a cell after selecting a Job in another cell

I’m creating an employee contact list and would like to be able to enter or select a Job and then have another cell populate the Department that corresponds to that Job.

For example, as I enter a new employee into the table, I’d like to be able to select “EM Vet Tech” in the Job row/cell and then have the next row/cell populate “Emergency” as the department. I’d also like it to later change the department when I change the job - so if I change job for a person from “EM Vet Tech” to “GP Vet Tech” I’d like the department to change from “Emergency” to “General Practice” automatically.

Is this possible? I’ve looked all morning and searched through the help but can’t quite find what I am looking for. I’ve also tried playing with linked tables, but I feel like the more I try to find a solution the more confused I get.

It sounds like you’re trying to leverage the lookup functionality. Have you seen this yet?

Would the “EM Vet Tech” be in the employee’s job title field? Perhaps you could have a table with job titles and the corresponding departments they relate to? Just throwing that out there…

-Jeff

Hi @Megan_Warner - there’s a couple of ways to do this, but this is the way I would approach it:

Have a “Jobs” table with the Job Name and Department:

In your “People” table have a link field to the jobs table (so linking a person to a job). Also have a lookup field which looks up the department from the selected job:

Now, when you select the job (or change the job), the Department value will populate automatically.

Breaking out jobs into its own table also allows you to see, for example, counts of people by job type:

JB

Arguably, “Departments” could be its own table too, although your specific scenario would dictate whether or not this is worth doing, e.g. if you wanted to capture other data about departments