LongJump Support Wiki

Difference between revisions of "Aggregate Functions"

From LongJump Support Wiki
imported>Aeric
imported>Aeric
 
(18 intermediate revisions by the same user not shown)
Line 1: Line 1:
<h3>
__NOINDEX__
Aggregate Functions</h3>
These functions give you the ability to select a group of records from an arbitrary object and
<div style="font-family: Arial, Verdana, sans-serif; font-size: 12px; color: rgb(34, 34, 34); background-color: rgb(255, 255, 255); ">
produce an aggregate value, without having to write Java code.
<p>
<span id="SUM"> </span> <span id="AVG"> </span>  
<strong>Background</strong></p>
<span id="MAX"> </span> <span id="MIN"> </span>  
<p>
<span id="COUNT"> </span>
Spreadsheet users are used to functions like SUM and COUNT that operate on a range of data.<br />
:{| border="1" cellpadding="5" cellspacing="1"
This enhancement provides similar functionality, in a way that lets the designer specify the records to operate on.</p>
! Methods !! Field Types
<p>
|-
<strong>Overview</strong></p>
| SUM(object_name, object_field, criteria) || number, currency, boolean
<p>
|-
Formula functions provide the capability for selecting a group of records from an object and<br />
| AVG(object_name, object_field, criteria) || number, currency, boolean
producing an aggregate value (SUM, COUNT, MAX, MIN, AVG), without having to write Java code.</p>
|-
<p>
| MAXIMUM(object_name, object_field, criteria) || number, currency, date
<strong>Functionality</strong></p>
|-
<table border="1" cellpadding="1" cellspacing="1" style="width: 457px; height: 111px; ">
| MINIMUM(object_name, object_field, criteria) || number, currency, date
<tr>
|-
<td>
| COUNT(object_id, criteria) || n/a
<strong>Methods</strong></td>
|}
<td>
where:
<strong>Field Types</strong></td>
:* '''object_id -''' Object Name or Object Identifier
</tr>
:* '''object_field -''' Field in the specified object (other than a Lookup field)
<tr>
 
<td>
:* '''criteria: -''' A condition that specifies the object records that are part of the collection.
SUM(<em>object_id, object_field, criteria</em>)</td>
::: 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:
<td>
::::<tt>SUM(Orders, order_total, 'customer_state =' + section_state)</tt>  
number, currency, boolean</td>
::: You could then write a report to group the records by section name, giving you totals for each section of the country.
</tr>
:::''Learn more:'' [[Common:Filter Expressions in APIs|Filter Expressions in APIs]]
<tr>
 
<td>
::* 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.)
MAXIMUM(<em>object_id, object_field, criteria</em>)</td>
:::''Note:'' The maximum number of indexes currently allowed in an index definition is 5.
<td>
 
number, currency, date</td>
;Considerations:
</tr>
:* The SUM() of a boolean field gives a count of values that are yes/true.
<tr>
:* The AVG() of a boolean field gives the percentage that are yes/true.
<td>
MINIMUM(<em>object_id, object_field, criteria</em>)</td>
<td>
number, currency, date</td>
</tr>
<tr>
<td>
AVG(<em>object_id, object_field, criteria</em>)</td>
<td>
number, currency, boolean</td>
</tr>
<tr>
<td>
COUNT(<em>object_id, criteria</em>)</td>
<td>
n/a</td>
</tr>
</table>


where:
{{Important|The results do not take into account user-security restrictions. These functions operate on
<p style="margin-left: 40px; ">
the entire set of records in an [[Object]], regardless of user capabilities.}}
<strong>object_id</strong>:<em>&nbsp;</em>Object Name or Object Identifier</p>
 
<p style="margin-left: 40px; ">
;Availability:
<strong>object_field: &nbsp;</strong>Field in the specified object&nbsp;<br />
<em>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<strong>Restriction:&nbsp;</strong></em>Fields in A Lookup field&nbsp;<em>cannot</em>&nbsp;be used to reference a field in a related record<em>.</em></p>
<p style="margin-left: 40px; ">
<strong>criteria:&nbsp;&nbsp;</strong>A condition that specifies the object records that are part of the collection.</p>
<ul>
<li style="margin-left: 40px;">
Syntax is same as searchRecords() API of JAVA/REST criteria<br />
The relevant information is here (only the page title is inappropriate):<br />
<span style="color: rgb(178, 34, 34);">&nbsp;</span><a href="http://lj.platformatyourservice.com/wiki/JAVA_API:Filter_Expressions_in_JAVA_APIs">http://lj.platformatyourservice.com/wiki/JAVA_API:Filter_Expressions_in_JAVA_APIs</a></li>
<li style="margin-left: 40px;">
Criteria must contain at least one index field. If index is combination of fields, the criteria must contain first field in that index.<br />
&nbsp;&nbsp;&nbsp; - It need not be a unique index.<br />
&nbsp;&nbsp;&nbsp; - This is a performance requirement, to provide for reasonably efficient accessing of records.</li>
<li style="margin-left: 40px;">
<strong>Note:</strong><br />
The maximum number indexes allowed in an index definition is currently 5.</li>
<li style="margin-left: 40px;">
Using index field in criteria will be validated in evaluation time also.</li>
<li style="margin-left: 40px;">
Index can be edited/removed after creation of the formula definition, for that reason formula validation for index presence occurs at runtime also.<br />
&nbsp;</li>
</ul>
<p style="margin-left: 80px; ">
e.g <strong>&#39;price &lt; 50&#39;</strong></p>
<p style="margin-left: 40px; ">
<em><strong>Notes:</strong></em></p>
<ul>
<li style="margin-left: 40px; ">
The SUM() of a boolean field gives a count of values that are yes/true.</li>
<li style="margin-left: 40px; ">
The AVG() of a boolean field gives the percentage that are yes/true.</li>
<li style="margin-left: 40px; ">
<em>The result does&nbsp;not&nbsp;take into account user-security restrictions.<br />
It depends on the total number of records, irrespective of user capabilities.</em><br />
(Document this fact prominently.)</li>
</ul>
====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</li>
::* 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 109: 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"