I work for a school district and want the value lists to depend on a choice from before, but can't seem to get it work correctly.
Building - contains all the schools we have, easy to do, this is the start
Department- each building will contain different departments, some will be grade levels, others will be subjects, this is dependent on the building chosen
Staff- this will be the staff that is in the building chosen and the department selected, this is the one I actually get to work as I created a self join with the above two as global fields and if they are selected everything works.
The problem is the middle value list, departments, the only way I can get it to work is to show all records not just the ones for the building chosen.
Here is table occurances view: