The Query Builder
The Query Builder assembles a query visually — choosing tables, joining them, filtering and selecting columns.
Where to find it
Architect Panel → Data:
- Query Builder — building a query visually
- Custom Query Views — saved queries, then View Actions on a row
Architect Panel → Data:
- Datastores — the tables being queried
Start from the question
Written in a sentence. "Which cases opened this month are still unassigned" is a question you can build; "case data" is not, and it produces a query nobody can check.
Start narrow
One table, a filter, a few columns. Run it, confirm the rows are what you expect, then add. Building a five-table query and then discovering it returns nothing is an afternoon; building it in five steps is twenty minutes.
Joins are where it goes wrong
Two symptoms, both common:
- Too many rows — a join matching more than one row on the other side multiplies the result, and totals become wrong rather than absent.
- Too few rows — a join that requires a match drops records that have none.
Check the row count against something you know. If a query about cases returns more rows than you have cases, a join is multiplying.
Filter early
Restricting rows before joining is both faster and easier to reason about. A query filtered at the end has already done the work.
Return only the columns you need
Every extra column is data fetched, transferred and rendered. For a list somebody reads, five columns is usually enough; twenty is a spreadsheet nobody looks at.
Beware totals across joins
Summing a value from a table that has been multiplied by a join gives a total that is confidently wrong. It is the most consequential query mistake, because the number looks plausible.
Check any total against a small case you can count by hand.
Watch how long it takes
A query that takes several seconds in the builder will take longer with more data and several people running it. If it is slow while you are building it, it will not get better.
Check it against something you know
Pick a record you can verify by hand and confirm the query treats it correctly. That is worth more than reading the query, because it tests what it does rather than what you meant.
Worked example
A query joining cases to their notes returned four times as many rows as there were cases, and the resulting total was four times too high. Reducing the join to one note per case fixed it. The total was then checked against a case counted by hand.
Recommendations
- Write the question as a sentence first.
- Build one step at a time, running each.
- Check the row count against something you know.
- Verify any total by hand on a small case.