1 Reply Latest reply on Apr 4, 2011 10:26 AM by philmodjunk

    Summing value X in all records based on value Y



      Summing value X in all records based on value Y



      I have a database for my classroom and a table that collects and calculates each student's grade. However, this table is for all my classes that I teach.

      What I want to do is the following: for each student, I want to calculate their grade and the average grade of their SPECIFIC class so I can graph their performance and compare it to the class' performance.

      Now I can easily calculate each student's average grade. Only problem is writing a script that (1) gets the records for all the students in that specific class, and (2) find the overall average of the grade for the class.

      Here is what I currently have:

      Set Variable [$$TotalGrades; Value:0]

      Go to Layout ["Students" (Students)]

      Set Variable [$$Classroom; Value: Students::Class]

      Go to Record/Request/Page[First]


        If[Students::Class = $$Classroom]

          Go to Field [ Students::Grade]

          Set Variable [ $$TotalGrades = $$TotalGrades + Students::Grade]

        End If

        Go to Record/Request/Page[Next;Exit after last]

      End Loop

      Set Filed [Students::Average Grade; $$TotalGrades/ Get(TotalRecordCount)]

      Can someone please tell me what I'm doing wrong? Also another thing to note is that the field 'Average Grade' is in a different layout of the same table, does that change anything?

      Thanks in advance! I really need the help :(

        • 1. Re: Summing value X in all records based on value Y

          Note, there are many errors in your script. Rather than try to fix that script, let's use a method that does not require any script at all except maybe to perform a find and sort to automate setting up your reports.

          Try using summary fields to compute totals, averages for all the records in a found set and for sub groups within them.

          Define this field to compute the class average:

          sClassAverage, define it as a field of type Summary that computes the Average Grade

          If you perform a find for all records of a given classroom, sClassAverage will compute the average grade for that class. If you create a list view layout with a sub Summary part "when sorted by Class". You can put sClassAverage in this sub summary part to display all the class averages for all your classes in the same report.

          See this tutorial on creating summary reports:

          Creating Filemaker Pro summary reports--Tutorial