7 Replies Latest reply on Dec 9, 2015 12:23 PM by brian47933

    Help tracking students' exam results

    brian47933

      I am in desperate need of guidance. I'm new to FileMaker.

       

      I want to keep track of students and their exam results.

       

      I have many students who take many exams. So this is a many-to-many relationship. I think I need a join table.

      The data is coming from a collection of Excel worksheets. I have standardized the column names and just collect some information from each worksheet (Speaking, Writing, Listening, Reading, Overall scores). This is a standardized test which is given multiple times. A student can take it one or more times.

       

      I am confused how to set this up in FileMaker Pro 13.

       

      I have a students table. On the student detail layout I have a tabbed window. I want the above results to appear on the tab. This should be done with a portal, pulling the data from the join table (I think).

       

      Am I going in the right direction? I'd like to get this done as soon as possible. I have another round of exam results coming up.

       

      Thank you in advance for your time.

        • 1. Re: Help tracking students' exam results
          brian47933

          I guess we're going to hire the work done or continue using multiple Excel spreadsheets.

           

          Thanks anyway.

          • 2. Re: Help tracking students' exam results
            user19752

            You didn't get reply with posting in bad place, it seems this "New User Group" is nearly dead since "This group is members only", and many of experienced users don't become member, so can't reply. Please try "Discussions" place.

             

            You are in the right direction, only need to go.

            • 3. Re: Help tracking students' exam results
              mark_baum

              Hi Brian,

               

              My apologies for not responding to this sooner. I'm the only one staffing this area right now and am caught up by a number of urgent projects. Until my team grows, I'd agree with user19752 -- you'll get more timely help from the entire community in the Discussions area, so I'll move this thread there now.

               

              I think we're talking about these entities:

               

              Students

              Exams (describing the standardized exam -- probably with an associated table of Questions, or with the questions hard-coded in as fields)

              Exam Instances as taken by a given student on a given date

               

              One possibility of the relationships might be:

               

              STUDENTS ---  joined by StudentID and Date -- EXAMINSTANCES -- joined by ExamID -- EXAMS

               

              On your Student Detail layout, you can include EXAMS data because it's visible (at least in theory) through the chain of relationships.

               

              Is this making sense as a first structural step? As you suggested, EXAMINSTANCES is a join table.

               

              Cheers --

               

              Mark

              • 4. Re: Help tracking students' exam results
                jbrown

                HI Brian.

                I've set up many school databases with test scores and such. I'd be happy to help. Feel free to drop me a PM or an email regarding further questions.

                I'm sorry I didn't respond sooner. I love working with school databases!

                 

                Thanks

                • 5. Re: Help tracking students' exam results
                  brian47933

                  Mark,

                  Thank you for your reply. I'm still not clear on what is needed.

                  I have a table with students and a table with exams.

                  The exams I want to track occur three times a year and are very standardized. A student could take the exam once or multiple times. The data will come from an Excel sheet which I want to import into FM after the exam.

                  The Exams table has the student ID, the name of the exam (for example we're using the date the exam was given i.e., 1516-01-SEP-25), Speaking, Writing, and Overall scores. (In the Exams table each record would have a unique ExamID. In the record we would have a StudentID, exam name, and results.)

                   

                  On the student layout I want to use a portal that will show the exam name and some of the scores. Ideally I will have the information in the portal sorted on the exam name with the most current exam results showing first. (sort on exam name).

                   

                  Is the key to this:

                   

                  STUDENTS (StudentID) --> EXAMINSTANCES {join table} ExamInstanceID, StudentID foreign key, ExamID foreign key <-- EXAMS (ExamID)

                   

                  I'm unsure about what to do with the EXAMINSTANCES join table. Does it just reside in the solution? Meaning I don't use it on the student layout or in portals?

                   

                  I *think* I have solved the relationship. I'm still unsure if I did it correctly. I don't know for sure if I should allow creations of records in the relationships in the Edit Relationship option.

                   

                  I've included screenshots. If you could give me feedback or suggestions I'd appreciate it.

                  Screen Shot 2015-11-19 at 7.41.09 AM.png

                   

                  Screen Shot 2015-11-19 at 7.41.57 AM.png

                   

                  Screen Shot 2015-11-19 at 7.42.14 AM.png

                  The above image is exactly what I need. Some students will take the same exam many times others just once. But many students will take the same exam on the same day.

                   

                  I hope this helps explain what I'm trying to do.

                   

                  Thanks for your help.

                  • 6. Re: Help tracking students' exam results
                    realgrouchy

                    You've marked the question as Answered but it sounds like you're still looking for help/advice...

                     

                    It looks to me like you have the relationships set up properly.

                     

                    You would want to have certain layouts:

                    - The Exams layout is where you can describe the details of each exam (e.g. date/location/time). You would want to set the ExamID field to be unique in the Exams table.

                    - The Students layout is where you describe the details of each student (e.g. student ID/name/contact info/program). You would want to set the StudentID field to be unique in the Students table.

                    - The ExamInstances table is not necessary as a standard layout, but it would have one entry per student per exam (e.g. EXAM01 STUD01, EXAM01 STUD02, EXAM02 STUD03, etc.). The ExamInstances table should NOT set the ExamID or StudentID fields to be unique, since the same student may appear in multiple entries, and the same exam will appear in multiple entries. If your entries in the Exams (sic) table refer to a type of exam (e.g. the Psych 101 exam) as opposed to a specific instance of the exam (e.g. the Psych 101 exam that will be held on Dec 12, 2015 in the main Gymnasium), then you would also want the ExamInstances table to have fields for date, grade, etc. so that you can then distinguish between a particular student's different attempts.

                     

                    In either or both of the Exams and Students layout, you would want a portal with data from the ExamInstances table

                     

                    You may still want to have an ExamInstances layout, but only for the purposes of importing and exporting data from the ExamInstances table (e.g. if you have a spreadsheet of students taking a particular exam).

                     

                    In addition, you'd want to set the field validation for StudentID and ExamID in the ExamInstances table to be restricted to a value list of existing Students and Exams. Go to File > Manage > Value Lists and create a value list for each of these, and point it to the respective field in the respective table. Then go to your field definitions and in the ExamInstances table set validation for the StudentID field and ExamID field to point to these value lists. While strictly speaking this isn't necessary, if you don't then it could result in ExamInstances having orphaned entries that don't correspond to a valid Student entry or Exam entry.


                    Hope this helps.


                    - RG>

                    • 7. Re: Help tracking students' exam results
                      brian47933

                      Thank you very much for the explanation. I managed to get the solution working by following your example. It's a great feeling to see it all come together.

                       

                      If I set the validation for Student ID in the ExamInstances table, is the validation automatically updated when I add new students to the Students table?

                       

                      Also, I have the ExamInstances Join Table layout created. Say I import the join table information before the exam but leave a field empty (maybe final score). I then want to update the ExamInstances from an Excel sheet. I know to map the fields on import but confused about how to update the found set. Also what if I want to add the final score and create a few new records on the same import. Is that possible?