Aggregate Functions

Another important category of functions includes those known as aggregate functions. These include Sum(), Count(), Min(), Max(), and Avg(). These all work in similar, quite intuitive ways. Each operates on a set of inputs (numeric, except for the Count() function) and produce a numeric output. The name of the function implies the operation each performs.

Sum() adds a set of numbers, Min() and Max() return the smallest and largest items of a set, Avg() returns the arithmetic mean of the numbers, and Count() returns the number of non-null values in the set.

The inputs for an aggregate function can come from any one of three sources:

  • A series of delimited values For example, Sum ( 6; 4; 7; 2 ) yields 19. Average ( 6; 4; 7; 2 ) yields 4.75. An interesting use of the Count() function is to determine the number of fields in a record into which a user has entered values. For instance, Count ( FirstName; LastName; Phone; Address; City; State; Zip ) would return 2 if the user had entered values into only those two fields.
  • A repeating field Repeating fields enable you to store multiple values within a single field within the same record. For instance, you might have repeating fields within a music collection database for listing the tracks and times of the contents of a given disc. The functions Count ( Tracks ) and Sum ( Times ) would produce the number of tracks and the total playing time for a given disc.
  • A related field By far, this is the most common application for aggregate functions. Imagine that you have a Customer table and an Invoice table and you want to create a field in Customer that totals up all the invoices for a particular customer. That field would be defined as Sum ( Invoices::InvoiceTotal ). Similarly, to tell how many related invoices a customer had, you could use the formula Count ( Invoices::CustomerID ) .

Note

When using the Count() function to count related records, it usually doesn't matter what field you count, as long as it's not empty. The count will not include records in which the specified field is blank. Typically, you should count either the related primary key or the related foreign key because these by definition should contain data.


Part I: Getting Started with FileMaker 8

FileMaker Overview

Using FileMaker Pro

Defining and Working with Fields

Working with Layouts

Part II: Developing Solutions with FileMaker

Relational Database Design

Working with Multiple Tables

Working with Relationships

Getting Started with Calculations

Getting Started with Scripting

Getting Started with Reporting

Part III: Developer Techniques

Developing for Multiuser Deployment

Implementing Security

Advanced Interface Techniques

Advanced Calculation Techniques

Advanced Scripting Techniques

Advanced Portal Techniques

Debugging and Troubleshooting

Converting Systems from Previous Versions of FileMaker Pro

Part IV: Data Integration and Publishing

Importing Data into FileMaker Pro

Exporting Data from FileMaker

Instant Web Publishing

FileMaker and Web Services

Custom Web Publishing

Part V: Deploying a FileMaker Solution

Deploying and Extending FileMaker

FileMaker Server and Server Advanced

FileMaker Mobile

Documenting Your FileMaker Solutions



Using FileMaker 8
Special Edition Using FileMaker 8
ISBN: 0789735121
EAN: 2147483647
Year: 2007
Pages: 296

Flylib.com © 2008-2020.
If you may any questions please contact us: flylib@qtcs.net