The Advanced Query Builder

The advanced builder adds what the simple one cannot express: or across different fields, nested grouping, and conditions about related records.

Choose New Advanced Query to start one, or convert an existing query when it outgrows the simple builder.

Conditions and groups

A condition is one comparison: a field, an operator, a value — the same idea as a filter row.

A group contains conditions and other groups, and decides whether its contents are combined with and or with or. Nesting groups is how you express logic that is not a flat list.

For example, "discounts over £50, on register 3 or register 7, excluding the manager" is a group combined with and, containing an or group for the registers.

Building one that reads correctly

Work outside-in. Decide the top-level relationship first — is the whole query fundamentally an and or an or? — then fill in the groups beneath it.

The most common mistake is a flat list of conditions joined by or when only two of them were meant to be alternatives. That returns far too much, and the symptom is a result set much larger than expected. Check the grouping before checking the conditions.

Value lookups

Where a field has known values, the value box offers them rather than requiring free text. Use the lookup rather than typing: it avoids spelling mistakes that silently return nothing, and it shows you what values actually exist.

Writing an expression

For cases the visual builder cannot express, a query can be written directly as an expression. The editor offers syntax highlighting and a choice of monospaced fonts.

This is a specialist route. Almost everything is expressible with groups, and a visual query is easier for a colleague to read later. Reach for the expression editor when you genuinely cannot build what you need any other way.

Testing as you go

Run the query as you build it rather than at the end. The row count after each addition tells you whether the last change did what you meant — and finding a mistake after one change is much easier than after six.

Existence tests and subqueries

Conditions about related records — a subject with no detections, a check with no discount — are a separate mechanism. See Subqueries and Existence Tests.

Disabled queries

A query can be marked disabled, shown as Query disabled. A disabled query is kept but not run, which is the tidy way to retire something that is temporarily wrong without deleting work others may rely on.

Saving and sharing

Save from the Query Manager. Advanced queries especially deserve a clear name and description — the logic is not obvious from the result, and you are writing for whoever opens it next.