Here is the situation: I have have 2 tables. One for students and one for counselor. the Students table as a counselor_id field that is related to the counselor_id field in the counselor table. The counselor table has a counselor_id, name, phone and active(yes or no) fields.
When creating a record for a new student, I have a dropdown list based on the counselor table that allows the user to choose the counselor for the student. This works fine.
The counselor table contains all the counselors ever at the school, about 200, and only 10 are still at the school and marked active.
What I would like is to have the counselor dropdown only to display the active counselors.
I tried to do this by creating a new TO relationship based on the active counselors, but then, for the students with inactive counselors, the layout display only shows the counselor_id from the student table.
Is there anyway to do this?