4 Replies Latest reply on Nov 21, 2013 3:12 PM by greenlion






           I am setting up a transcript layout for students to show their accreditation record.  I have a Student table and a Class Registration child table.  I also have a Courses table with Classes child table and the Class Registration child table.  I need to show the Courses that have a pass grade from the corresponding Class Registration.  I also need to show only the highest mark for each Course if they have taken it more than once.  I am having trouble because there is no Course primary key on the Course Registration, as the Course primary key is on the Classes table.

           Could someone please give me their recommendations on how to go about this.  Thanks.

        • 1. Re: Calculation

               Before we can set up a calculation we have to make sure the relationships are correct and in place. Is this what you have?

               Student----<ClassRegistration>----Classes>----Courses   (----< means one to many)

               And are your grades entered into a field in ClassRegistration?

          • 2. Re: Calculation

                 That's right.  The grades are entered into a field in ClassRegistration as well yes.

            • 3. Re: Calculation

                   This looks like something best done with ExecuteSQL if one can parse through all the needed SQL without getting a syntax error result, but....

                   Add an additional occurrence of ClassRegistration and Classes  to your relationships:


                   Courses::__pkCourseID = Classes|MaxGrade::_fkCourseID

                   Classes|MaxGrade::__pkClassID = ClassRegistration|MaxGrade::_fkClassID AND
                   Classes|MaxGrade::gStudentID = ClassRegistration|MaxGrade::_fkStudentID    (gStudentID is a global field set by ONRecordLoad triggered script.)

                   Define an unstored calculation field, cGrade in Classes to copy over the Grade from ClassRegistration|MaxGrade, be sure to select Classes|MaxGrade in the context drop down in the specify calculation dialog box.

                   In the relationship between Courses and Classes|MaxGrade, specify a sort order for Classes|MaxGrade that sorts the records by cGrade. If the grade is the traditional ABCDF type grade, choose ascending order so that the record with the best grade in Classes|MaxGrade is the first related record.

                   Now you can put a portal to Courses on your Students Layout and include Classes|MaxGrade::cGrade in the portal row to show the best grade earned for each course. You can use a portal filter to omit failing grades from this portal.

                   Whew! that's my first cut at using a non-ExecuteSQL method to get those results...


              • 4. Re: Calculation

                     Brilliant.  I really battled with this, being a little green still to FM.  The grades are actually %'s and because each course has a different passing grade, I also created a cPass unstored calculation field in Classes to copy over the Pass from ClassRegistration|MaxGrade (a pass or fail field) so that I was able to filter the portal like you mentioned with only the passing grades.  Thanks a lot, I really appreciate your help.