3 Replies Latest reply on Nov 17, 2015 7:29 PM by macwombat

    Creating a Class Grade Scale


      Hi guys,


      Bewarned, I am a noob at this, I've figured out how to do some of this myself but am really working at a rudimentary level when working with formulas and calculations.  I've figured out how to do a lookup to match values to create an output, in my case the quarter total and a semester grade.  I've made a separate table from my main table and have created a relationship between the points a student has earned and a value that I have put in that corresponds to a letter grade (A, A-, B+, etc).


      My solution was to make a record for every value and in a grade field link that value with a letter grade.  My solution is that I have to have to manually input every single value as a separate record and a corresponding grade.  I've got two fields, QTR total and QTR grade.  I have to make for example a record with a QTR total value of 100 and a QTR grade of A, I have to make a separate record for QTR total value 99 and a QTR grade of A.


      What I am looking to do and can't figure out is how to streamline this so that values between 100-91 will yield an A, values between 90-81 will yield an A- etc.


      Any suggestions?



        • 1. Re: Creating a Class Grade Scale

          If your grade cutoffs are not going to change between reporting periods or for different subjects then you could hard code the QTR grade as a calculated field with the following calc:


          Case (

          Qtr total > 90 ; "A" ;

          Qtr total > 80 ; "B" ;

          etc )

          1 of 1 people found this helpful
          • 2. Re: Creating a Class Grade Scale



            Hadn't thought of that.  Is there a way to use a table?  I'd like to be able to change it if need be, though I could just go back into the calculation to do it I suppose.


            There's no easy way to create a field that is a range of values is there?

            • 3. Re: Creating a Class Grade Scale

              I'd like to be able to change it if need be,


              That's a key point.  Here's a few thoughts.

              1.  Are you keeping a new record for each student for each reporting period?  Important for historical data.

              2.  Create records in your grade values table with 4 fields - grade descriptor eg. "A", grade upper value, grade lower value and a reporting period ID (to distinguish these grade values from ones you might use in Qtr 1 2016).

              3.  Make sure all your student reporting records have the reporting period ID.

              2015-11-18 14_28_20-FileMaker Pro Advanced.png


              4.  Create a relationship to the grade values table based on the reporting period, qtr total and the upper and lower grade values (see screenshot)

              2015-11-18 14_25_56-Edit Relationship.png

              5.  Make your Qtr Grade a calc to the grade field on the grades value list.

              2015-11-18 14_27_19-Manage Database for “test1”.png

              Hope this helps.