AgileApps Support Wiki

Difference between revisions of "Aggregate Functions"

From AgileApps Support Wiki
imported>Aeric
imported>Aeric
Line 1: Line 1:
<h3>
===Aggregate Functions===
Aggregate Functions</h3>
<div style="font-family: Arial, Verdana, sans-serif; font-size: 12px; color: rgb(34, 34, 34); background-color: rgb(255, 255, 255); ">
<p>
<strong>Background</strong></p>
<p>
Spreadsheet users are used to functions like SUM and COUNT that operate on a range of data.<br />
This enhancement provides similar functionality, in a way that lets the designer specify the records to operate on.</p>
<p>
<strong>Overview</strong></p>
<p>
Formula functions provide the capability for selecting a group of records from an object and<br />
producing an aggregate value (SUM, COUNT, MAX, MIN, AVG), without having to write Java code.</p>
<p>
<strong>Functionality</strong></p>
<table border="1" cellpadding="1" cellspacing="1" style="width: 457px; height: 111px; ">
<tr>
<td>
<strong>Methods</strong></td>
<td>
<strong>Field Types</strong></td>
</tr>
<tr>
<td>
SUM(<em>object_id, object_field, criteria</em>)</td>
<td>
number, currency, boolean</td>
</tr>
<tr>
<td>
MAXIMUM(<em>object_id, object_field, criteria</em>)</td>
<td>
number, currency, date</td>
</tr>
<tr>
<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>


====Overview====
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.
====Functionality====
:{| border="1" cellpadding="1" cellspacing="1"
! Methods !! Field Types
|-
| SUM(<em>object_id, object_field, criteria</em>) || number, currency, boolean
|-
| AVG(<em>object_id, object_field, criteria</em>) || number, currency, boolean</td>
|-
| MAXIMUM(<em>object_id, object_field, criteria</em>) || number, currency, date
|-
| MINIMUM(<em>object_id, object_field, criteria</em>) || number, currency, date
|-
| COUNT(<em>object_id, criteria</em>) || n/a
|}
where:
where:
<p style="margin-left: 40px; ">
:* '''object_id -''' Object Name or Object Identifier
<strong>object_id</strong>:<em>&nbsp;</em>Object Name or Object Identifier</p>
:* '''object_field -''' Field in the specified object
<p style="margin-left: 40px; ">
:: '''Restriction:''' A Lookup field cannot be used.
<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>
:* '''criteria: -''' A condition that specifies the object records that are part of the collection.
<p style="margin-left: 40px; ">
::* Syntax is same as searchRecords() API of JAVA/REST criteria<br />
<strong>criteria:&nbsp;&nbsp;</strong>A condition that specifies the object records that are part of the collection.</p>
The relevant information is here (only the page title is inappropriate): [[JAVA_API:Filter_Expressions_in_JAVA_APIs]]
<ul>
::* 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.)
<li style="margin-left: 40px;">
:::'''Note:''' The maximum number indexes currently allowed in an index definition is 5.
Syntax is same as searchRecords() API of JAVA/REST criteria<br />
 
The relevant information is here (only the page title is inappropriate):<br />
;Considerations:
<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>
:* The SUM() of a boolean field gives a count of values that are yes/true.
<li style="margin-left: 40px;">
:* The AVG() of a boolean field gives the percentage that are yes/true.
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 />
{{Note|The results doenot take into account user-security restrictions. These functions operate on
&nbsp;&nbsp;&nbsp; - This is a performance requirement, to provide for reasonably efficient accessing of records.</li>
the entire set of records in an [[Object]], regardless of user capabilities.}}
<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====
====Availability====


Line 100: Line 44:
::* Criteria (Action Based - Add, Updae, Delete, Import Record, Ownership Change, Workflow State Change)
::* Criteria (Action Based - Add, Updae, Delete, Import Record, Ownership Change, Workflow State Change)
::* Add/Update Record Action
::* Add/Update Record Action
:* Calendar Data Policies - Criteria Not Support</li>
:* Calendar Data Policies - Criteria Not Support
:* Calendar Data Policies - Add/Update Record Actions Aggregate functions will be evaluated.</li>
:* Calendar Data Policies - Add/Update Record Actions Aggregate functions will be evaluated.</li>
:* Default Values
:* Default Values

Revision as of 20:09, 10 February 2012

Aggregate Functions

Overview

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.

Functionality

Methods Field Types
SUM(object_id, object_field, criteria) number, currency, boolean
AVG(object_id, object_field, criteria) number, currency, boolean
MAXIMUM(object_id, object_field, criteria) number, currency, date
MINIMUM(object_id, 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
Restriction: A Lookup field cannot be used.
  • criteria: - A condition that specifies the object records that are part of the collection.
  • Syntax is same as searchRecords() API of JAVA/REST criteria

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.)
Note: The maximum number 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.

Notepad.png

Note: The results doenot 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
  • Criteria (Action Based - Add, Updae, Delete, Import Record, Ownership Change, Workflow State Change)
  • Add/Update Record Action
  • Calendar Data Policies - Criteria Not Support
  • Calendar Data Policies - Add/Update Record Actions Aggregate functions will be evaluated.
  • Default Values
  • Formula Fields

They are not available here:

  • Layout Rules
  • Custom Access Criteria
  • Reports
Retrieved from "https://agileappscloud.info/aawiki-src/index.php?title=Aggregate_Functions&oldid=16664"