Difference between revisions of "Aggregate Functions"

From AgileApps Support Wiki
imported>Aeric
imported>Aeric
 
(27 intermediate revisions by the same user not shown)
Line 1: Line 1:
<noinclude>__NOINDEX__</noinclude>
<noinclude>__NOINDEX__</noinclude>
These functions give you the ability to select a group of records from an arbitrary object and
__TOC__
produce an aggregate value, without having to write Java code.
====About the Aggregate Functions====
<span id="SUM"> </span> <span id="AVG"> </span>  
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.
<span id="MAX"> </span> <span id="MIN"> </span>  
 
<span id="COUNT"> </span>
{{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:
:* [[Default Field Values]]
:* [[External Lookup#Lookup Service Configuration]]
:* [[External Lookup#Search Service Configuration]]
:* [[Layout Rules#Conditions and Actions|Layout Rule Conditions]]
:* [[Layout Rules#Actions|Layout Rule assign-value-to-field action]]
:* [[Formula Fields]]
:* [[Validations|Validation Criteria]]
 
They are not available for use in other contexts in which a [[Formula Expression]] may be created.
 
<span id="SUM"></span> <span id="AVG"></span>  
<span id="MAX"></span> <span id="MIN"></span> <span id="COUNT"></span>
 
====The Aggregate Functions: SUM, AVG, MAXIMUM, MINIMUM, COUNT====
:{| border="1" cellpadding="5" cellspacing="1"
:{| border="1" cellpadding="5" cellspacing="1"
! Methods !! Field Types
! Methods !! Field Types
|-
|-
| SUM(object_name, object_field, criteria) || number, currency, boolean
| <tt>SUM('objectName', 'fieldName', 'criteria_string')</tt> || number, currency, boolean
|-
|-
| AVG(object_name, object_field, criteria) || number, currency, boolean
| <tt>AVG('objectName', 'fieldName', 'criteria_string')</tt> || number, currency, boolean
|-
|-
| MAXIMUM(object_name, object_field, criteria) || number, currency, date
| <tt>MAXIMUM('objectName', 'fieldName', 'criteria_string')</tt> || number, currency
|-
|-
| MINIMUM(object_name, object_field, criteria) || number, currency, date
| <tt>MINIMUM('objectName', 'fieldName', 'criteria_string')</tt> || number, currency
|-
|-
| COUNT(object_id, criteria) || n/a
| <tt>COUNT('objectName', 'criteria_string')</tt> || n/a
|}
|}
where:
where:
:* '''object_id -''' Object Name or Object Identifier
:* ''''objectName' '''
:* '''object_field -''' Field in the specified object (other than a Lookup field)
::: A string containing the object name (not its display label) or the object ID.
 
::: For example: <tt>'Some_Object'</tt>
:* '''criteria: -''' A condition that specifies the object records that are part of the collection.  
:* ''''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 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:
::: 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:
::::<tt>SUM(Orders, order_total, 'customer_state =' + section_state)</tt>  
:::: <tt>SUM('Order_Items', 'item_total', 'customer_state =' + section_state)</tt>  
::: You could then write a report to group the records by section name, giving you totals for each section of the country.  
::: You could then write a report to group the records by section name, giving you totals for each section of the country.  
:::''Learn more:'' [[Common:Filter Expressions in APIs|Filter Expressions in APIs]]


::* 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.)
{{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.}}
:::''Note:'' The maximum number of indexes currently allowed in an index definition is 5.


;Considerations:
====Considerations====
:* The SUM() of a boolean field gives a count of values that are yes/true.
:* 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 AVG() of a boolean field gives the percentage that are yes/true.
 
:* The criteria expression must contain at least one index field.  
{{Important|The results do not take into account user-security restrictions. These functions operate on
:* 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.)
the entire set of records in an [[Object]], regardless of user capabilities.}}
::''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.<br>Therefore:
;Availability:
:# 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: <tt><nowiki>'' + boolean_field</nowiki></tt>
The Aggregate Functions are available here:
:#:or: <tt>boolean_field + ' AND customer_state = ' + section_state</tt>
:* Validations
:# 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.)
:* Default Values
:#:For example: <tt><nowiki>'string_field = ''some value'' AND customer_state = ' + section_state</nowiki></tt>
:* Formula Fields
::: 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.)
 
They are not available here:
:* Layout Rules
:* Custom Access Criteria
:* Reports

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