Difference between revisions of "Aggregate Functions"

From AgileApps Support Wiki
imported>Aeric
imported>Aeric
 
(3 intermediate revisions by the same user not shown)
Line 3: Line 3:
====About the 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 value can be sum of values, an average, maximum, or minimum value, or it can be a simple count of records.
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 value can be sum of values, an average, maximum, or minimum value, or it can be a simple count of records.
{{Tip|A [[Rollup Summary Field]] is often more useful than an aggregate function. With a Rollup Summary Field you define the same kinds of summary calculations for a set of ''related'' records--for example, to sum the line items in an order--and store that value as a field in the record, where it is kept up to date whenever the set of related records is modified.}}


The aggregate functions are available for use in:
The aggregate functions are available for use in:
Line 14: Line 16:


They are not available for use in other contexts in which a [[Formula Expression]] may be created.
They are not available for use in other contexts in which a [[Formula Expression]] may be created.
{{Tip|A [[Rollup Summary Field]] is often more useful than an aggregate function. With a Rollup Summary Field you define the same kinds of summary calculation for a set of ''related'' records--for example, to sum the line items in an order--and store that value as a field in the record, where it is kept up to date whenever the set of related records is modified.}}


<span id="SUM"></span> <span id="AVG"></span>  
<span id="SUM"></span> <span id="AVG"></span>  
Line 28: Line 28:
| <tt>AVG('objectName', 'fieldName', 'criteria_string')</tt> || number, currency, boolean
| <tt>AVG('objectName', 'fieldName', 'criteria_string')</tt> || number, currency, boolean
|-
|-
| <tt>MAXIMUM('objectName', 'fieldName', 'criteria_string')</tt> || number, currency, date
| <tt>MAXIMUM('objectName', 'fieldName', 'criteria_string')</tt> || number, currency
|-
|-
| <tt>MINIMUM('objectName', 'fieldName', 'criteria_string')</tt> || number, currency, date
| <tt>MINIMUM('objectName', 'fieldName', 'criteria_string')</tt> || number, currency
|-
|-
| <tt>COUNT('objectName', 'criteria_string')</tt> || n/a
| <tt>COUNT('objectName', 'criteria_string')</tt> || n/a
Line 61: Line 61:
:#:For example: <tt><nowiki>'' + boolean_field</nowiki></tt>
:#:For example: <tt><nowiki>'' + boolean_field</nowiki></tt>
:#:or: <tt>boolean_field + ' AND customer_state = ' + section_state</tt>
:#:or: <tt>boolean_field + ' AND customer_state = ' + section_state</tt>
:# To include a string literal in a criteria string, use double quotes for the literal, and enclose the whole expression in single quotes.
:# To include a string literal in a criteria string, use two single-quotes for the literal, and enclose the whole expression in single quotes. (The doubling is an ''escape'' that produces a single quote within the string.)
:#:For example: <tt>'string_field = "some value" AND customer_state = ' + section_state</tt>
:#:For example: <tt><nowiki>'string_field = ''some value'' AND customer_state = ' + section_state</nowiki></tt>
::: To learn about other operators you can use in expressions, see [[Common:Filter Expressions in APIs|Filter Expressions in APIs]].<br>(But keep the principles above in mind.)
::: To learn about other operators you can use in expressions, see [[Common:Filter Expressions in APIs|Filter Expressions in APIs]].<br>(But keep the principles above in mind.)

Latest revision as of 10:06, 24 October 2019

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 value can be sum of values, an average, maximum, or minimum value, or it can be a simple count of records.

Thumbsup.gif

Tip: A Rollup Summary Field is often more useful than an aggregate function. With a Rollup Summary Field you define the same kinds of summary calculations for a set of related records--for example, to sum the line items in an order--and store that value as a field in the record, where it is kept up to date whenever the set of related records is modified.

The aggregate functions are available for use in:

They are not available for use in other contexts in which a Formula Expression may be created.

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
MINIMUM('objectName', 'fieldName', 'criteria_string') number, currency
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 two single-quotes for the literal, and enclose the whole expression in single quotes. (The doubling is an escape that produces a single quote within the string.)
    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.)