10 Replies Latest reply on Jul 14, 2016 2:28 PM by pademo57

    Sometimes a Student, sometimes a Teacher - Filter Dropdown List

    pademo57

      In our business an employee can either be a Student or a Teacher or some other designation.

      I have 4 files: Employees, Courses, a join table j_Emp_Cour, and a Status table where the Status table has a relationship with the Employee table.

       

      What I am trying to do is when someone sets up a new course I would like a drop-down list showing only the Teachers for this course.

      I tried putting a global field in the Employee table but I couldn't get the drop-down list to only show an Employee with the status of Teacher.

       

      Could someone show me how to set this up please?

       

      Pademo57

        • 1. Re: Sometimes a Student, sometimes a Teacher - Filter Dropdown List
          jbrown

          Afternoon.

          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.

           

          jb

          • 2. Re: Sometimes a Student, sometimes a Teacher - Filter Dropdown List
            pademo57

            Thanks Jeremy,

            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.

             

            Pademo57

            • 3. Re: Sometimes a Student, sometimes a Teacher - Filter Dropdown List
              jbrown

              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.

              • 4. Re: Sometimes a Student, sometimes a Teacher - Filter Dropdown List
                pademo57

                Hi Jeremy,

                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?

                 

                Pademo57

                • 5. Re: Sometimes a Student, sometimes a Teacher - Filter Dropdown List
                  jbrown

                  See the attached pic.

                  Demo_VL.png

                  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?
                  Thanks.

                  • 6. Re: Sometimes a Student, sometimes a Teacher - Filter Dropdown List
                    pademo57

                    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 ?

                    • 7. Re: Sometimes a Student, sometimes a Teacher - Filter Dropdown List
                      jbrown

                      Can you post a picture of your value list set up and of this part of the relationship graph?

                      • 8. Re: Sometimes a Student, sometimes a Teacher - Filter Dropdown List
                        pademo57

                        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.

                        • 9. Re: Sometimes a Student, sometimes a Teacher - Filter Dropdown List
                          jbrown

                          Check out the pic.

                          FM 15 comes with a new checkbox that you will have to make sure is checked (I think).☺ Pic_VL.png

                          • 10. Re: Sometimes a Student, sometimes a Teacher - Filter Dropdown List
                            pademo57

                            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.

                             

                            Pademo57