Sub Query expression – one value from a query¶

The sub query expression runs a query on any entity and returns one value derived from the records it finds: their number, whether any exists, a sum, or the value of a field. Unlike the Aggregate expression, it needs no relationship.

1. At a glance¶


Purpose	Runs a query and returns one value from its result
Typical use	"Number of open tickets of this customer" without a direct relationship, "Is there already a record with this serial number?", looking up a value in a configuration entity
Returns	Depends on the aggregation type (section 3)
Related	Aggregate (over a relationship), Queries

2. Settings¶

Setting	Meaning	Required
"Lookup Entity"	The entity whose records are searched	yes
"Aggregation type"	`Select`, `Count`, `Sum`, `Average`, `Min`, `Max`, `Exists` or `SelectFirst`	yes
"Lookup Field"	The field of the lookup entity whose values are used. Disabled for `Count` and `Exists`.	for all types except `Count` and `Exists`
"Limit"	The maximum number of records the query reads. Empty: 9,999. Disabled for `Exists`, which always reads a single record.	no
Query	The conditions the records must meet, edited like a query directly in this editor	yes

The query belongs to the expression; it is not a stored query and cannot be shared with other definitions.

In the conditions of the query, the left side is a field of the lookup entity. The right side is evaluated for the current record of the expression – this is how a sub query refers to the record it is used for, for example "Customer equals the customer of this ticket".

A plain field on the right side compares two columns

If the right side is a Field expression on its own, nexxcite does not read the current record. It compares two fields of the same searched record instead (see Queries, section 4). To compare with a field of the current record, use an expression that is not a plain field reference – for example a text expression into which the field is inserted.

3. Aggregation types¶

Type	Returns	Without matching records
`Count`	The number of matching records	`0`
`Exists`	`true` if at least one record matches, otherwise `false`	`false`
`Sum`	The sum of the non-empty values of "Lookup Field"	`0`
`Average`	The average of the non-empty values	`0`
`Min` / `Max`	The smallest / largest non-empty value	empty
`Select`	A JSON array of the non-empty values	`[]`
`SelectFirst`	The first non-empty value, as a plain value	empty

4. Behaviour¶

The query is run with the read limits of the current user. Records the user may not see are not counted.
At most "Limit" records are read – 9,999 when "Limit" is empty. Count therefore never returns more than the limit, and Sum, Average, Min, Max and Select only consider the first records up to the limit. Exists reads a single record, whatever the limit.
A smaller limit makes the expression cheaper where only a few records matter, for example Select for a short list of values.
The records are read completely and the values are computed afterwards. The sub query is therefore noticeably more expensive than a field reference – see section 6.
A missing lookup entity, missing query, missing "Lookup Field" (where needed) or a failing query make the expression return an empty value.
An empty query (no conditions) is valid and selects all records of the lookup entity.

5. Examples¶

Duplicate check: Lookup entity "Device", aggregation type Exists, query: "Serial number" Equals a text expression with the field "Serial number" of the current record inserted, and the record id field NotEquals the system function EntitySetRecId. Used in a validation rule, this prevents a second device with the same serial number.

Value from a configuration entity: Lookup entity "Settings per country", aggregation type SelectFirst, lookup field "VAT rate", query: "Country" Equals a text expression with the field "Country" of the current order inserted.

Open tickets of the customer: Lookup entity "Ticket", aggregation type Count, query: "Customer" Equals a text expression with the field "Customer" of the current record inserted, and "Status" NotEquals Closed.

6. Limitations and performance¶

"Limit" cuts the result off, it does not warn: with more matching records than the limit, Count returns the limit and the other types only see part of the records (section 4).
For SelectFirst, "first" follows the order in which the database returns the records. A sort order cannot be set in the editor, so with several matching records the result is not predictable. Make the conditions specific enough to match one record.
Every evaluation runs the query. Used in a grid column, that is one query per row; used in a form, one query whenever the form evaluates the expression. Where the value is needed often and may be a little out of date, wrap the expression in a Cache expression or store the value in a field with a business rule.