1 Reply Latest reply on May 25, 2012 11:57 AM by philmodjunk

    Max() returns value in found set, of currently shown record, not maximum value in set

    shi_guy

      Summary

      Max() returns value in found set, of currently shown record, not maximum value in set

      Product

      FileMaker Pro

      Version

      12

      Operating system version

      Windows 7

      Description of the issue

      I am doing a Max() calculation on a set of data, and Max() only returns the value that is currently displayed, NOT the Maximum value in the data set. If you scroll through the records while viewing the Max() calculation in the data viewer, it changes as each record is displayed. Data is 'number'

      Steps to reproduce the problem

      Look at Data Viewer and Max(number field).  This does not show max value, as you scroll through records, this value for Max changes... it should not.

      Expected result

      Maximum value in found set

      Actual result

      Value of selected record

      Workaround

      None found, had to write a script with more steps to produce the desired result.  In a large database, this could be a speed issue.

        • 1. Re: Max() returns value in found set, of currently shown record, not maximum value in set
          philmodjunk

          This is true of all aggregate functions used with that syntax. Aggregate functions use one of the following to compute a value from a set of fields--not the found set:

          Max ( field1 ; field2 ; field3 ) --- returns max value of values stored in these fields for the current record

          Max ( repeatingfield ) --- returns max value of all values stored in the repetitions of this field

          Max ( Relatedtable::Field ) --- returns max value of Field from all records related to the current record

          To get an aggregate value based on a found set, you would need to use a summary field in place of the calcualtion using an aggregate function.