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?
That's right. The grades are entered into a field in ClassRegistration as well yes.
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...
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.