Are you asking for two value lists--one for teachers and one for counselors?
Or are you asking for a setup that finds the records for all counselors when you select "counselor" and finds the records for all teachers when you select "teacher"?
I think you want the first option.
You have a field in teachers that contains "teacher" if they are a teacher and "counselor 'if they are also a counselor?
You can define two calculation fields:
cCounselorKey : If ( type = "Counselor" ; TeacherID ; "" )
cTeacherKey : If ( type = "Teacher" ; TeacherID ; "" )
I'm using a field, TeacherID that would be set up as a serial number field because its possible to have two teachers with the same name. To see a list of teacher names define a two column value list listing cTeacherKey in column 1 and the teacher's name in column 2. Selecting a value from this list will enter the TeacherID--not the name into the field you format with a value list.
Do the same with a second value list but refer to cCounselorKey instead of cTeacherKey to get a value list of teachers who are also counselors.
This is a simplified version of a conditional value list. It's also possible to use a relationship between two (or even the same ) tables to do this, but that doesn't seem necessary based on what you have posted here.
I tried your solution. I created the two calculation fields as you mentioned, cteatherkey and ccouncelorkey.
Then I assigned a drop down list with a value list specifying ccouncelorkey plus the name. But when I tested it I see a list of only the first record with value in ccouncelorkey although more records have values (as I checked from the table layout).
I went to the table and changed the type of ccouncelorkey to text (it retained all values) then I tested again and I got a correct drop down list.
Does this mean that a calculation field cannot be in a value list or do I have another problem?
I would also be interested to know about the solution using relationships, because this situation will repeat a lot throuout my system.
I believe you might be interested in conditional value list. First field would hold Type (whether Counselor or Teacher) and the second list would hold the names of that Type.
A demo is worth a thousand ill-placed words. Here is a demo by Comment (Michael Horak) which is the best example available. He has several over on FM Forums (which is free to register) and you can download other conditional value list examples from his Users blog. This is simple but is 2-tiered & self-clearing
He has more complex 3-tiered and conditional value lists is portals as well.
I figured out why the calculation field was not working for the value list. I noticed that I had to specify whether the result is text or number. It works now.
And LaRetta has posted an excellent example of the relationship based approach I referred to. (Thanks)