In our business an employee can either be a Student or a Teacher or some other designation.
I have 4 files: Employees, Courses, a join table j_Emp_Cour, and a Status table where the Status table has a relationship with the Employee table.
What I am trying to do is when someone sets up a new course I would like a drop-down list showing only the Teachers for this course.
I tried putting a global field in the Employee table but I couldn't get the drop-down list to only show an Employee with the status of Teacher.
Could someone show me how to set this up please?