Aggregate Functions

Aggregate Functions

Background

Spreadsheet users are used to functions like SUM and COUNT that operate on a range of data.
This enhancement provides similar functionality, in a way that lets the designer specify the records to operate on.

Overview

Formula functions provide the capability for selecting a group of records from an object and
producing 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
MAXIMUM(object_id, object_field, criteria)	number, currency, date
MINIMUM(object_id, object_field, criteria)	number, currency, date
AVG(object_id, object_field, criteria)	number, currency, boolean
COUNT(object_id, criteria)	n/a

where:

object_id: Object Name or Object Identifier

object_field:  Field in the specified object 
             Restriction: Fields in A Lookup field cannot be used to reference a field in a related record.

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):
   <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>
• Criteria must contain at least one index field. If index is combination of fields, the criteria must contain first field in that index.
      - It need not be a unique index.
      - This is a performance requirement, to provide for reasonably efficient accessing of records.
• Note:
  The maximum number indexes allowed in an index definition is currently 5.
• Using index field in criteria will be validated in evaluation time also.
• Index can be edited/removed after creation of the formula definition, for that reason formula validation for index presence occurs at runtime also.
   

e.g 'price < 50'

Notes:

• 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 result does not take into account user-security restrictions.
  It depends on the total number of records, irrespective of user capabilities.
  (Document this fact prominently.)

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