I am trying to create a conditional value list based on two tables: One is for students. The other is for teachers. I would like to assign a teacher to a student from the student record by using a pull down menu with a value list of teacher's names. However the value list must only include active teachers (vs. inactive teachers on sabattical). So the value list would only show active teacher names.
Do I need to create a calculation If/Then field? (If teacher status is active, then teacher name)...something like that. I am so close to getting this to work. I can create a pull down menu that shows the active teacher ID#s (using a calcuation tile for Active ID#s), but when I add a second field (teacher name) and then say display only second field (when specifying fields for value list), it then shows all teachers, rather than just active ones. If I delete the second field and just show the active ID calculation tile, it works fine, but then my menu is only a list of numbers. Please advise? And thanks in advance.