Aggregate Functions
On this page:
Use aggregate functions to determine the existence of, or to perform a count or aggregation against, a set of profiles or documents that relate in some way to the profile or document that the formula is written for. The profile type, child profile type, document template, or child template that the formula is written for is referred to as the context type. The profile type, child profile type, document template, or child template that is the target of aggregation is referred to as the target type.
Functions
| Function | Description |
|---|---|
| Exists(…) | Returns True if any specified records of the specified target type exist. |
| Count(…) | Returns a count of the specified records of the specified target type. |
| SumOf(…) | Returns a sum of all numeric values in the specified records of the specified target type. Empty values are ignored (not included in the sum), but if all values are empty, the result is EMPTY. |
| MinOf(…) | Returns the minimum numeric values in the specified records of the specified target type. Empty numeric values are ignored, but if all values are empty, the result is EMPTY. |
| MaxOf(…) | Returns the maximum numeric values in the specified records of the specified target type. Empty numeric values are ignored, but if all values are empty, the result is EMPTY. |
| DistinctCountOf(…) | Returns a count of the distinct (unique) values in the specified field or expression in the specified records of the specified target type. Empty values are ignored for the count. |
| TopOneValueOf(…) | Returns the specified value found in the first record of the specified records in the specified target type. You can optionally specify a sort or descending sort to influence which value is the top one. |
21.6.4 and later.
| Functions | Description |
|---|---|
| NthValueOf(…) | Returns the specified value found in the nth record of the specified records in the specified target type. You can optionally specify a sort or descending sort to influence the sequencing of the values. (Version 19.4 and later) |
21.6.4 and later.
| Functions | Description |
|---|---|
JoinCharacterValueOf(…) | Joins (concatenates) the specified character value from the specified records of the specified target type into a single value with individual values separated from each other by the specified delimiter (typically a comma). The character value delimiter is always the second function parameter and must be a literal string such as ', ' (specifies a comma delimiter). You can optionally specify a sort or descending sort to influence the sequencing of the joined character values. |
JoinDistinctCharacterValueOf(…) | Very similar to JoinCharacterValueOf except that it will join unique values and omit duplicate ones. However, this function does not support an explicit sort. Instead, it automatically sorts the unique character values as it joins them. All other function parameters are the same as JoinCharacterValueOf. |
Parameters
The parameters of the aggregate functions have a similar structure.
- The first parameter is always the identification of the target type (profile type name or template code).
- For functions that aggregate a specific value (for example, SumOf, TopOneValueOf, and NthValueOf), the second parameter is the field or expression that produces that value.
- In advanced cases where the relationship between the target type and the context type is not directly implicit, an explicit relationship may need to be specified as the next parameter.
- The last parameter specifies a filter expression that can generally reference fields from both the target type and the context type. This parameter is optional.
- If fields have the same name in the target type and context type, the field in the context type can be prefixed with "Outer.FieldName" to distinguish it from the one in the target type.
The TopOneValueOf function is a special case and supports an optional sort expression preceded by a column and with an optional DESC (descending) keyword. This sort is used to control what the top value will be.
19.4 and later.
The NthValueOf function consists of the same parameters and structure as the TopOneValueOf function but takes an additional parameter of sequence <0 to N-1>.
Following is a listing of all the syntax variations, organized by the context type and the target type.
Context Type | Target Type | Syntax (optional parts in italics) |
|---|---|---|
Top-level profile type | Child profile type of top-level profile type. | Exists(child_profile_type_name, filter_expression) Count(child_profile_type_name, filter_expression) SumOf(child_profile_type_name, numeric_value, filter_expression) MinOf(child_profile_type_name, numeric_value, filter_expression) MaxOf(child_profile_type_name, numeric_value, filter_expression) DistinctCountOf(child_profile_type_name, value, filter_expression) TopOneValueOf(child_profile_type_name, value, filter_expression : sort_expression DESC) NthValueOf(child_profile_type_name, value, filter_expression : sort_expression, 0 to N-1) JoinCharacterValueOf(child_profile_type_name, delimiter_literal_string, character_value, filter_expression : sort_expression DESC) JoinDistinctCharacterValueOf(child_profile_type_name, delimiter_literal_string, character_value, filter_expression) |
Child profile type | Child profile type of same top-level profile type. | Same as above |
Document template | Child profile type of top-level profile type that the document template is associated with. | Same as Above |
Top-level profile type | Document template of top-level profile type. | Exists(template_id, filter_expression) Count(template_id, filter_expression) SumOf(template_id, numeric_value, filter_expression) MinOf(template_id, numeric_value, filter_expression) MaxOf(template_id, numeric_value, filter_expression) MaxOf(template_id, numeric_value, filter_expression) DistinctCountOf(template_id, value, filter_expression) TopOneValueOf(template_id, value, filter_expression : sort_expression DESC) NthValueOf(template_id, value, filter_expression : sort_expression, 0 to N-1) JoinCharacterValueOf(template_id, delimiter_literal_string, character_value, filter_expression : sort_expression DESC) JoinDistinctCharacterValueOf(template_id, delimiter_literal_string, character_value, filter_expression) |
Document template | Document template of the same top-level profile type. | Same as above |
Document template | Child template. | Same as above but use the child template ID as the first parameter |
Child template | Another child template from the same document template. | Same as above but use the child template ID as the first parameter |
Child template | Grand-child template. | Same as above but use the grand-child template ID as the first parameter |
Grand-child template | Another grand-child template of the same child template. | Same as above but use the grand-child template ID as the first parameter |
Top-level profile type | Other top-level profile types that reference context profile type via the profile reference field. | Exists(profile_type_name, profile_reference, filter_expression) Count(profile_type_name, profile_reference, filter_expression) SumOf(profile_type_name, numeric_value, profile_reference, filter_expression) MinOf(profile_type_name, numeric_value, profile_reference, filter_expression) MaxOf(profile_type_name, numeric_value, profile_reference, filter_expression) DistinctCountOf(profile_type_name, value, profile_reference, filter_expression) TopOneValueOf(profile_type_name, value, profile_reference, filter_expression : sort_expression DESC) NthValueOf(profile_type_name, value, profile_reference, filter_expression : sort_expression, 0 to N-1) JoinCharacterValueOf(profile_type_name, delimiter_literal_string, character_value, profile_reference, filter_expression : sort_expression DESC) JoinDistinctCharacterValueOf(profile_type_name, delimiter_literal_string, character_value, profile_reference, filter_expression) |
Document template | Child profile type where the top-level profile type is not associated with the document template but the document template has a profile reference field pointing to the parent profile. | Exists(profile_type_name, child_profile_parent_reference = document_profile_reference, filter_expression) Count(profile_type_name, child_profile_parent_reference = document_profile_reference, filter_expression) SumOf(profile_type_name, numeric_value, child_profile_parent_reference = document_profile_reference, filter_expression) MinOf(profile_type_name, numeric_value, child_profile_parent_reference = document_profile_reference, filter_expression) MaxOf(profile_type_name, numeric_value, child_profile_parent_reference = document_profile_reference, filter_expression) MaxOf(profile_type_name, numeric_value, child_profile_parent_reference = document_profile_reference, filter_expression) DistinctCountOf(profile_type_name, value, child_profile_parent_reference = document_profile_reference, filter_expression) TopOneValueOf(profile_type_name, value, child_profile_parent_reference = document_profile_reference, filter_expression : sort_expression DESC) NthValueOf(profile_type_name, value, child_profile_parent_reference = document_profile_reference, filter_expression : sort_expression, 0 to N-1) JoinCharacterValueOf(profile_type_name, delimiter_literal_string, character_value, child_profile_parent_reference = document_profile_reference, filter_expression : sort_expression DESC) JoinDistinctCharacterValueOf(profile_type_name, delimiter_literal_string, character_value, child_profile_parent_reference = document_profile_reference, filter_expression) |
Self-Referential Formulas
The "outer" keyword is particularly useful when the target type is the same as the context type (self-referential). An example is if, in the context of a service record (such as a constraint), you want to detect whether another service exists with the same service type. Use the following formula to accomplish this:
- Exists(ServiceRecords, ServiceType = OUTER.ServiceType AND IDT <> OUTER.IDT)
The same construct works in documents and child documents. In the context of child documents, IDT refers to the unique internal identity of the child document, and ParentIDT refers to the unique internal identity of the parent document).