5 Replies Latest reply on Mar 26, 2011 1:32 PM by pademo57

    Employee can be Instructor or Student on Course

    pademo57

      Title

      Employee can be Instructor or Student on Course

      Post

      At our company an Employee starts out training as a Student and once they have enough training they become an Instructor.

      I've tried to set this up in a Filemaker Database but I'm having a problem relating the files and maybe it's the way I'm trying to relate them.

      Employee File: Empl_ID, Empl_Fname, Empl_Lname, Empl_Code ( Empl_Code for Student = S and Instructor = I)

      Course File: Course_ID, Course_Name, Course_StartDate, Course_EndDate, etc.

      What I wanted to do on the Course Layout was to have a portal for the Instructor and another portal for the Students.

      As a new Course was created you could select from the Instructor portal an Instructor and you could select from the Student portal the Student(s) for the course.

      I got this to work by creating a separate file for Instructor and Student which used the Course_ID in the Instructor and Student File and related it to the Course File.  

      I also wanted to show in the Employee Layout the courses each employee was associated with. But this is where Filemaker created a second Employee file and when I try to show the Courses in a portal on the Employee File it only shows the associated courses for the students and not the instructors.  Argh? Am I making this clear?

      Maybe I am relating these files the wrong way. Any help would be appreciated. 

        • 1. Re: Employee can be Instructor or Student on Course
          philmodjunk

          I doubt FileMaker created a 2nd file. If this took place on Manage | Database | Relationships. It created a 2nd table occurrence of one of the tables in your solution. That's just a new reference to an existing table and is done to prevent relationships that can be traced in a circle on the graph.

          If you are linking employees to Courses, it would seem you need a third table:

          Employees----<Enrollment>------Courses

          Employees::Empl_ID = Enrollment::Empl_ID
          Courses::Course_ID = Enrollment::Course_ID

          This assumes that one course can be taken by many employees and that an employee can take many courses.

          To enroll an employee in a course (including an instructor employee), you creat a record in enrollment with the employee's ID and the Courses ID in order to link the two. A portal to Enrollment on the employees layout can be used to list all courses they have enrolled in. You can add fields from the courses table to the portal row to provide a course name, etc. in the portal rows. A portal to Enrollment on the Courses layout would list all Employees enrolled in the course.

          You can set up two portal's on the Course layout that filter the records differently so that one lists Instructors and one Lists Students. Exactly how you filter for instructors vs. employees depends on the version of FileMaker. The simplest version requires FileMaker 11, so let me know what version you are using so that I can describe an approach the will work for your version of FileMaker.

          However, couldn't an employee be rated as an instructor in one course and a student in others? If so, then you'll need another table to list their status for each course.

          • 2. Re: Employee can be Instructor or Student on Course
            pademo57

            1) Yes, I meant a 2nd Employee Table.

            2) Yes, Employees can take many Courses and Courses can have many Employees.

            3) Not currently, once an Employee becomes an Instructor, he stays as an Instructor.  But I'm sure once I get this working, the powers that be, would like that option of another table so Employees can be an Instructor on one Course and a Student on another Course.

            4) My current version is Filemaker Pro Ver 11.0v3 on a Windows Vista system.

            • 3. Re: Employee can be Instructor or Student on Course
              pademo57

              I should add that when I set this up I used this configuration:

               

              Employee  --< Instructor >--- Course

              Employee2 --< Student >--- (Connected to the above Course)

              But if there is a better way, please show me the way.

              • 4. Re: Employee can be Instructor or Student on Course
                philmodjunk

                1) No it's a table occurrence. A table is something else. See this thread:   Tutorial: What are Table Occurrences?

                Yes. There's a better way, see my earlier post. Use a join table (enrollment) to link employees to courses.

                Given your current setup where an employee is an instructor or a student, but not both, you can set up two portals to Employee on your courses layout, but use a portal filter on each to restrict the visible records to those that are rated as "instructor" in one portal and those that aren't in the other.

                The filter expression for the student's portal would be: Employee::Empl_Code = "S". Change "S" to "I" and you have the filter expression for instructors.

                When the time comes to rate employees as instructor on a course by course basis, add yet another "join" table and use it to link your employee instructors to specific courses:

                Employees----<Enrollment>----Courses----<Course_Instructors>-----Instructors

                Instructors, like Employee2 in your example, is a 2nd table occurrence of Employees. Now you can list students by using a portal to Enrollment and Instructors by using a portal to Course_Instructors.

                • 5. Re: Employee can be Instructor or Student on Course
                  pademo57

                  Thanks PhilModJunk,

                  1) Right TO not Table.

                  Once again, thanks for straightening that out.  For some reason I seem to be making this harder than it really is.  Perhaps because I have been writing web pages using PHP and MySQL and I am used to writing everything out like WHERE statements and so forth.

                  Well, that's my excuse but I really appreciate all the help, it's working great.