6 Replies Latest reply on Jul 20, 2012 6:10 AM by JAB

# Calculate Min, Max, Sum across several records (rows) from a single field (column)

### Title

Calculate Min, Max, Sum across several records (rows) from a single field (column)

### Post

Hi,

I'm new to FileMaker Pro and am trying to to figure out if there is a way to calculate the minimum of data listed in several records (rows) and in one field (column) without summarizing the data?

For example I have the following data:

Field 1

6

3

5

8

2

Each number is a different record (or row) in under Field 1 in the database.  I want Field 2 to calculate and display the group minium of the five numbers in Field 1 (6, 3, 5, 8, 2).  When I set Field 2's type to calculate and enter "Min(Field 1)" into the Specify Calculation Window FileMaker Pro gives me the following numbers listed in Field 2:

 Field 1 Field 2 6 6 3 3 5 5 8 8 2 2

Basically, FileMaker Pro is finding the mimimum of each record (row) in Field 1 and gives me that answer in Field 2 (i.e., it is treating each record in Field 1 individually not as a group).

I want my database to look like this:

 Field 1 Field 2 6 2 3 2 5 2 8 2 2 2

Again, I would like to do this without using Summary Field Options (I want to keep Field 2 as a calculation field).  Eventually, adding other fields with the maxium and summation of the five numbers listed in Field 1 (i.e., max = 8 and the sum = 24).

Any thoughts?

Thanks!

• ###### 1. Re: Calculate Min, Max, Sum across several records (rows) from a single field (column)

Please explain in more detail why you don't want a summary field. It can do this quite nicely and the getSummary function can access the group's summary values for you.

It's also possible to define a relationship such that aggregate functions, max, min, sum, etc can be used.

• ###### 2. Re: Calculate Min, Max, Sum across several records (rows) from a single field (column)

Long story short...I am trying to group several records together (based on the ID field), find the minimum of those records, and record that value in another field.

For example, assume the following data:

 ID Field 1 A 6 A 3 A 5 B 8 B 2

I want to group all the "A" records together and find and record the minimum value in Field 2 AND I want to group all "B" records together and find and record their minimum value in Field 2.  So the database would look like this:

 ID Field 1 Field 2 A 6 3 A 3 3 A 5 3 B 8 2 B 2 2
• ###### 3. Re: Calculate Min, Max, Sum across several records (rows) from a single field (column)

And a summary field, used with getSummary to access the group based sub totals will do that, so why not use a summary field?

GetSummary ( SummaryField ; ID )

will return the summary sub total for groups produce by sorting on ID, the "break field".

The subtotals depend on your current found set and sort order.

A self join relationship can also be used that does not require any sorting,  but will pull data for the related group from your entire table, so it may or may not be a better option.

• ###### 4. Re: Calculate Min, Max, Sum across several records (rows) from a single field (column)

Hi PhiModJunk,

How do I go about using the GetSummary (SummaryField; ID) function?

I was able to create a Summary Field Type (Field 2), which gives me the following data:

 ID Field 1 Field 2 A 6 2 A 3 2 A 5 2 B 8 2 B 2 2

Then when I go to create Field 3 (as a calculation type field) with the following calculation "GetSummary (Field 2; ID)" nothing populates Field 3 (i.e., Field 3 is left blank).  Should I be doing the GetSummary function somewhere in Field 2?

• ###### 5. Re: Calculate Min, Max, Sum across several records (rows) from a single field (column)

Did you sort your records by the ID field?

• ###### 6. Re: Calculate Min, Max, Sum across several records (rows) from a single field (column)

Got it!  Thank you!