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

FunctionDescription
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.

This content applies to 21.6.4 and later.

FunctionsDescription
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)

This content applies to 21.6.4 and later.

FunctionsDescription

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. 

This content applies to 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)

Note: The "IDT <> OUTER.IDT" ensures that EXISTS is only detecting service records that are not the current service record.
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).