Hi,
I have a base where I keep my business's time entries for each of the employees. However, they may be in a different state based on the day, so I'm looking for a way to populate a field for each time entry showing their location.
The time entries have a date, a person who performed it (currently as a linked record), and the the number of hours.
I currently have a sheet with a separate record for each date and single select field for each employee showing the state that they were in. Note I can change the organization of this table if needed (for example to entries with a date, a single employee and a location).
Any thoughts on how to best set this up? I currently have lookup fields for each individual employee in the time entries table, looking up their location on the given date, and then a formula field that uses a switch that selects which lookup field based on the user who performed the time entry. However, it requires a new lookup field and an update to the formula each time I add an employee. Any ideas on how to make this more robust given that there needs to be a lookup based on two variables (both date and the employee)?
