Count()

Category: Aggregate

Syntax: Count ( field {; field...} )

Parameters:

fieldAny related field, repeating field, or set of non-repeating fields; or an expression that returns a field, repeating field, or set of non-repeating fields. Parameters in curly braces { } are optional and may be repeated as needed, separated by a semicolon.

Data type returned: Number

Description:

Returns a count of the fields (or repetitions, in the case of repeating fields) in the parameter list that contain non-blank values.

When the parameter list consists of two or more repeating fields, Count() returns a repeating field in which the corresponding repetitions from the specified fields are counted separately. So if a field Repeater1 has three values, 16, 20, and 24, and another field, Repeater2, has two values, 14 and 25, Count (Repeater1; Repeater2) would return a repeating field with values 2, 2, and 1.

Examples:

Function	Results
Count ( field1; field2; field3 )	Returns 2 when field1 and field2 contain valid values, and field3 is empty.
Count ( repeatingField )	Returns 2 when repetitions 1 and 2 contain valid values, and repetition 3 is empty.
Count ( InvoiceItem::InvoiceID )	Returns 2 when the current record is related to two InvoiceItem records. When using the Count() function to count the number of related records, be sure to count a field that is guaranteed not to be blank, such as the table's primary key.

FileMaker 8 modifies the Count() function to take portal and field context into account. For example, in a scenario where a Customer table occurrence is related one-to-many with an Invoice table occurrence that is then related one-to-many to a LineItem table occurrence, evaluating a Count() function from Customer to LineItem will yield all LineItem records for the current Customer record if the user's context is on the Customer TO; but if the user's context is on the Invoice TO (if a user clicks in an Invoice portal row, say, or a script navigates into the portal using a Go To Field script step), Count(LineItem::field) will return a count of just those line items related to the currently selected invoice. Given that calculation fields explicitly specify their evaluation context, this issue is most likely to arise in scripting.