Difference between revisions of "Aggregate Functions"

From AgileApps Support Wiki
imported>Aeric
imported>Aeric
Line 22: Line 22:
:: '''Restriction:''' A Lookup field cannot be used.
:: '''Restriction:''' A Lookup field cannot be used.


:* '''criteria: -''' A condition that specifies the object records that are part of the collection.
:* '''criteria: -''' A condition that specifies the object records that are part of the collection.  
::* Syntax is same as searchRecords() API of JAVA/REST criteria<br />
::* The [[#Filter Expression Syntax]] section below gives the syntax rules.
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.)
::* 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.
:::'''Note:''' The maximum number indexes currently allowed in an index definition is 5.
Line 51: Line 50:
:* Custom Access Criteria
:* Custom Access Criteria
:* Reports
:* Reports
===Filter Expression Syntax===
{{:Common:Filter Expressions in APIs}}

Revision as of 20:27, 10 February 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_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.
  • The #Filter Expression Syntax section below gives the syntax rules.
  • 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

Filter Expression Syntax

A filter expression defines search criteria in this format:

{field_name} {comparison operator} {field_value}
Optionally, complex expressions can be designed, using {logical operators} and parenthesis (...)
where
{field_name}
The name of a field in an Object; for example the ProductInventory object might have a field named QuantityOnHand.
Learn more: Field Name
{comparison operator}
Used to compare a field to a value, creating an expression.
Examples:
  • Less than: <
  • Equals: =
{field_value}
A number, datetime, or string; the value of a field; for example, in the Fruit field, a field value might be apples or oranges
{logical operator}
A logical operator, used to create complex filters
Examples:
  • apples OR oranges
  • apples AND oranges
parentheses (...)
Used to group expressions; determines the order in which the components of the expression are evaluated
Examples:
  • (((apples AND oranges) OR (oranges AND grapes)) AND pears)
  • (apples OR oranges OR grapes OR peaches) AND plums))
Considerations
  • Spaces are legal in filter expressions.
  • Quotes are needed around dates and strings. They are not needed around numbers.
  • Only single quotes are recognized around values, not double quotes.

Building Filter Expressions in APIs

Components of a filter expression are: <FIELD_NAME> <OPERATOR> <VALUE> <LOGICAL_OPERATOR>

FIELD_NAME

The name of the Field or Computed Field to use in creating a filter

OPERATOR

The standard alphabetic operators shown in the GUI can be used:
Operator Description
equals Returns only records with the specified value
not equal to Returns only records that do not include the specified value
less than Returns only records that are less than the specified value
less than equals Returns only records that are less than or equal to the specified value
greater than Returns only records that are more than the specified value
greater than equals Returns only records that are more than or equal to the specified value
contains Returns only records that contain the specified value
not contains Returns only records that do not contain the specified value
starts with Returns only records that start with the specified value
ends with Returns only records that end with the specified value
In addition, the symbolic operators can be used:
Operator Description
= Equal to

Returns only records with the specified value

!= Not Equal to

Returns only records that do not include the specified value

< Less than

Returns only records that are less than the specified value

> Greater than

Returns only records that are more than the specified value

<= Less than or equal to

Returns only records that are less than or equal to the specified value

>= Greater than or equal to

Returns only records that are more than or equal to the specified value

FIELD_VALUE

The search value - a string, a date, a number.
Examples:
String: 'Paper 123'
Date: '06/06/2007'
Numeric Value: 9383
Empty Field: '' or BLANK
Follow these guidelines when specifying values:
  • The value can be partial text or whole words
  • Enclose strings and date/time values in 'single quotes'
  • To search for a blank (empty) string, use two single quotes, with nothing between them ('').
  • To search for a blank (empty) field of other kinds, use a field value of BLANK
  • To search for multiple field values, separate each value using vertical pipe (|)
For example: company_name='Acme|Ajax'
(The criteria is satisfied when the company name is either Acme or Ajax)
  • To search for a field value that happens to contain a vertical pipe (|), you need to "escape" the pipe so it is recognized as a literal character in the field. To do that, you put a backslash in front of it: \|. For example, company_name contains '\|'
  • That works in REST APIs and in the GUI.
  • In Java strings, the backslash is already an escape character. (It combines with the one that follows it to create a single character.)
  • So in Java code, you need two backslashes: \\|
(The first two (\\) combine to become a single backslash (\), so the filter expression handler sees "\|", as desired.)
  • Finally, note that "equals" does not work for this particular search. The required operator is "contains".
  • To include a single quote in the value that you are searching for, specify an additional quote for every single quote ('').
For example in business rules, in the execution criteria use the single quote as listed in the following images:
  • When specified conditions are true:
Single quote condition.png
  • When specified expression is true:
Single quote expression.png

LOGICAL_OPERATOR

Logical operators can be used to build more complex expressions.
The logical operators are:
AND
OR
Considerations
  • Two subexpressions joined by a logical operator form a logical expression.
  • Logical expressions resolve to a Boolean value: 1/0 or TRUE/FALSE.
  • Use parentheses--()--to group expressions logically and to join multiple expressions.
  • Parentheses are used in pairs; each open parenthesis "(" requires a closing parenthesis ")".
  • For example:
((<expression1> AND <expression2>) OR (<expression3> AND <expression_4>))
Learn more: Using Logical Operators and Parentheses