6 Replies Latest reply on May 15, 2017 6:26 AM by coherentkris

    Table Relationship Question - Join Table

    gswest

      Hello,

       

      So I'm working my way through learning Filemaker and I am creating a Gradebook. I have set up the following tables:

       

      Class

      Assignments

      Students

      Lectures

       

      I want to be able to set up a list of Assignments in my Classes Table by using a Portal to Assignments.

      I a then want to add students to a class via another portal.

       

      My problem is this...I want students to have a portal that shows the assignments they have for a class.

       

      So I can't connect Class and Assignments, Class and Students, and Students and Assignments. I tried adding another Table - StudentAssignments but it can't be connected to both Assignments and Students. I'm stuck as to how to fix this. I have parent keys for all tables and foreign keys for Students and Assignments as well as Lectures.

       

      Any help on this would be greatly appreciated.

       

      Geoff

        • 1. Re: Table Relationship Question - Join Table
          philmodjunk

          The answer is in the name of your question. You need some join tables to support the many to many relationships involved.

           

          Note that "classes" can have two different interpretations depending on how your school is organized. You might have a "class" of Mathematics with "sessions" or "periods" to which students are assigned to. You might have first period students taking math and a different group of students taking math during second period. Or you might choose to consider each Time slot that you teach a "class" with a specified subject for that time slot. It will help us to help you if you clarify those distinctions.

           

          But you will definitely need  Join table facilitating a link between students and assignments:

           

          Students----<Student_Assignment>------Assignments

          Students::__pkStudentID = Student_Assignment::_fkStudentID

          Assignments::__pkAssignmentID = Student_Assignment::_fkAssignmentID

           

          The grade or score that a student gets for completing an assignment can be recorded in a field that is defined in Student_Assignment.

           

          Your portal to list assignments on a student layout would not be a portal to Assignments but rather a portal to Student_Assignment. Descriptive fields from the assignment table may be included in this portal.

           

          You would also link assignments to either a "class" or a "session" via a different relationship linking assignments to classes. Once you have made that link, a script can populate the student_Assignment table with a new record linked to that new assignment for each student attending that class.

          • 2. Re: Table Relationship Question - Join Table
            gswest

            Thanks for the quick response. Unfortunately, I am being unbelievably dense...

             

            GradebookDatabaseRelationships.png

            So I've tried linking Assignment to Classes and Assignments to ClassAssignments and then Class. It says I have to create a new table to do that. I know this is something simple but I just can;t wrap my head around it.

            Thanks again for the help though.

             

            Geoff

            • 3. Re: Table Relationship Question - Join Table
              philmodjunk

              Better re-read that dialog that pops up. You are not being asked to create a new table, but rather a new occurrence of one of your existing tables. This adds another "box" to the relationship graph, but does not add a table to your database as the boxes, table occurrences as we call them, are not actually tables, but the means to access a table via a specific name and set of relationships.

               

              You may find this tutorial useful:

              Tutorial: What are Table Occurrences?

              • 4. Re: Table Relationship Question - Join Table
                gswest

                Got the table occurrence created. But now I'm trying to figure out how to create the portal that will allow me to create the necessary related records. I know I have to run a script when a student is added to a class. Does this sound right?

                (I know this isn't in the script form yet, just trying to figure this out)

                 

                Trigger Script when adding Student

                Get __pkClassID, __pkStudentID

                Count # of Assignments associated with Class (12 for example)

                Go to Layout for StudentAssignments

                Set Up Loop to repeat Count # Times

                     Get __pkAssignmentID for Assignment Record #

                     Create New Record putting StudentID and ClassiD into _fkStudentID and _fkClassID and __pkAssignmentID for Assignment Record # into _fkAssignmentID

                End If # = 0

                Return to Original Layout

                 

                Does this look right?

                 

                Thanks,

                Geoff

                • 5. Re: Table Relationship Question - Join Table
                  philmodjunk

                  Your script seems based on the assumption that a newly added new student should be assigned all previous assignments.

                   

                  The main idea is correct but not all the details. You would use the relationship between class and assignments or ExecuteSQL to get a list of assignmen IDs in a variable. Your loop can then get the needed IDs from the variable in order to create the needed records in the join table.

                  • 6. Re: Table Relationship Question - Join Table
                    coherentkris

                    FYi a "join" table is called an associative entity in relational database terms and is used to solve the many to many problem.

                    For details look on Wikipedia

                    Associative entity - Wikipedia