7 Replies Latest reply on Mar 20, 2010 5:19 AM by Jade

    Basic Relationship Setup - Teachers

    briananderson

      Title

      Basic Relationship Setup - Teachers & Students

      Post

      I'm fairly moderately new to FMP10 and I believe this is rather simple to do, but my head isn't working to form it 100%. 

       

      I'm trying to set up two separate forms. One for Teachers that show a list of all their Students. One for Students that list all their Teachers.  I've got two tables, one for Teachers and one for Students, that relate to each other via a serial # TeacherID. 

       

      When I put together the form for Teacher, using a portal to list the Students (and am able to add new ones), it works great. 

       

      The problem is when I want to create a Student form, I get a list of duplicate students (because they are associated to numerous other teachers.)

       

      I want to be able to have just a list of students and their associated teachers and vice versa with the teachers. 

       

      I know I have some kind of relationship problem or table setup, but I can't figure it out. Could someone point me in the right direction?

       

      Thank you in advance!!!

       

        • 1. Re: Basic Relationship Setup - Teachers & Students
          Jade

          Hi Brian,

           

          I think you are describing a many-to-many relationship (i.e. one teacher has many students and each student has many teachers).  This is generally handled using a third "join" table.  The join table (Teacher_Student) contains only one teacher id and one student id per record.   It is used as the portal basis on both the teacher and student layouts.

           

          The relationship looks something like this:-

          Teachers -< Teacher_Student >- Students

          • 2. Re: Basic Relationship Setup - Teachers & Students
            carolynhaywood

            Hi Jade!

             

            I have a question about how this works.  Do you enter all the records on a layout based on the  join table?  And are the Student and Teacher tables empty?

            • 3. Re: Basic Relationship Setup - Teachers & Students
              Jade

              Hi Carolyn,

               

              Only the portals are based on the join table.  The Teachers and Students records are not empty; they contain the names and other data related to each.  

               

              Setup the relationships to allow creation (and deletion) of the records in the join table via the relationships.

               

              The portal on the Teachers layout contains the Teacher_Student::Student_id and Students::Name (plus any other student data fields you want).  Most likely, the Teacher_Student::Student_id is a drop-down menu based on a Student value list.  When you select a student from this list in the portal, it will automatically create a join record with the Teacher_id and Student_id.

               

              The portal on the Students layout contains the Teacher_Student::Teacher_id and Teachers::Name (plus any other teachers data fields you want).  Probably, the Teacher_Student::Teacher_id is a drop-down menu based on a Teachers value list.  When you select a teacher from this list in the portal, it will automatically create a join record with the Teacher_id and Student_id.

               

              So, using the Teachers layout you can assign students to a Teacher.  Conversely, you can assign teachers to a student on the Student layout. FMP maintains the join table records via the relationships.

               

              • 5. Re: Basic Relationship Setup - Teachers & Students
                briananderson

                Thanks Jade! Took me a million times of reading your reply for it to sink in! But it works! Thank you!!!!!

                • 6. Re: Basic Relationship Setup - Teachers & Students
                  briananderson

                  A followup question.

                   

                  With the same setup, how can I have the Teacher Form create a new student and still keep it associated to that teacher within the same portal?  I can select a student previously entered, but right now, I cannot enter a new student.

                   

                  Any help would be great!

                  • 7. Re: Basic Relationship Setup - Teachers & Students
                    Jade

                    Well, I'm glad you have it working.  It is far easier to do than describe.  I was hoping that you wouldn't ask your last question…

                     

                    You can add another portal to the Teachers layout based on the Students table.  Check the "allow creation of records via the relationship" checkbox for Students on the Students to Teacher_Student relationship.  This will allow you to add a New Student record in the last row of this Students portal.  Since the Students record is added from a specific Teachers record, you will find that FileMaker will also add a new record to the join table automatically.  In turn, the new Student record will also be displayed in your first portal that is based on Teacher_Student.

                     

                    So why have 2 portals? Here is the downside (at least I haven't found an easy solution): you cannot use this new Students portal to assign any existing Students to the Teachers record because it is based on the Students table and not on the join table.

                     

                    It may be neater to allow the user to switch to the Students Layout instead (perhaps with a scripted button or open a new window).