Difference between revisions of "Aggregate Functions"

From AgileApps Support Wiki
imported>Aeric
imported>Aeric
Line 22: Line 22:
:* '''object_field -''' Field in the specified object (other than a Lookup field)
:* '''object_field -''' Field in the specified object (other than a Lookup field)
:* '''criteria_string'''  
:* '''criteria_string'''  
:: A condition that specifies the object records that are part of the collection,
:: A condition that specifies the object records that are part of the collection, ''where the expression is contained in a string''.
:: ''where the expression is contained in a string''.
::: 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:
::: 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>  
::::<tt>SUM(Orders, order_total, 'customer_state =' + section_state)</tt>  
Line 36: Line 35:
:* The criteria expression must contain at least one index field.  
:* The criteria expression must contain at least one index field.  
:* If the index is a combination of fields, the criteria expression must contain the first field in that index. (It does not need to be a unique index, but an index is required, for performance.)
:* If the index is a combination of fields, the criteria expression must contain the first field in that index. (It does not need to be a unique index, but an index is required, for performance.)
:::''Note:'' An object can have up to five indexes.
::''Note:'' An object can have up to five indexes.
:* Unlike criteria specified elsewhere in the platform, the criteria expression provided here is in the form of a string. Therefore:
:* Unlike criteria specified elsewhere in the platform, the criteria expression provided here is in the form of a string. Therefore:
:# To test a boolean field, concatenate the value to a string--because in Java, anything concatenated to a string is converted to a string.
:# To test a boolean field, concatenate the value to a string--because in Java, anything concatenated to a string is converted to a string.
::#:For example: <tt>'' + boolean_field + ' AND '</tt>
:#:For example: <tt><nowiki>'' + boolean_field</nowiki></tt>
::#:or: <tt>boolean_field + ' AND customer_state = ' + section_state</tt>
:#:or: <tt>boolean_field + ' AND customer_state = ' + section_state</tt>
::# To include a string literal in a criteria string, use double quotes for the literal, and enclose the whole expression in single quotes.
:# To include a string literal in a criteria string, use double quotes for the literal, and enclose the whole expression in single quotes.
::#:For example: <tt>'string_field = "some value" AND customer_state = ' + section_state</tt>
:#:For example: <tt>'string_field = "some value" AND customer_state = ' + section_state</tt>
:* To learn about other operators you can use in expressions, see [[Common:Filter Expressions in APIs|Filter Expressions in APIs]]. (But keep the principles above in mind.)
:* To learn about other operators you can use in expressions, see [[Common:Filter Expressions in APIs|Filter Expressions in APIs]].<br>(But keep the principles above in mind.)


;Availability:
;Availability:

Revision as of 00:47, 23 April 2014

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_string) number, currency, boolean
AVG(object_name, object_field, criteria_string) number, currency, boolean
MAXIMUM(object_name, object_field, criteria_string) number, currency, date
MINIMUM(object_name, object_field, criteria_string) number, currency, date
COUNT(object_id, criteria_string) n/a

where:

  • object_id - Object Name or Object Identifier
  • object_field - Field in the specified object (other than a Lookup field)
  • criteria_string
A condition that specifies the object records that are part of the collection, where the expression is contained in a string.
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.

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.

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.
  • The criteria expression must contain at least one index field.
  • If the index is a combination of fields, the criteria expression must contain the first field in that index. (It does not need to be a unique index, but an index is required, for performance.)
Note: An object can have up to five indexes.
  • Unlike criteria specified elsewhere in the platform, the criteria expression provided here is in the form of a string. Therefore:
  1. To test a boolean field, concatenate the value to a string--because in Java, anything concatenated to a string is converted to a string.
    For example: '' + boolean_field
    or: boolean_field + ' AND customer_state = ' + section_state
  2. To include a string literal in a criteria string, use double quotes for the literal, and enclose the whole expression in single quotes.
    For example: 'string_field = "some value" AND customer_state = ' + section_state
  • To learn about other operators you can use in expressions, see Filter Expressions in APIs.
    (But keep the principles above in mind.)
Availability

The Aggregate Functions are available here:

  • Validations
  • Default Values
  • Formula Fields

They are not available here:

  • Layout Rules
  • Custom Access Criteria
  • Reports