8 Replies Latest reply on May 26, 2017 3:31 PM by gswest

    Still having issues trying to get relationships working in Gradebook

    gswest

      So i've come a long way in this database but I still have things I can't figure out. I'm attaching a copy of the offending database here.

       

      Question 1) I need the _fkAssignmentID to populate in the StudentAssignments table when a new record is created but I can't figure out how to do it. As it is when I enter an assignment, I have to go in and manually add the correct ID.

       

      2) I want to be able to chart AVG, MIN, MAX, STD, etc for specific course assignments.

       

      3) The database does, at least at a base level, what I absolutely need at this point. But I want it to be a little more flexible for data analysis. Also, ideally I want to be able to run reports based on a specific class, specific student, or specific assignment.

       

      Any help would be greatly appreciated.

       

      Best,

       

      Geoff

        • 1. Re: Still having issues trying to get relationships working in Gradebook
          StephenWonfor

          Geoff

           

          1.  Does this image match how you visualize your database?  Class Centric?

          2.  Allowing relationships to cascade delete can be a bit of a problem at times.  Accidents happen.  I script all my deletes so that user is aware of what will be happening.  Depending upon the situation I will actually delete the records, at other times I will negativize the foreign key - so "9878" becomes "-9878".  The related record vanishes from the portal but can easily be restored.

          3.  Regarding your question#1 - is the idea that you assign an assignment to the class, then assign that assignment to each student?  If so you could script it like this: (note: that there are many ways to do this - it seems to depend upon what you get used to doing - the favorite hammer syndrome I suppose)

          4.  Copy and Paste in scripts can also be problematic.  Far better to use SetVariable instead of Copy and SetField instead of Paste.  More reliable and also free of context - i.e.. Copy needs to have the field on the layout.

           

          start from the classes layout

          put a button the portal row for the assignments

          instruct the button to do this:

           

          //this is faux code created by just typing - not actually tested in FMP

          //

           

          write the assignment id (Classes--->Assignments) into $id_assignment

          write the class id (Classes--->Assignments) into $id_class

          GoToRelatedRecord Classes-->Students - show only related

          GoToRecord First

          Loop

              SetVariable($Students;List($Students;__pkStudentID))

               GoToRecordNext(ExitAfterLast)

          EndLoop

          GoToLayout - StudentAssignments

          SetVariable($Count;1)

          Loop

               NewRecord

               SetField(StudentAssignments:_fkStudentID;GetValue($Students);$Count)

               SetField(StudentAssignments:_fkClassID;$id_class)

               SetField(StudentAssignments:_fkAssignmentID;$id_assignment)

               SetVariable($Count;$Count + 1)

               ExitLoopIf ($Count > valueCount($Students))

          EndLoop

          GoToLayout(Classes)

          //you might consider a flag on the assignment to indicate it has had this routine run on it - query the user

          //etc.

          GradeBook.png


          Stephen

          1 of 1 people found this helpful
          • 2. Re: Still having issues trying to get relationships working in Gradebook
            erolst

            StephenWonfor wrote:

             

            start from the classes layout

            put a button the portal row for the assignments

            instruct the button to do this:

             

            //this is faux code created by just typing - not actually tested in FMP

            //

             

            write the assignment id (Classes--->Assignments) into $id_assignment

            write the class id (Classes--->Assignments) into $id_class

            GoToRelatedRecord Classes-->Students - show only related

            GoToRecord First

            Loop

            SetVariable($Students;List($Students;__pkStudentID))

            GoToRecordNext(ExitAfterLast)

            EndLoop

            GoToLayout - StudentAssignments

            ...

             

            It seems to me that you can replace the entire part in bold with

             

            # [ while in the Classes context ]

            Set Variable [ $students ; List ( Students::pk ) ]

            • 3. Re: Still having issues trying to get relationships working in Gradebook
              StephenWonfor

              Undoubtedly.  As I mentioned - "favorite hammer". 

              • 4. Re: Still having issues trying to get relationships working in Gradebook
                BruceRobertson

                You are not done with the data model yet.

                You need to add some join tables as discussed by others.

                Attached are some screenshot showing where I have started to make some of those changes.

                Gradebook Tables.png

                Gradebook Graph.png

                • 5. Re: Still having issues trying to get relationships working in Gradebook
                  erolst

                  I know this as just "hammer syndrome" – from "when all you problems look like nails"

                  • 6. Re: Still having issues trying to get relationships working in Gradebook
                    jbrown

                     

                     

                    2) I want to be able to chart AVG, MIN, MAX, STD, etc for specific course assignments.

                     

                     

                    It's my opinion that analysis points like this should be kept in a summary table. An assignment's scores are in the past, so the results will stay static. Maybe one or two scores will be updated, but the bulk is set. So a summary table (one Bruce has called ExamResults, possibly), one field for each analysis point you wish to keep is, as I think, a good way to go. You can write a script to do the following with one assignment:

                    1. Create a record  for the assignment

                    2. Record the assignments ID from the Assignment table

                    3. Find the average score of all the scores and plop that value in a field

                    4. find the MIN score of all the scores, plop that in a field

                    5. Find the Max . . .

                    and so on.

                     

                    This stores the data for charting over time (for a particular teacher if you're also keeping the teacher ID in this table) and allows the graph or report to simply SHOW the point rather than trying to calculate all of them each time.

                     

                    My first database (poorly designed as I now know) was partially a gradebook. I had to figure out quickly that trying to do all these calculations live is a bad way to go.

                    • 7. Re: Still having issues trying to get relationships working in Gradebook
                      gswest

                      Stephen,

                       

                      Yes, this is what I am aiming for - Class Centric. I don't need each particular student to be able to enroll in multiple classes. I know a lot of gradebook type questions/solutions assume this is a requirement but for my purposes I don't need this. I teach 4 to 5 classes a term. I need to track class materials (lectures, notes, etc) as well as student performance and student contact. I would like to be able to set up a class and then as I add students the assignments cascade into the student but it isn't entirely necessary. I made a work-around in my db and as previously stated this led to me not being able to automatically pull the __pkAssignmentID into the StudentAssignment::_fkAssignmentID and I know that creates problems. I'll mess with the scripts later today. Fortunately summer is fast approaching and I'll be able to work on this for several months without having to use process real data.

                       

                      Thanks,

                      Geoff

                      • 8. Re: Still having issues trying to get relationships working in Gradebook
                        gswest

                        If I don't need a single student to be able to enroll in multiple classes then I don't need the ClassRegistrations table right? Each student can only be enrolled in one course but each course can have many students.

                         

                        Geoff