Aggregate Functions

About the Aggregate Functions

Thee aggregate functions let you select a group of records from an arbitrary object and produce an aggregate value from them, without having to write Java code.

The aggregate functions are available for use in:

• Default Field Values
• External Lookup#Lookup Service Configuration
• External Lookup#Search Service Configuration
• Layout Rule Conditions
• Layout Rule assign-value-to-field action
• Formula Fields
• Validation Criteria

They are not available for use in:

• Computed Fields in Reports
• Custom Access Criteria
• Macros#Visibility Criteria
• Rules#Execution Criteria
• Rule Actions to add or update a record

Tip: (For most purposes, a Rollup Summary Field tends to be more useful, as it lets you define a summary calculation for a set of related records--for example, to sum the line items in an order.

The Aggregate Functions: SUM, AVG, MAXIMUM, MINIMUM, COUNT

Methods	Field Types
SUM('objectName', 'fieldName', 'criteria_string')	number, currency, boolean
AVG('objectName', 'fieldName', 'criteria_string')	number, currency, boolean
MAXIMUM('objectName', 'fieldName', 'criteria_string')	number, currency, date
MINIMUM('objectName', 'fieldName', 'criteria_string')	number, currency, date
COUNT('objectName', 'criteria_string')	n/a

where:

• 'objectName'

A string containing the object name (not its display label) or the object ID.

For example: 'Some_Object'

• 'fieldName'

A string containing the name of a field in the specified object.

Must be the field name, not its display label. For example: 'some_field'.

Must be a simple field of type number, currency, or boolean.

Does not work for formula fields, text fields, Lookups, or any other kind of field.

• 'criteria_string'

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

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('Order_Items', 'item_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.

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.

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.
• The criteria expression must contain at least one index field.
• If the index is a combination of fields, the criteria expression must contain the first field in that index. (It does not need to be a unique index, but an index is required, for performance.)

Note: An object can have up to five indexes.

• Unlike criteria specified elsewhere in the platform, the criteria expression provided here is in the form of a string.
  Therefore:

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

   For example: '' + boolean_field

   or: boolean_field + ' AND customer_state = ' + section_state

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

   For example: 'string_field = "some value" AND customer_state = ' + section_state

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