Aggregate Functions

From AgileApps Support Wiki
Revision as of 19:43, 10 February 2012 by imported>Aeric (Created page with "<h3> 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>B…")
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)

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

<tbody> </tbody>
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