Queries – selecting records¶
A query selects the records of one entity: "open tickets", "my tasks", "customers without a contract". Grids, lists, dashboards, actions, rules and expressions use queries to get their records. This chapter describes how a query is built, how each kind of condition behaves and where queries are used.
It is intended for administrators who configure listings, actions and rules.
1. Queries at a glance¶
A query consists of conditions on the records of one entity. It does not compute values, does not display anything and does not change anything – it only decides which records belong to the result.
| Kind of condition | What it checks | Example |
|---|---|---|
| Clause | A field of the record compared with a value | "Status" NotEquals Closed |
| Group | Several conditions combined with And or Or – the brackets of a query | "Priority is High and (Category is Hardware or Network)" |
| Related clause | How many related records meet conditions | "Customers with at least 2 overdue invoices" |
| Date part clause | A part of a date, or a time range relative to now | "Created within the last 7 days", "Due month is 12" |
| Full text clause | Words in the full-text-indexed fields | "Contains 'printer' and 'toner'" |
Summary¶
- A query belongs to one entity. Its conditions read the fields of that entity (or of a record linked to it through a to-one relationship).
- The comparison values are expressions. This makes queries dynamic: "assigned to the current user", "due before the date in this action variable".
- Conditions are translated into a database query. They are evaluated by the database, not record by record in nexxcite.
- The read limits of the user's roles are always added to every query. A query never shows a user records they may not see – and it is not the place to implement such limits.
- A condition nexxcite cannot evaluate (for example a clause without a field) is skipped. The result then contains more records, not fewer.
2. Creating and running a query¶
Queries are managed under Admin Studio → Automation → Queries, grouped by entity. You can also open them under Admin Studio → Entities in the group "Queries" of an entity.
- Select the entity group the query is for.
- Add a new query. The "Query Editor" opens with the name, description and tags at the top and the condition tree below.
- Build the conditions (sections 3 to 7) and save.
The entity of a query is fixed when it is created. "Execute" in the toolbar of the management view runs the selected query and shows the result in the default grid of its entity.
Queries can also be written directly into other definitions, for example into a Sub Query expression or a prompt. Such a query belongs to its definition and cannot be shared.
Permissions¶
| Permission | Allows |
|---|---|
| "Open Query Manager" | Opening Admin Studio → Automation → Queries |
| "Add Query" | Creating queries |
| "Edit Query" | Changing queries |
| "Delete Query" | Deleting queries |
| "Export Query Results" | Downloading the records of a grid or list as a file |
By default, these permissions are part of "System Administrator". See Roles and permissions.
3. Groups – combining conditions¶
The condition tree starts with a group. Every group has a gate:
| Gate | Meaning |
|---|---|
| "And" | Every condition in the group must be met |
| "Or" | One condition in the group is enough |
The buttons in the header of a group add conditions:
| Button | Adds |
|---|---|
| "Add clause" | A clause (section 4) |
| "Add group" | A nested group – needed for mixed conditions such as "A and (B or C)" |
| "Add related clause" | A related clause (section 5) |
| "Add DatePart Clause" | A date part clause (section 6) |
| "Add full text clause" | A full text clause (section 7) – only for entities that take part in full text search |
The "Move" menu of an entry offers "Move up", "Move down", "Move into group" and "Move out of group"; the minus icon removes the entry.
- A query without any condition returns all records of the entity (within the user's read limits).
- An empty group adds no condition.
- A newly added group has no gate yet. Choose "And" or "Or" – a group without a gate is ignored together with everything inside it.
4. Clauses¶
A clause compares a field of the record with a value.
| Part | Meaning |
|---|---|
| Left side | The field that is compared. A field of the query's entity or – through a to-one relationship – of a related record. Only field references are possible here. |
| Operator | How the field is compared (table below). A new clause starts with Equals. |
| Right side | The value the field is compared with, as an expression. Hidden for Empty and NotEmpty. |
Operators¶
| Operator | True when | Remark |
|---|---|---|
Equals |
The field equals the value | Whether upper and lower case matter depends on the database. With the usual settings of SQL Server they do not (our assessment – check your database collation). |
NotEquals |
The field differs from the value | |
LessThan, LessThanOrEqual, GreaterThan, GreaterThanOrEqual |
The field is smaller / greater than the value | Compared with the data type of the field – numbers numerically, dates chronologically |
Like |
The field matches a pattern, ignoring case | Add the wildcards yourself: % for any text, _ for one character. %printer% means "contains printer"; printer without % only matches the exact value. * is not a wildcard here. |
NotLike |
The field does not match the pattern | Same pattern rules |
Empty |
The field has no value | See "Empty values" below |
NotEmpty |
The field has a value | |
In |
The field equals one of the values of a list | The right side must return a JSON array, for example ["Open","New"] |
NotIn |
The field equals none of the values of the list |
The right side¶
- The right side is evaluated once, before the query runs, for the record the query is run from – the record of the form, the record an action runs on. It is not evaluated for each searched record.
- The result is converted to the data type of the field (see Expressions – data types). A text that is not a number, compared with a number field, becomes 0.
- A plain field expression on the right side compares two fields of the same searched record
("Changed on"
GreaterThan"Resolved on"). To compare with a field value of the record the query is run from, use an expression that is not a plain field reference – for example a text expression into which the field is inserted. - An equality comparison on a date and time field compares the exact moment, including the time. To compare by day, use a date part clause with the part "Date".
Lists for In and NotIn¶
| Right side returns | Effect |
|---|---|
A JSON array, for example ["A","B"] |
The field is compared with every value of the array |
| An empty value | The clause is ignored – it does not restrict the result |
| A text that is not a JSON array | In matches no record, NotIn matches all |
Lists usually come from expressions: the aggregation type Select of an
Aggregate or Sub Query expression, or the
rule SplitStringJsonArray of a Modify Value expression.
Empty values¶
Empty is true when the field has no value. For some field types, a stored default counts as empty
as well:
| Field type | Counts as empty |
|---|---|
| Text | No value or an empty text |
| Id or link | No value or the empty id |
| Date, date and time | No value or a date up to 1 January 1753 (the stored form of an empty date) |
| Number, yes/no | Only no value – 0 and "no" are not empty |
5. Related clauses¶
A related clause looks at the related records of each record: "has at least one open task", "has no invoice".
| Setting | Meaning |
|---|---|
| Relationship | The relationship from the query's entity to the related records |
| Operator | How many related records must meet the inner conditions (table below) |
| Number | The X of the operators that need one |
| Inner conditions | Conditions on the related records. They are always combined with And; add a group inside for Or. Without inner conditions, every related record counts. |
| Operator | True when |
|---|---|
AtLeastXContains |
At least X related records meet the conditions. "Has any" is AtLeastXContains with 1. |
AtMostXContains |
At most X related records meet them |
ExactlyXContains |
Exactly X related records meet them |
NotExactlyXContains |
Any number except X |
AllContains |
All related records meet them – also true when there are no related records at all |
NoneContains |
No related record meets them |
Example: Customers with at least two overdue invoices – entity "Customer", related clause with
relationship "Invoices", operator AtLeastXContains, number 2, inner clauses "Status" NotEquals
Paid and a date part clause "Due on" "More than X days ago" with number 0.
6. Date part clauses¶
A date part clause checks a part of a date or a time range relative to now. Use it instead of computing boundary dates yourself.
| Setting | Meaning |
|---|---|
| Field | A date or date and time field |
| Date part | "Date", "Date & Time", "Time", "Day of Year", "Day of Week", "Day of Month", "Week", "Month", "Quarter", "Year", "Hour" |
| Operator | Equals, NotEquals, LessThan, LessThanOrEqual, GreaterThan, GreaterThanOrEqual compare the extracted part with the right side. Is and IsNot check a time range. Empty and NotEmpty check whether the field has a value. |
| Right side | The value the part is compared with – a number for day, week, month, quarter, year and hour (0–23), a date for "Date" |
| Range and number | Only for Is / IsNot (table below) |
Time ranges with Is and IsNot¶
| Range | True when the date lies … |
|---|---|
| "Now" | on today's date |
| "Within the last X minutes", "… hours", "… days", "… weeks", "… months", "… years" | between X units ago and now |
| "More than X minutes ago", "… hours ago", "… days ago", "… weeks ago", "… months ago", "… years ago" | X units ago or earlier |
IsNot is the opposite. The sign of the number is ignored – both kinds of range always point into
the past. Dates in the future are never "within the last X days". "Within the last 0 days" matches
practically nothing.
Points to note¶
- Ranges are measured from the current time of the database server.
- "Week" is the ISO week number (1–53): weeks start on Monday, and week 1 is the week that contains the first Thursday of the year. 1 January can therefore belong to week 52 or 53 of the previous year, and 31 December to week 1. "Day of Week" depends on the first day of the week configured in the database (with the SQL Server default, 1 is Sunday).
EmptyandNotEmptyin a date part clause only test whether the field has a value at all. Unlike a normal clause, the stored form of an empty date (1 January 1753) does not count as empty here.Like,NotLike,InandNotInare offered in the list but add no condition.
Examples: "Created on" "Within the last X days", number 7; "Due on" part "Year"
Equals a text expression with the year; "Birthday" part "Month" Equals 12.
7. Full text clauses¶
A full text clause searches words in several fields at once – the condition behind a search box.
| Setting | Meaning |
|---|---|
| Fields | The fields that are searched. Only fields marked for full text search are offered; * searches all of them. |
| Operator | "Contains" or "Not Contains" |
| Value | An expression with the search words, typically an action variable or a prompt |
- Every word of the value must occur (the words are combined with And).
- If the value is empty, the clause is ignored and does not restrict the result.
- Full text clauses are only offered for entities that take part in full text search, and they need the full text search of the database.
8. Sorting¶
The query editor has no sort settings. The order of a listing comes from the place that shows it:
- A grid or list sorts by its own settings; the user can change the order with the column headers or with the sort options of a filter.
- Where nothing decides the order, the database returns the records in an order of its own, which can change.
Queries written with the AI assistant can contain sort rules. The query editor does not show them, and saving the query in the query editor removes them.
9. Where queries are used¶
| Place | How the query is used |
|---|---|
| Grids and lists (through navigation to search results), dashboard widgets such as query result grids, lists, cards and charts, catalogs | The query provides the records that are shown; grids load them page by page |
| Related items selector (form block) | Restricts the records that can be linked |
| Actions | An action can run once per record of a query; commands export query results or link records found by a query |
| Business rules | For example lookup validations (which records may be chosen) or rules that monitor how many records a query returns |
| Entity policies of roles | Read, edit, delete and add limits are written as queries (see Roles and permissions) |
| Expressions | Sub Query expressions and prompts with a selection list |
| Mailbox listeners | Finding the record an incoming e-mail belongs to |
| AI assistants | Stored queries with permitted tags can be run by an assistant |
The record a query is run from¶
The right sides of the conditions are evaluated for the record the query is run from. This record depends on the place:
- in a form or a form block: the record of the form,
- in a sub query expression: the record the expression is evaluated for,
- in an action that runs once per record of a query: no record – the conditions of that query cannot refer to one.
Where there is no such record, field values on the right side are empty.
10. Behaviour when something is missing¶
| Situation | Effect |
|---|---|
| A clause has no field, or the field no longer exists | The clause is skipped – the result is wider |
| A clause needs a right side but has none | The clause is skipped – the result is wider |
| The right side references an expression that no longer exists | The query fails with an error |
| A related clause has no relationship | The clause is skipped |
| A group has no gate | The group adds no condition – everything inside it is ignored |
| A full text value is empty | The clause is skipped |
Because missing parts widen the result rather than narrowing it, test a new or changed query with "Execute" before you use it in an action that changes or deletes records.
11. Performance hints¶
- Prefer conditions on fields of the entity itself. Conditions on fields of related records (to-one) and related clauses need additional joins or sub-selects in the database.
- Use date part clauses for time ranges instead of computing dates with expressions – the result is the same, the configuration is simpler.
Likewith a leading%(%printer%) cannot use a database index and becomes slow on large tables. For text search in large tables, use full text clauses.- Expressions that read data in a listing cost per row. A Sub Query or Aggregate expression shown in a grid column runs once per row. Store such values in a field with a business rule, or cache them, if they are needed often.
- Record counts on quick filters cost one count query per filter badge (see Filters).
12. Frequently asked questions¶
Does a query take the permissions of the user into account? Yes. The read limits of the user's roles are added to every query automatically.
Why does my query return all records?
Usually a condition is skipped (section 10): a clause without a field or right side, a group without
a gate, an empty full text value or an empty list for In. Also check that the right side returns a
value in the context the query runs in.
Can a query compare with a field of the record that is open in the form? Yes – with an expression on the right side that reads the field, for example a text expression with the field inserted. A plain field expression on the right side compares with a field of the searched record instead (section 4).
Why does Like with printer not find "Printer broken"?
In a query, Like does not add wildcards. Use %printer%.
Can I sort a query? Not in the query editor. The order comes from the grid, list or filter that shows the records (section 8).