1 2 Previous Next 17 Replies Latest reply on May 24, 2012 11:00 AM by MichaelWeisenberg

    Using MAX function to obtain highest value in a Summary Field

    MichaelWeisenberg

      Title

      Using MAX function to obtain highest value in a Summary Field

      Post

      All,

      I am using a college gradebook application and have a summary field in one of my tables used to compute a total for student participation credit sorted by (unique) student ID. In my table I also have a separate record for each class date. For the summary field to be accurate, I must return the highest running value in the summary field based upon the latest date. I then use the value in this summary field as part of my final student grade calculation in a calculated field I have placed in a table related by student ID. I am finding that the results of my student grade calculation in my calculated field are not accurate because it is using the initial value from my summary field rather than the more accurate value from the latest date. 

      From my research, I believe I can use the MAX(field) function to properly capture the latest (highest) value from my summary field, but I am unsure as to the syntax when entering into my calculated field formula. My calculated field references the name of the summary field, but I am unsure how to apply the MAX (field) function in my formula. 

      My current calculated field formula reads as follows but is returning the incorrect lower value from my referenced "Total Class Participation Score" summary field. If my logic is correct, I would want to properly use the MAX(field) function in my formula to return the correct latest date (highest) value.

      (((Student Attendance_Class Participation Computation::Total Class Participation Score/270)+  (Interim Quiz #1 Grade * 100) + (Midterm Exam Grade  * 180)+ (Interim Quiz #2 Grade * 100) + (Final Exam Grade  * 200)+ (Oral Presentation Grade * 150))/10 - Student Attendance_Class Participation Computation::Excessive Absenteeism Penalty)/100

      I would appreciate your help in identifying if I am on the right track in my logic described above and, if so, your help in describing how to use the MAX(field) function in a calculated field that references another field. Thanks for your help.

       

        • 1. Re: Using MAX function to obtain highest value in a Summary Field
          philmodjunk

          (
           ((Student Attendance_Class Participation Computation::Total Class Participation Score/270) + 
             (Interim Quiz #1 Grade * 100) +
             (Midterm Exam Grade  * 180) +
             (Interim Quiz #2 Grade * 100) +
             (Final Exam Grade  * 200) +
             (Oral Presentation Grade * 150)
            )/10 -
            Student Attendance_Class Participation Computation::Excessive Absenteeism Penalty
           ) /100

          You have two tables that store the data used in this calculation. The relationship between them will affect the value returned by your Total class Participation Score summary field.

          What relationship do you have between these two tables?

          This summary field will return a total calculated from all related records. A summary field in Student Attendance_Class Participation Computation can also be defined to return the maximum value instead of the total value, but once again, you must have a relationship that matches to the correct set of records in the Student Attendance_Class Participation Computation before this will return the value that you want.

          • 2. Re: Using MAX function to obtain highest value in a Summary Field
            MichaelWeisenberg

            PhilModjunk,

            Thanks for your reply. I have more than 2 tables in my college gradebook FMP application, but the two tables referenced above are joined by a unique Student ID field through a mutual JOIN table which also includes the Student ID field. With regard to the Summary Field I am using, I must use the TOTAL function sorted by Student ID as I am trying to sum the total of each students individual class participation scores by class date (another field in the table). I believe your suggestion to use a Maximum Value summary may work if I apply it to the Total Class Participation Field sorted by each Student ID rather than using the Total of the student's individual participation credit by date field as I am now doing. Thanks... I will try your suggestion to see if it will work for me. Meanwhile, my other alternative would be to apply the MAX (field) function to the calculated formula shown above if I can get the syntax correct. I have some syntax ideas to try...  but if you have a suggestion, I would appreciate your advice. Thanks again.

            • 3. Re: Using MAX function to obtain highest value in a Summary Field
              philmodjunk

              the two tables referenced above are joined by a unique Student ID field through a mutual JOIN table which also includes the Student ID field.

              I read that as:

              Students----JoinTable------Student Attendance_Class Participation Computation

              Students::StudentID = JoinTable::StudentID

              Student Attendance_Class Participation Computation::StudentID = JoinTable::StudentID

              That can't be right as there would be no need for the join table. I must assume that the link from join to Student Attendance_Class Participation Computation is based on another field such as a Class or Session ID. I need to know the full details of the relationship that you have in place here in order to make useful suggestions.

              I must use the TOTAL function sorted by Student ID as I am trying to sum the total of each students individual class participation scores by class date

              I have yet to see how any part of your relationship uses the class date. Unless the class date is part of the relationship so that the only records being totaled have a matching StudentID AND a Matching Date, the total returned will not be the total you want. Sorting--either the records on your layout, in a portal or via a sort option in your relationship will have no effect on the value returned by the summary field.

              And I don't see where you need a "maxiumum" value since your calculation only refers to a summary field that computes a total.

              • 4. Re: Using MAX function to obtain highest value in a Summary Field
                Sorbsbuster

                Michael - I think you are trying to use the 'Max' function to simply get the latest result, as in 'the result from the maximum date', or something.  That can be achieved by sorting the relationship that the data is drawn from in descending date order.  The first record will provide the data, and it will be the latest date.

                However that is only a technical point - you need to fix all the points Phil raises first.

                • 5. Re: Using MAX function to obtain highest value in a Summary Field
                  MichaelWeisenberg

                  Phil, Sorsbuster,

                  Thanks for your responses. FYI, I have attached a JPG file of my latest Gradebook database relationships showing all my current tables. Please note I am using a JOIN table following the earlier recommendation of Sorsbuster because my database makes use of many to many relationships. Being new with FMP, I may have misinterpreted the advice I was given. Note the JOIN table includes fields for Student ID & Class ID. The Student ID & Class ID fields are, in turn, related to my other tables as needed. For now, given the attached structure, my database appears to be functioning OK and giving me the results I need with the exception of the calculation field (calculation formula for Student Final Grade Computation shown earlier in my 1st thread message) in the Student Final Grade Computation Table not showing the MAXIMUM result from the summary field "Total Class Participation Score" found in my "Student Attendance_Class Participation" Table. The reason why I need a MAXIMUM result from this field is that the summary field is summarized by Student to show a running total from the "Student Class Participation Score by Date" field in the same table. The summary is by student, but the running total is a total of all dates. Ultimately, the value I need in my calculated field is the highest (Maximum) value by Student from the running total from that field so that the total reflects the latest date that has been entered. That is why I had the idea to use the MAX(field) function in my calculated field to ensure I am getting this result. I am still playing with this, but as of yet, I do not have the syntax for use of this function down pat.   I would appreciate any advice you may have on the proper use of this function, or any other ideas you may have to achieve the results I describe above. Please let me know of any other questions you may have regarding my intent. Thanks again for all your help.

                  Michael

                  • 6. Re: Using MAX function to obtain highest value in a Summary Field
                    philmodjunk

                    The reason why I need a MAXIMUM result from this field is that the summary field is summarized by Student to show a running total from the "Student Class Participation Score by Date"

                    Aha! seen this one before. Duplicate your summary field and then modify the new copy to remove the running total details. Now you can refer to this new summary field in place of the running total version and you will get the total of the related records--instead of trying to access the maximum entry in the running total.

                    • 7. Re: Using MAX function to obtain highest value in a Summary Field
                      MichaelWeisenberg

                      Hi Phil,

                      Thanks for your reply. I am not sure if this would work for me as I am trying to return a running result from my Summary field sorted by Student AND by Date. So, in my "Student Class Participation Score by Date" field I am placing a score corresponding for each class date (I generate a new record for each student/class date). So, for example, I may enter a score of 6 for Student A on 1/18/12 & then, in a new record, enter a score of 9 for Student A on 1/20. The running total for each student is then entered into my Summary Total Class Partiicpation Score field which, in turn, is used in my calculated Student final grade computation field. The problem is that my calculated Student final grade computation field is picking up either random or 1st entries per student from my referenced Summary Total Class Participation Score field when I would want to return the maximum value entered to date for each student. Unless I misunderstand, if I try your suggestion, & strip out the running total from my duplicated summary field, I only receive the same total number in each of my records, regardless of the student or date. Please let me know if I am misinterpreting your recommendation. Thanks again.

                      Michael

                      • 8. Re: Using MAX function to obtain highest value in a Summary Field
                        philmodjunk

                        The total returned wil be specific to what the relationship controls. As I understand the relationships and "table context" specified for this relationship, it should return the total for the student, not all the records in the table. In your example, do you need the value of 9 or 15 returned? Do you have multiple scores entered for each date or just one for each date for each student?

                        It's really not clear to me what, if any effect the date has on the total you need for your calculation.

                        Is it that you have daily sub totals for a given student of say   10, 15, 99 and 5 and your calculation needs the maximum subtotal--99?

                        If so then the challenge is in producing those subtotals in a calculation field so that you can use either a summary field or the max function to retreive the maximum sub-total.

                        The only way I see to do that is a self join by date and student ID in order to calculate the daily sub total--from which your calc can then get the max value.

                        • 9. Re: Using MAX function to obtain highest value in a Summary Field
                          MichaelWeisenberg

                          Hi Phil,

                          Thanks for your continuing assistance towards solving my problem.

                          To explain further, my "Student Attendance Class Participation" table is returning a separate record for each student per date. I generate 1 record for student A on 1/18 nad then a new record for student A on 1/20. For each of these student/date records I am entering a separate participation score which then gets entered as a running total per student per date into my total summary field. So, in my total summary field, I may have for ech student a separate score for each date which is shown as a running total, as such:

                          Student A

                          1/18 score... 6; 1/20 score... 3 with 9 showing on the 1/20 Student A record in the total summary field

                          Student B

                          1/18 score... 9; 1/20 score... 6 with 15 showing on the 1/20 Student B record in the total summary field

                          During my class year, I enter these participation scores into my table as the semester progresses. At any given time, I would want the highest (Maximum) running total number for each student to enter into my formula used in my final grade calculated field. As it stands, my calculated field from my "Student Final Grade Computation" Table references my summary field but does not display the Maximum Value from my running total per student, per date as described above. FYI, I am repeating my current calculated formula (shown in my first message above)below:

                          (((Student Attendance_Class Participation Computation::Total Class Participation Score/270)+  (Interim Quiz #1 Grade * 100) + (Midterm Exam Grade  * 180)+ (Interim Quiz #2 Grade * 100) + (Final Exam Grade  * 200)+ (Oral Presentation Grade * 150))/10 - Student Attendance_Class Participation Computation::Excessive Absenteeism Penalty)/100

                          If, from your explanation above, the only way I can achieve this is to produce a self join table, I would appreciate your advice how I can do this. Thanks for your help.

                          Michael

                          • 10. Re: Using MAX function to obtain highest value in a Summary Field
                            philmodjunk

                            The self join should not be needed. That would only be necessary if you recorded multiple participation scores for the same student on the same date and then needed to access the largest daily total for that student.

                            From what I can see in your descriptiosn posted here. Using the duplicate summary field with the running total options removed will return the correct values for your calculation. The Relationship will ensure that the total returned is specific to the student.

                            • 11. Re: Using MAX function to obtain highest value in a Summary Field
                              MichaelWeisenberg

                              Hi Phil,

                               

                              I followed your recommendation, but unfortunately, it does not seem to work for me. After duplicating the field, I removed the running total options. Even though my table is sorted by student (for the original summary field), the duplicated summary field returns the same grand running total number for all my records; not specific to each student. Do you have any suggestions to fix this? Looking at my database relationship JPG sent earlier, is my relationships between tables causing this problem? Thanks for your continuing help with my issue.

                               

                              Michael

                              • 12. Re: Using MAX function to obtain highest value in a Summary Field
                                philmodjunk

                                To repeat: any sort order on the table will not have any effect on the value returned by the summary field. (I suspect that this is also why your original summary field was returning strange results for some students.)

                                I do not see any issues in the relationship graph that explain what you are getting, though I'd change the relationship in order to simplify it. I'll keep that for the end of this post.

                                I see these relationships as those used in this computation:

                                Student Final Grade Computation::StudentID = Student_Class Gradebook Join Table::StudentID = Student Attendence Class Participation Computation::StudentID

                                The new summary field is defined in Student Attendence Class Participation Computation. The calculation field (cannot be an auto-entered calculation as it won't properly update.) is defined in Student Final Grade Computation and Student Final Grade Computation is selected in the context drop down at the top of the specify calculation dialog.

                                If all of that matches what you have then the using Student Attendence Class Participation Computation::newSummaryField inplace of the original summary field should enable this calculation to correctly compute the student grade.

                                For these two tables, I'd change the relationship to link from Final Grade Computation directly to Student Attendence Class Participation Computation as you have a one to many relationship between these two tables and thus do not need the join table. (Note, this assumes that a given student never attends two of your classes at the same time and with the same StudentID. If it is, the fact that you do not match by both StudentID AND Class would keep the summary field from returning the correct value.)

                                • 13. Re: Using MAX function to obtain highest value in a Summary Field
                                  MichaelWeisenberg

                                  Hi Phil,

                                  I really appreciate all the assistance you have been providing. While I have experience with Microsoft Access, I am new to using Filemaker Pro and this is the 1st database I have created. 

                                  All my relationships are just as you indicate above. As I stated earlier, when I added your recommended dupllcated summary field with running total removed to my Student Attendence Class Participation Computation table, the results displayed were the same in each record (grand total of all records entered to date) without displaying subtotal results for each student. However, are you implying that if I now point my calculated field in my Student Final Grade Computation table to my newly duplicated summary field it will display the correct results by student in that table? If so, I will try this & let you know if it works. Please confirm if I am on the right track by pointing my calculated final grade computation field to the new duplicated summary field without running total even though the results from that field are not segregated by student in my Student Attendence Class Participation Computation table. 

                                  Thanks again for all your help!

                                  Michael

                                  • 14. Re: Using MAX function to obtain highest value in a Summary Field
                                    philmodjunk

                                    However, are you implying that if I now point my calculated field in my Student Final Grade Computation table to my newly duplicated summary field it will display the correct results by student in that table?

                                    I wouldn't say I am implying that. It's exactly what I have recommended for some time. I have no idea how you are currently viewing the value returned by that summary field. If you are viewing that summary field on a layout based on Student Attendence Class Participation Computation then you will see the total for the current found set (which would be the entire table if you have selected show all records.) But when you put this on a layout based on Student Final Grade Computation (You must see that exact name in "Show Records From" in layout setup...), you should see a subtotal specific to that student's ID.

                                    1 2 Previous Next