The 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

Here is a closer look at the available options for summary fields.

TOTAL OF - Returns the total of a selected numeric field (or calculation field with a numeric result) for all the records in the current found set. This would be used to give you a total of sales in a sales report. There is a check box option called "Running Totals" that will summarize the current total as to move from one record to the next . This is the equivalent of a spreadsheet sum function where 2 + 2 + 2 = 6.

AVERAGE OF - Returns the average amount of a numeric field (or calculation field with a numeric result) for the current found set. This can be used to give you the average sale in a sales report. Say you are doing a report on product sales for the month of July. You have a multiple structured pricing scheme based upon the quantity purchased on one invoice. The average summary field would be great in figuring out what your average profit is per unit by subtracting... well ... should I say it.... the average cost of the product for the month. There is a check box option that is labeled "Weighted Average." This will weigh the calculated result based upon a field that you choose in the scrollable list to the right. Average is the equivalent of (2 + 3 + 4) / 3 = 3

COUNT OF - Counts the number of valid, populated entries in an indicated field. It is important to know that the Count function is intelligent enough to ignore empty fields and will not count them. One popular method of using the Count option is in the sub summary part of a monthly sales report. Here you can get a count of the number of invoices for each month in the found set and using the Total option, you can get the total sales amount. For example for June 1999 (54 sales - $5,000) and for May 1999 (49 sales - $4800).

MINIMUM OF - The smallest value (number, date or time) of a particular field in the found set of records or for the set of records in a sub summary part. Using the same monthly sales report example discussed above where June 1999 (54 sales - $5,000 Smallest Sale Was $10.00) and for May 1999 (49 sales - $4800 Smallest Sale Was $20.00).

MAXIMUM OF - The largest value (number, date or time) of a particular field in the found set of records or for the set of records in a sub summary part. Using the same monthly sales report example discussed above... June 1999 (54 sales - $5,000 Largest Sale Was $500) and for May 1999 (49 sales - $4800 Largest Sale Was $300).

STANDARD DEVIATION FUNCTION - A statistical element that shows how much a field value deviates from one record to another. It has a checkbox labeled "By population" that changes it from a statistic to a population element. A good example of how Standard Deviation might be used is if comparing test scores for different schools, the standard deviation will tell you how diverse the test scores are for each school.

FRACTION OF TOTAL OF - Returns a fractional representation of a value you specify against all the values in a numerical field (or calculation field with a numeric result) for a found set of records.

© 2010 - Dwayne Wright - dwaynewright.com
The material on this document is offered AS IS. FileMaker Pro is the registered trademark of FileMaker Inc.