LongJump Support Wiki

Difference between revisions of "Aggregate Functions"

From LongJump Support Wiki
imported>Aeric
imported>Aeric
 
(15 intermediate revisions by the same user not shown)
Line 1: Line 1:
===Aggregate Functions===
__NOINDEX__
 
====Overview====
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 (SUM, COUNT, MAX, MIN, AVG), without having to write Java code.
produce an aggregate value, without having to write Java code.
 
<span id="SUM"> </span> <span id="AVG"> </span>
====Functionality====
<span id="MAX"> </span> <span id="MIN"> </span>
<span id="COUNT"> </span>
:{| border="1" cellpadding="5" cellspacing="1"
:{| border="1" cellpadding="5" cellspacing="1"
! Methods !! Field Types
! Methods !! Field Types
|-
|-
| SUM(<em>object_id, object_field, criteria</em>) || number, currency, boolean
| SUM(object_name, object_field, criteria) || number, currency, boolean
|-
|-
| AVG(<em>object_id, object_field, criteria</em>) || number, currency, boolean
| AVG(object_name, object_field, criteria) || number, currency, boolean
|-
|-
| MAXIMUM(<em>object_id, object_field, criteria</em>) || number, currency, date
| MAXIMUM(object_name, object_field, criteria) || number, currency, date
|-
|-
| MINIMUM(<em>object_id, object_field, criteria</em>) || number, currency, date
| MINIMUM(object_name, object_field, criteria) || number, currency, date
|-
|-
| COUNT(<em>object_id, criteria</em>) || n/a
| COUNT(object_id, criteria) || n/a
|}
|}
where:
where:
:* '''object_id -''' Object Name or Object Identifier
:* '''object_id -''' Object Name or Object Identifier
:* '''object_field -''' Field in the specified object
:* '''object_field -''' Field in the specified object (other than a Lookup field)
:: '''Restriction:''' A Lookup field cannot be used.
 
:* '''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>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]]


:* '''criteria: -''' A condition that specifies the object records that are part of the collection.
::* Syntax is same as searchRecords() API of JAVA/REST criteria<br />
The relevant information is here (only the page title is inappropriate): [[JAVA_API:Filter_Expressions_in_JAVA_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 34: Line 35:
:* 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.


{{Note|The results doenot take into account user-security restrictions. These functions operate on  
{{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.}}
the entire set of records in an [[Object]], regardless of user capabilities.}}


====Availability====
;Availability:


The Aggregate Functions are available here:
The Aggregate Functions are available here:
:* Validations
:* Validations
:* Data Policies
:* Data Policies
::* Criteria (Action Based - Add, Updae, Delete, Import Record, Ownership Change, Workflow State Change)
::* Triggering Criteria for these Action-Based Data Policies:
::* Add/Update Record Action
:::  Add, Update, Delete, Import Record, Ownership Change, Workflow State Change
:* Calendar Data Policies - Criteria Not Support
::* Add/Update Record Actions for Action-Based Data Policies
:* Calendar Data Policies - Add/Update Record Actions Aggregate functions will be evaluated.</li>
:* Default Values
:* Default Values
:* Formula Fields
:* Formula Fields
Line 53: 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
  • 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.

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.

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
Retrieved from "https://agileappscloud.info/ljwiki-src/index.php?title=Aggregate_Functions&oldid=12974"