From Dwayne Wright PMP
Certified FileMaker Developer
WEB: www.dwaynewright.com
EMAIL: info@dwaynewright.com
TWITTER: dwaynewright
YOUTUBE: FileMakerThoughts
Please Note: If you are viewing this page in a news feeder, the images may get munged up a bit. For the best experience, please visit the journal directly by clicking (here).
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, the delay in recalculating the summary field will grow also.
One thing which is often confusing to new users is a summary field calculates on the current found set or the grouping 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. It will look something like this....
2003 Sales Total $150,000 << Sum of Sales field
----------------------------------
June Sales Total $15,000 << Sum of Sales field
----------------------------------
Dwayne Sales Total $10,000 << Sum of Sales field
Edward Sales Total $5,000 << Sum of Sales field
----------------------------------
July Sales Total $20,000 << Sum of Sales field
----------------------------------
Dwayne Sales Total $1,000 << Sum of Sales field
Edward Sales Total $19,000 << Sum of Sales field
As you can see, the field has a different total in each sub-summary part but it is the same field on the layout. Again, you will have to sort the database by year, month and sales person before the totals will show. The reason the data needs to be sorted is so it can be grouped accordingly with the sub-summary parts. It should also be noted that summary fields in sub-summary parts can only be viewed on the printed page or in FileMaker Preview mode.
Quick Note: For reports with multiple sub-summaries, you have to make sure your sort order matches when you run the report. The best way to ensure the sort order is correct before printing a report is to automate the process with a script. This way, you don't have users trying to memorize complex sort orders for reports. Also, make sure none of your layout elements extend across two or more layout parts. If you do, chances are it won't appear at all or make your report look real goofy.
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 Of
The following discusses these in more detail...
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 check box 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.
=
More info about the author and FileMaker in general, contact me at info@dwaynewright.com.
© 2008 - 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
Tuesday, May 13, 2008 at 5:32AM 
My good friend Chris Kubica at Application Architects, LLC has released another one of their free reports comparing third party products for a particular purpose. This report reviews the many great FileMaker-based solutions and plug-ins you can use to easily add graphs and charts to your databases. You can check it out at http://www.applicationarch.com/compare.htm . 
