Difference between revisions of "Aggregate Functions"

From AgileApps Support Wiki
imported>Aeric
imported>Aeric
Line 20: Line 20:
|}
|}
where:
where:
:* ''''objectName' -''' A string containing the object name (not its display label) or the object ID. For example: <tt>'Some_Object'</tt>
:* ''''objectName' '''  
:* ''''fieldName' -''' A string containing the name of a field in the specified object.
:: A string containing the object name (not its display label) or the object ID. For example: <tt>'Some_Object'</tt>
:* ''''fieldName' '''
:: A string containing the name of a field in the specified object.
:: Must be the field name, not its display label. For example: <tt>'some_field'</tt>.
:: Must be the field name, not its display label. For example: <tt>'some_field'</tt>.
:: Must be a simple field of type number, currency, or boolean.
:: Must be a simple field of type number, currency, or boolean.

Revision as of 02:41, 8 May 2014

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

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.

Warn.png

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.)

Availability

The Aggregate Functions are available here:

  • Validations
  • Default Values
  • Formula Fields

They are not available here:

  • Layout Rules
  • Custom Access Criteria
  • Reports