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