5 Replies Latest reply on Oct 14, 2013 12:40 PM by simonkunak

    Gradesheet project, how can I set relationships properly? - Please, help!


      Hello to everybody!


      I’m struggling to create a database in which I can keep a record of my students grades. It seems a pretty simple project but the way I need it to work makes it a bit tricky.


      I attach here my database to give a clearer idea abut my problem.


      I’ve created a database with four tables: Tests, Students, Groups and Grades. My main goal is to be able to navigate through each Test record and use a portal to show the assigned students to each of those tests along with the grade fields so I can fill the grade fields in and create the related record as I type. In other words, this would look like a classic grade sheet (one for each test)!


      Of course, this would be only part of a more complete database but this is the part that is giving me a big headache so I’d be really grateful if any of the experts around could give me a hand!



      The ideal workflow for this potential database is as it follows.


      To start, a user would have all the students details already filled in the Students table (with the fields Student_Id, Student_name and Group)


      Then, the user would create the test records (that is, in the layout named Tests) and would initially assign each test to one group of students (A, B or C).


      Ideally, each test should be assigned to a group of students but we must leave the door open to:


      A) Some students on the assigned group not to appear in the grade sheet for a particular test (or, in other words, be able to ‘take out’ some students that will not need to go through a particular test)




      B) Some students from one group to be included in the grade sheet for a test which is originally set for other group.


      Then, the user should go to the layout named Gradesheet to navigate through each tests and be able to start filling each test with each assigned student grades.


      So far, I’ve reach a version in which I manage to accomplish all my goals BUT ONLY for the first test assigned to each student. It works fine until we need to go for a second test assigned to one group or to one student. Then FIlemaker shows only the first grades for the student.


      I believe that the solution is linked to the relationships and, of course, I assume that I’m stuck in something really stupid here!


      Besides, I suspect that the tables and relations structure that I’m using has the chances to provoke later problems (orphan records if any change after grading by mistake, the repeated field as a way to get a selection of students, etc.) So, I’m widely open and really grateful to any suggestions or advise regarding a full new structure for this project.


      Many thanks in advance!

        • 1. Re: Gradesheet project, how can I set relationships properly? - Please, help!



          Your biggest problem is your relationship from students directly with tests

          Your tests needs to be used in several circumstances but what you have done is restricted it to one.... hence the one record.

          Your way allows students to only do one test once... ever.


          Your record of the assignment of the test is the grade record.


          Tests needs to sit independently on the relationship graph and the join to students broken.

          I would also do the same with grades.

          Instead create a table instance from the Grades table and call it 'sudent Grades' and rejoin to students. Then on the right of it you could join to a table instance called Grade tests... or similar.


          Simiarly, it seems that one student can only ever belong to one group. That fixed mentality is also restrictive but may be necessary. I would have to examine things a bit more deeply to determine if this is so. The way you have it, you don't need the group table at all as you already have the facility in the students table which can allow you to have self-relationships to the student table by the Group Name.


          I do not like to use the base tables directly in relationships for several reasons... but basically if you do you are stuck with it and will start to build spiderwebs on the relationship chart. There are many instances required for different purposes... including students, student-group, student-grades, student-tests... then there might be more refined ones like... student-grades-pass or student-grades-fail or such


          Student-tests can be seen through the student-grades relationship.. a portal showing the tests on the other side.... That same relationship can be used from the tests table to look back at the students.


          Sorry this is a bit confused but it is late... Hope it helps anyway...


          - Lyndsay

          1 of 1 people found this helpful
          • 2. Re: Gradesheet project, how can I set relationships properly? - Please, help!

            Simon -


            What Lyndsay is describing is called a "join table". It's a way (probably the preferred way) of implementing a many-to-many relationship. Which is what you have: Each student can be related to many tests, and each test can be related to many students. Hence, you insert a table between them where each record represents the unique combination of Student and Test.


            At a minimum, such a table would contain the unique IDs of the Student and Grade parent record. You can also use a join table record to record information that is unique to that combination (for example, the seat number the student sat in when he took the exam, if such things are important to you).


            Hope that expands a bit on Lyndsay's explanation.



            • 3. Re: Gradesheet project, how can I set relationships properly? - Please, help!

                Most certainly necessary, Mike. I had been battling punctuation in the wee hours and let this distract me. Almost made sense as I wrote it but reviewing it just now shows up all the gaps.

              Mike_Mitchell wrote:


              Hope that expands a bit on Lyndsay's explanation.

              1 of 1 people found this helpful
              • 4. Re: Gradesheet project, how can I set relationships properly? - Please, help!

                Hi Lindsay and Mike,


                Many, many thanks for your response!


                Lindsay, I think you pointed out the main problem on my structure and Mike added the magic words “join table”. Many many thanks to both of you!


                Clearly, as Lindsay said, I needed to break the relationship between tests and students and create a join table to collect the unique records to link Student and Tests, and then their Grades.


                I've been working on the file for a while and now, with just a join table between Students and Tests (called Assignements and containing only its ID, and then ID_Tests and ID_Students)), it works just fine! Just what I needed! Besides, as Mike said, it will be useful to keep information related with the test or student when taking the test, as location, seat, etc.)


                Now I’ve a new layout to create the assignments and I must say that I’m still struggling to find a most simple and intuitive process to create the assignement records (I mean, as simple as the one I already had with the unpopular repeated field, using a checkbox in the Tests Layout)... but this is a total different story now... Any sugestions???


                Once I’ve it finished I’ll upload a copy, just in case anyone else around is in need of a similar gadget.


                Many, many thanks to both for your good advise!

                • 5. Re: Gradesheet project, how can I set relationships properly? - Please, help!



                  I think I should have placed my previous comment here so the web could send you a warning you about it!

                  If I'm wrong, sorry for this extra comment!