I need some help in figuring out a better approach to a problem of filtering a list from a table.
I have 3 tables:
On the job table record I want to select a department then select a person but filter names to show only people from that department.
I can get this to work BUT foresee this causing problems down the road.
For example if John Doe was in Creative Service Department when the record was created now he moved to Engineering so we change his department group in the Person table. When you go back to records once assigned to him I see a primary key displayed because the filter will only show people related to that department.
See my attached simple db I created to replicate this problem.
Any ideas of how I can go about this?