Aggregate Functions

These functions give you the ability to select a group of records from an arbitrary object and produce an aggregate value, without having to write Java code.

Methods	Field Types
SUM(object_name, object_field, criteria_string)	number, currency, boolean
AVG(object_name, object_field, criteria_string)	number, currency, boolean
MAXIMUM(object_name, object_field, criteria_string)	number, currency, date
MINIMUM(object_name, object_field, criteria_string)	number, currency, date
COUNT(object_id, criteria_string)	n/a

where:

• object_id - Object Name or Object Identifier
• object_field - Field in the specified object (other than a Lookup field)

• criteria_string: - A condition that specifies the object records that are part of the collection, where the expression is contained in a string.

Considerations

• Typically, the criteria selects some field in the object record and compares it to a value in the current record. For example, to get total Orders for different sections of the country, you might have a Section object that contains one record for each state. (That arrangement would let you shift a state from one section to another, at will.) You might then use an aggregate function to total orders for each record:

SUM(Orders, order_total, 'customer_state =' + section_state)

You could then write a report to group the records by section name, giving you totals for each section of the country.

• Unlike expressions used elsewhere in the platform, this one is in the form of a string. Therefore:

1. To test a boolean field, concatenate the value to an empty string--because in Java, anything concatenated to a string is converted to a string.

   For example: + boolean_field

2. To include a string literal in a criteria string, use double quotes for the literal, enclosing the whole expression in single quotes.

   For example: 'string_field = "some value" '

• To learn about other operators you can use in expressions, see Filter Expressions in APIs. (But keep the principles above in mind.)

• Criteria must contain at least one index field. If index is combination of fields, the criteria must contain first field in that index. (It does not need to be a unique index, but an index is required, for performance.)

Note: The maximum number of indexes currently allowed in an index definition is 5.

Considerations

• The SUM() of a boolean field gives a count of values that are yes/true.
• The AVG() of a boolean field gives the percentage that are yes/true.

Important: The results do not take into account user-security restrictions. These functions operate on the entire set of records in an Object, regardless of user capabilities.

Availability

The Aggregate Functions are available here:

• Validations
• Default Values
• Formula Fields

They are not available here:

• Layout Rules
• Custom Access Criteria
• Reports