You have to create a relationship from your courses table to the Employee (or Status) table with the status you want: Teacher. You'd put a calc field in the courses table called "Teacher_c" and set it as a text calc and put "Teacher" in the calculation dialog.
From there you can relate this table to the other using the calculated field. Then set your value list to that related table, showing only related values starting from the courses table.
Question: why are you putting the status in a separate table? If employees can be a student or a teacher, it seems to belong as an attribute of employees, thus having that field in there.
(Even if people have multiple status, you can stlll keep the status field in the employee table).
I'll create a quick demo for you.
EDIT: Take a look at this demo. See if this will help answer questions.
StudentTeacherDemo.fmp12.zip 71.1 K
I appreciate you taking the time to show me how to make this work.
The reason I was using a separate table for Status was ( I thought) for record keeping purposes. In other words, in one Course an Employee could be a Student, while in the next Course the Employee might be the teacher. That way all of the records for a particular Employee wouldn't change if I change the Employee Status. But I could be wrong on this one, I'm fairly new to programming with Filemaker, so any suggestions would be greatly accepted.
Hmm. I'd have to think about that. Maybe others can answer the question better.
I had this same issue in a custom app I was working on, and I solved it this way:
1. Each employee has a status: Active or Inactive.
2. Each employee has one or more roles: Here you could label someone as a teacher. I had other roles, but teacher one one of those. I set up a checkbox in this field and allow a user to click on more than one role (as you see in my demo).
3. Even if there are multiple values in this role field, people will show up in lists generated by the relationship technique I demo'ed. If David Copperfield is listed as a Teacher and Student, he will show up in the value list of teachers and in the value list of Students.
And think about this.
3. When you enroll someone into a course, he/she is a student. That is sort of given.
4. When a teacher is set up in a course, he/she is a teacher.
Even if the employee's status changes, his/her record of being in a course (as a student) is kept and his/her status of being a teacher (in a course) is kept as well.
But to keep your model, simply set up the relationship from courses to status to employees table. Make your value list be based on the Employees table in this relationship and relate it starting at the status table.
One last thing (I hope), now that I have the field showing only the Teachers, after clicking on a teacher, the field shows only the ID number instead of the Teacher's name. I tried duplicating the field and switching the field to Display Data from the Employee Table and the c_FullName but instead it shows the first Employee Name in the Database.
Is there some way I can click on the field, show the teacher name and then have it show the name instead of the ID?
See the attached pic.
Are you using FM 15 or another version?
The ID is showing up in the field because it is the field selected in the first column of the "Specify Fields" dialog (the 3rd one). You have to enable the 2nd column and choose another field, the full name, and then click the check box that shows "Show values only from the second field."
does that help?
Yes, I'm using FM15 Pro Advanced and I think I must have screwed something up because now when I ask it to show only values from the second field it now shows a ?
Can you post a picture of your value list set up and of this part of the relationship graph?
I'm wondering if it is something I have done in FM15 because I downloaded your zip file again and now it too only shows the ID number after I select a teacher on the pop-up menu.
It's fine when I click into the field because it shows the teachers name but afterwards it only shows the id number.
Thank you Jeremy,
Oh my goodness! Yep that was exactly what was wrong. Is there a reason that Filemaker added that little detail?
I'm feeling a bit embarrassed that I didn't notice the checkbox.
Thanks Jeremy for all your help.