3 Replies Latest reply on Mar 6, 2017 10:05 AM by fmpdude

    Max function


      Seem to be having trouble with the MAX function


      I have a table SHIFT INFORMATION and a field CONTAINER NUMBER, the container number field will vary in sequence.

      Here are three examples: I'm looking for the highest number, the example below would yield 4, 30 or 7 respectively.

      All fields are number fields or are set to result in a number and contain nothing but numbers.

      Container Number

      1          1          1    

      2          10        3 

      3          20        5

      4          30        7


      I created a calculation field using MAX(Container Number) in the same table... I get nothing?


      Seems too straight forward for me to screw up?

        • 1. Re: Max function

          the aggregate functions apply differently that you would expect when used on fields/records in same table

          Aggregate functions perform statistical analysis on numbers (and also dates or times for some functions) in:


          several fields in a record.


          related fields whether displayed in a portal or not.


          repeating fields.

          Aggregate functions

          • 2. Re: Max function

            If you need the max over a found set. Try a summary field.

            • 3. Re: Max function

              This is confusing as the aggregate functions don't work as you would expect by default. In an Excel spreadsheet, the SUM works on that data right there. But in FMP, it doesn't. It works with related data and other. Just one thing to learn.


              Hopefully, they'll fix that counter-intuitiveness in some future version.


              As philmodjunk said, a summary field is the way to go.


              A summary field is really cool since in a report, for example, the field will give you different (but correct) amounts depending on where you put it. So, you could create a single COUNT summary field. If you put the field in the header, it will give you the count of all the records. But, if you copy and paste that same summary field and put it into a detail band, it will give you the count of just those shown records.


              That's pretty cool!