Design a Query
Goal
Build a query without writing JSON: pick the table, join the tables related to it, choose the columns it returns, narrow it with conditions, and — when the answer is a number rather than a list — group and measure it.
Where it is
Open your solution, choose Saved Queries, then New — or select a query and choose Edit. The designer fills the page rather than sliding in from the side, because a query has structure of its own: tables joined to tables, a condition tree, an ordered column list, measures. In a narrow panel a single condition wraps onto four lines and stops looking like one condition.
Choose Close to go back to the list. Nothing is saved until you choose Save.
The three tabs
They are three views of one query, not three editors. Whatever you change on one, the others show — so they can never disagree.
| Tab | What it is for |
|---|---|
| Simple | The everyday query: a table, its columns, a filter, a sort, a row limit. |
| Advanced | Everything, plus related tables, grouping with measures, and the options. |
| JSON | The document itself, for anything the form does not draw. |
| Results | What the query returns, as a table. This one is a view of the answer, not of the query. |
A query that already uses related tables, grouping or options opens on Advanced, so nothing it contains is hidden from you. If you switch such a query to Simple, the form says what it is not showing — and keeps it untouched.
Steps
1. Choose the table
Select the Table button and search. Everything else in the query is built from this table and the tables joined to it.
Changing the table clears the rest, and that is deliberate: columns, conditions and measures all name fields of the old table, and keeping them would produce a query that saves and then refuses to run, naming a field you can plainly see on screen.
2. Add related tables (Advanced)
Choose Add related table. The list is drawn from the relationships your environment already has, so you never type a field name. If nothing is related to your table yet the list opens and says so — a related table comes from a lookup column, so add one on either table and it appears here.
- One related record — following a lookup, such as a deal's account. This is the usual join and it is safe.
- Many related records — can repeat rows — going the other way, such as an account's deals. This returns the parent row once per child, which is what silently doubles a total. Use it when you mean it, and see Numbers that look too big below.
Each joined table gets:
- A name (its alias). Its columns are called
alias.columneverywhere in the query. You can rename it; the query designer warns you if something still names the old one. - A join setting — Keep rows with no match (the default) or Only matching rows. The default is the safe one: with Only matching rows, a deal that has no account disappears from the results entirely, and nothing on screen connects the disappearance to the join.
- Its own conditions, opened with the arrow at the start of the row. These narrow the related table inside the join, so rows with no match are still kept when the join is set to keep them. Putting the same condition in the main filter instead would throw those rows away.
A query can join up to eight tables, nested up to three deep.
3. Choose the columns
The left pane lists every column the query can name — the table's own and every joined table's, grouped by table and filtered as you type. Tick one and it appears on the right, which is what the query returns in order; the × removes one.
The order matters — it is the order the columns come back in, and the order a report prints them in. Drag a row by its grip to move it, the same gesture as an option set's values or an app's navigation. Without a mouse: focus the grip, press Space to pick the row up, the arrow keys to move it, and Space again to drop it.
Leave the right pane empty to return the table's own columns.
You do not have to know which table a column belongs to. Tick "Account · Industry" and the query writes it under the account join for you.
4. Narrow it
The Filter section is the same AND/OR condition builder as an Advanced Find or a view's filter, and it now offers every column in the query — the joined tables' as well as the table's own.
Each condition is one row: field, operator, value, and an × to remove it.
Is one of takes several values at once. On a choice column it opens a searchable list you can tick; on a text or number column you type a value and press Enter to add it, and each value becomes a chip you can remove. This is one condition with several values, not several conditions you have to OR together by hand.
Is at least and Is at most are there because writing "at least 5" as "greater than 4" is only right on whole numbers, and is quietly wrong on a decimal or a date.
5. Group and measure (Advanced)
Tick Return groups instead of records when the answer is a number rather than a list — which is what a chart and a KPI tile are drawn from.
- Measures — what to compute. Count needs no field (it counts records) but accepts one (then it counts the rows where that field is set). Sum and Average are offered numeric columns only. Each measure has a short name; that is the name the result comes back under, and what a chart binds to.
- Group by — up to two keys. Leave it empty for one total over the whole table. A date column also offers a period — by day, week, month, quarter or year.
- Conditions on the measures — see below.
6. Conditions on the measures (Advanced)
This is the question a filter cannot ask.
A filter decides which records go into a group. A condition on a measure decides which groups come out. "Customers who ordered more than ten times" cannot be written as a filter at all, because no single order knows how many its customer placed.
So: group by customer, measure Count as orders, then add a condition — orders is at
least 10.
Conditions can only name measures this query computes. If you find yourself wanting to compare a plain column here, that belongs in the filter.
7. Sort and limit
Sort by any column the query returns. A grouped query has no columns to sort by — its rows are groups — so it offers the grouping keys and the measure names instead, which are the only names it has.
Return at most caps the rows, or the groups. Sorting by a measure descending and capping at five is the "top five customers" query.
8. Options (Advanced)
- Remove duplicate rows — only matters with a join that has many records per row. It does not correct a total: a grouped query still counts the repeated rows, because removing duplicate rows and counting are different questions.
- Include the text of lookup columns — on by default, because a column of record ids is not a view of anything. Turn it off for an export keyed by id.
- Time zone — used for date periods. Without one they are computed in UTC, so a record made at nine in the evening lands on the next day, and a daily chart is wrong by a sliver every day in a way nobody notices.
9. Save, then preview
Save checks the query three ways: that it is a well-formed document, that it is a valid query, and that it would actually run here — every field, join, grouping and measure resolved against your environment's real tables.
Preview runs the query and opens the Results tab, which shows what came back as a table: one column per column the query returns, under its display name, in the order you put them in. Lookup columns show the record's name, not the id stored behind it.
- A record query is paged, 25 rows at a time, with the pager under the table and the total above it. A grouped query is not paged — it returns every group it finds — so its table is the whole answer.
- It runs the query as saved, with your own permissions. Save first: unsaved edits are not included, and the tab says so above the table. A query you have never saved has nothing stored to run, and Preview is unavailable until you save it once.
The JSON tab
The designer draws almost all of the query language, and the JSON tab is the rest — paging, and
anything a hand-written or AI-generated query uses that the form has no control for. It is the
parsware-query@1 schema, and the same document you
would hand an assistant asked to write a query for you.
Whatever the form does not draw is kept exactly as it is while you edit everything else. A designer that quietly dropped part of your query would be the worst possible way to find out it had one.
Numbers that look too big
A join in the "many related records" direction repeats the parent row once per match, and totals count the repeats: an account with five deals is counted five times.
Following a lookup — the usual direction — cannot do this. If a total looks inflated, check whether a join says many related records, and either measure the child table instead or ask the question the other way round.
Troubleshooting
- "This is not valid JSON, so the designer cannot show it." — the JSON tab has a missing comma or bracket. Fix it there; the form cannot show a document it cannot read, and showing an empty one would overwrite what you typed the moment you touched a control.
- "These are named by the query but no longer exist in it." — you removed a related table, or renamed one, and something still names it. Remove those, or add the table back under the same name.
- "Unknown field …" on save — the field belongs to a joined table and needs the join's name in front of it, or the join was removed.
- A condition on a measure is refused — it names something that is not one of this query's measures. Rename it to match, or move the condition into the filter if it is about a column.
- Adding a related table shows nothing to pick — nothing is related to this table yet, which the list says when it opens. A related table comes from a lookup column: see Create a relationship between entities.
- "This environment's relationships could not be loaded" — the designer could not read the environment's relationships, so it has no joins to offer. This is not something the query is doing wrong; close the designer and open it again.