FileMaker Summary Fields

From Dwayne Wright PMP, PMI-ACP, CSM
Certified FileMaker Developer

WEB: www.dwaynewright.com
EMAIL: info@dwaynewright.com
TWITTER: dwaynewright
YOUTUBE: FileMakerThoughts

Summary fields are normally used to return a numerical result for records in a current found set or that fall within a sub summary part of a layout. Some examples would be monthly sales, averages of profit per product and other such numerical decision making aids. Summary fields are one of the eight possible choices in the Define Fields dialog box. Just like calculation fields, you cannot enter data into a summary field while in browse mode.

Do not be misled into thinking that summary fields have to be about numbers only. You can summarize numerical data from text, date, time and even container fields with the COUNT OF, MAXIMUM OF or MINIMUM OF options. The summarized total can be affected at any moment and shown live. Typically, it's not a good idea to put a summary field on a data entry screen because the data entry screen may be tied up doing a recalculation on a summary field. You then have to wait to do your work until the calculation is complete. This isn't too bad on moderately sized databases but as the database grows in overall size of records, the delay in recalculating the summary field will grow also.

One thing which is often confusing to new users is a Summary field that calculates on on the current found set or on the set that applies to a sub summary part. This means you have to take into consideration the "found set" when you have summary fields in reports that run from scripts. Say, for instance, you have a summary field for sales named "Sum of Sales." It's the sum of a field called Subtotal. We used the Subtotal field because we don't want tax or shipping amounts to show in our sales number. If you perform a find for sales in 1998, this field will show the sum total for those records. If you search for June of 1998, it will only result in the total for those records.

Working with three sub summary layout parts on a layout, the first shows a sub summary when sorted by year, the second by month and the third by salesperson. When you put the same summary field, in this example, "Sum of Sales" in each part, you get three different answers (after you do the correct sort operation, of course). The first time the summary fields appears (in the part sorted by year), it will give you the annual totals, the second time by each month and finally by each sales person for that month.

There are seven different types of summary fields, which are:

Total Of
Average Of
Count Of
Minimum Of
Maximum Of
Standard Deviation Of
Fraction of Total

=
More info about the author and FileMaker in general, contact me at info@dwaynewright.com.

© 2009 - Dwayne Wright - dwaynewright.com

The material on this document is offered AS IS. There is NO REPRESENTATION OR WARRANTY, expressed or implied, nor does any other contributor to this document. WARRANTIES OF MERCHANT ABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE EXPRESSLY DISCLAIMED. Consequential and incidental damages are expressly excluded. FileMaker Pro is the registered trademark of FileMaker Inc.