Aggregate expression – values over related records¶

The aggregate expression computes one value over the records linked to the current record through a relationship: how many there are, whether any exists, the sum, average, smallest or largest value, or a list of values.

1. At a glance¶


Purpose	Counts, sums or collects over the related records of the current record
Typical use	Number of open tasks of a project, total of all order lines, "has at least one attachment"
Returns	Depends on the aggregation type (section 3)
Related	Sub Query (records of any entity, without relationship), Queries (related clauses)

2. Settings¶

Setting	Meaning	Required
"Relationship"	The relationship from the entity of the current record to the records that are aggregated	yes
"Aggregation type"	`Count`, `Sum`, `Average`, `Min`, `Max`, `Exists`, `Select` or `SelectFirst`	yes
"Apply to"	The value computed for each related record – usually a field of the related entity. Disabled for `Count` and `Exists`.	for all types except `Count` and `Exists`
"Limit"	Optional condition evaluated for each related record. Only records for which it is true are aggregated. Empty means all related records.	no

"Apply to" and "Limit" are evaluated for the related record, so they read the fields of the related entity. They are only available once a relationship is selected.

3. Aggregation types¶

Type	Returns	Without related records
`Count`	The number of related records (after "Limit")	`0`
`Exists`	`true` if at least one related record exists (after "Limit"), otherwise `false`	`false`
`Sum`	The sum of the values; every value is converted to a number	`0`
`Average`	The average of the values	`0`
`Min`	The smallest value	empty
`Max`	The largest value	empty
`Select`	A JSON array of all non-empty values, for example `["A-100","A-101"]`	`[]`
`SelectFirst`	The first non-empty value, as a plain value (not an array)	empty

Sum and Average treat values that are not numbers as 0.
Min and Max ignore empty values. They compare the remaining values as numbers if all of them are numbers, as dates if all of them are dates, and as text otherwise – so 9 is smaller than 10, but in a mix of numbers and texts, 10 comes before 9. The value found is returned unchanged. If no value is left, the result is empty.
The order of Select and SelectFirst is the order in which the related records are loaded; it cannot be set here.

4. Behaviour¶

The expression needs a current record. Without one it returns an empty value.
It works on the related records as they are in the current record – in a form, this includes related records that were added in the form and have not been saved yet.
All related records are loaded and evaluated one by one. On relationships with very many records this costs time; see the performance hint in section 6.
A missing relationship, a missing "Apply to" (where it is needed) or no aggregation type make the expression return an empty value.
Count combined with a filled-in "Apply to" returns an empty value (0 if there are no related records). The editor disables "Apply to" for Count but keeps a value entered before the type was changed. Clear "Apply to" before you switch to Count.

5. Examples¶

Open tasks of a project: Relationship "Tasks", aggregation type Count, Limit: "Status" NotEquals Done.

Order total: Relationship "Order lines", aggregation type Sum, Apply to: a Mathematic expression Quantity * Unit price (fields of the order line).

Has an attachment: Relationship "Attachments", aggregation type Exists.

E-mail addresses of all contacts: Relationship "Contacts", aggregation type Select, Apply to: the field "E-mail". The result can be used as the right side of the operator In or processed further with the JSON rules of Modify Value.

6. Limitations¶

Only relationships that start at the entity of the current record can be used.
Every evaluation loads the related records. In a grid column, that happens for every row. For large numbers of related records, consider storing the value in a field with a business rule.