Difference between revisions of "Aggregate Functions"
From LongJump Support Wiki
imported>Aeric |
imported>Aeric |
||
| (4 intermediate revisions by the same user not shown) | |||
| Line 1: | Line 1: | ||
__NOINDEX__ | |||
These functions give you the ability to select a group of records from an arbitrary object and | 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. | produce an aggregate value, without having to write Java code. | ||
| Line 22: | Line 23: | ||
:* '''criteria: -''' A condition that specifies the object records that are part of the collection. | :* '''criteria: -''' A condition that specifies the object records that are part of the collection. | ||
:: | ::: 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> | ::::<tt>SUM(Orders, order_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. | |||
:::''Learn more:'' [[Common:Filter Expressions in APIs|Filter Expressions in APIs]] | :::''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.) | ::* 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 indexes currently allowed in an index definition is 5. | :::''Note:'' The maximum number of indexes currently allowed in an index definition is 5. | ||
;Considerations: | ;Considerations: | ||
| Line 44: | Line 46: | ||
::: Add, Update, Delete, Import Record, Ownership Change, Workflow State Change | ::: Add, Update, Delete, Import Record, Ownership Change, Workflow State Change | ||
::* Add/Update Record Actions for Action-Based Data Policies | ::* Add/Update Record Actions for Action-Based Data Policies | ||
:* Default Values | :* Default Values | ||
:* Formula Fields | :* Formula Fields | ||
| Line 52: | Line 53: | ||
:* Custom Access Criteria | :* Custom Access Criteria | ||
:* Reports | :* Reports | ||
:* Add/Update Record Actions for Calendar-based Data Policies | |||
Latest revision as of 21:58, 5 March 2012
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) number, currency, boolean AVG(object_name, object_field, criteria) number, currency, boolean MAXIMUM(object_name, object_field, criteria) number, currency, date MINIMUM(object_name, object_field, criteria) number, currency, date COUNT(object_id, criteria) n/a
where:
- object_id - Object Name or Object Identifier
- object_field - Field in the specified object (other than a Lookup field)
- criteria: - A condition that specifies the object records that are part of the collection.
- 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.
- Learn more: Filter Expressions in APIs
- 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:
- 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
- Data Policies
- Triggering Criteria for these Action-Based Data Policies:
- Add, Update, Delete, Import Record, Ownership Change, Workflow State Change
- Add/Update Record Actions for Action-Based Data Policies
- Default Values
- Formula Fields
They are not available here:
- Layout Rules
- Custom Access Criteria
- Reports
- Add/Update Record Actions for Calendar-based Data Policies