Create Queries

This page explains how to build data queries using Simple Object Designer.

Accessing Query Creation

Open Expose Data > Data Queries
Click New to create a query
The Query Card opens

Query Card Settings

Basic Information

Field	Description	Example
Query Name	Internal identifier (letters and numbers only, max 27 characters)	"CustomerSalesSummary"
Caption	Display name	"Customer Sales Summary"
Query Category	Grouping	"Sales"
Available as API	Expose the query as an OData API endpoint
Usage Category	Where the query appears in Business Central (None, Lists, Tasks, ReportsAndAnalysis, Documents, History, Administration)

API Settings

The API Settings group is visible when Available as API is enabled:

Field	Description
Entity Name	Singular API identifier
Entity Caption	Display name for the entity
Entity Set Name	Plural collection name
Entity Set Caption	Display name for the collection
Group	API group used in the endpoint URL (defaults to the Object Prefix from setup)

When you enter an Entity Name, the caption and set names are suggested automatically. Entity Name and Entity Set Name must differ — the set name should be the plural form (e.g. customer vs customers).

Building Query Structure

Step 1: Add Root Data Item

In Query Elements, add first data item
Select the primary table
This is the root of your query

Step 2: Add Columns

For each data item:

Select the element and click the Columns action
Tick Included in Query for the fields you want in the output
Choose a Method (aggregation or date part) if needed

To join additional tables:

Add new data item (new elements are appended at the end, as a child of the element above)
Select the table
Configure link to parent
Set an appropriate SQL Join Type — LeftOuterJoin or FullOuterJoin, for example

Current limitation: one element per level. Queries currently support only one element at each level of the hierarchy. Attempting to add a data item as a sibling of an existing element fails with:
New elements must be added at the end of the query.
New elements can only be appended at the end, as a child of the element above them. The error appears regardless of which row position you try to insert at, and it does not mean you have hit a maximum table count.

There is no limit on the total number of tables — nine tables in a chain is fine. The constraint is purely that two tables cannot sit at the same level, which means several tables that all need to link back to the root by the same key cannot be added as direct siblings.

In practice, queries with more than two data items are currently unreliable and may produce this error even when the element is genuinely at the end. Improved multi-level support is actively being worked on.

Step 4: Configure Links

Links connect a data item to its parent:

On the child element, choose the SQL Join Type (defaults are fine for most cases; use LeftOuterJoin or InnerJoin)
Click the Filters action on the child element
Add a line with Filter Type Link: pick the field on the child element and the corresponding field on the parent table

Step 5: Arrange the Hierarchy (optional)

Queries support multiple levels. Multiple siblings under the same parent are not yet supported — see the limitation under Step 3. Use the actions on the Elements part to restructure:

Action	Effect
Move Up / Move Down	Reorder an element (and its children) among its siblings
Indent	Make the element a child of the element above it at the same level
Outdent	Move the element one level up, next to its current parent

Rules:

A query has exactly one top-level (root) element
Every child element needs at least one filter of type Link (or SQL Join Type CrossJoin)
After moving an element under a different parent, review its Link filters - they reference the previous parent table

Query Elements

The Elements Part

Shows data item hierarchy:

Root table at top
Child tables indented under their parent
Columns under each table

Note: multiple children sharing the same parent is not currently supported — only one element per level. This is planned for a future release.

Element Types

Type	Description
DataItem	A table in the query
Column	A field to include

Element Properties

Property	Description
Name	Element identifier
Caption	Display name
Data Type	For columns

Configuring Columns

The Columns Page

Select an element and click Columns. The Query Element Columns page lists every field on the element's table:

Column	Description
Field Number / Field Name	The field on the table
Class	Field class — FlowFields can slow the query down, so only include the ones you need
Type	The field type
Included in Query	Tick to include the field as an output column
Filter field in Query	Tick to make the field available as a filter for API consumers (this also includes the field)
Method	Aggregation or date-part method for the column (see below)

Output column names are derived from the field names (lower-cased, with spaces and special characters removed).

Field types that cannot be query columns

FlowFields are not supported in queries. This is a Business Central platform limitation, not an SOD one. The Columns page does list them (Class = FlowField), but including one can make the query — and any API exposing it — fail at consumption time. If you need FlowFields, expose the data through a regular table API endpoint instead, where they are supported and calculated automatically. See Create API Endpoints.
BLOB fields cannot be included. Doing so makes the query fail at runtime with Nav.Types.Exceptions.NavNotSupportedException ("NotSupported"). More generally, a NotSupported exception when running a query points at a field type that is not query-compatible — remove it and republish.

Calculated and computed columns are not possible

Calculated fields cannot be added to a query; Business Central does not support them in query objects. That includes:

Concatenating fields to build a value (an item number plus a variant suffix, for example)
Supplying a default value when a field is null
Anything resembling a SQL CASE expression

The only per-column transformations available are the documented Methods below plus the Add Count Column flag. Nothing else. This is not planned to change — queries are limited to what Microsoft provides in the platform.

Per-Column Methods

The Method column on a query column can transform that column's value:

Method	Description	Use For
(blank)	Raw value	Regular columns
Day	Day component of a date	Date breakdowns
Month	Month component of a date	Date breakdowns
Year	Year component of a date	Date breakdowns
Sum	Total	Amounts, quantities
Average	Mean value	Averages
Min	Minimum	Lowest value
Max	Maximum	Highest value

Day/Month/Year can only be used on Date or DateTime fields; Sum/Average/Min/Max only on numeric fields.

To get a row count, do not look for a Count method on a column — there isn't one. Enable the Add Count Column flag on the data item instead, which adds a count column to the result.

Configuring Links

Links are defined on the element's Filters page, as filter lines with Filter Type Link:

Link Configuration

Property	Description
Filter Field	Field on this (child) element
Field Value Field No.	Corresponding field on the parent table

Add multiple Link lines to join on more than one field. The parent field is automatically added to the parent element's included columns.

Support for more complex queries, including multiple Link lines, arrived in a recent SOD update. If adding a second Link produces an error, update SOD to the latest version.

When the linking field has the same name in both tables — ServiceCode as the primary key in one table and a foreign key in the other, for instance — include only one of the two identically named columns in the query output. The other exists purely to establish the link.

Including both makes publishing fail with A member of type QueryColumn with name '<fieldname>' is already defined in Query '<QueryName>'. If the columns are genuinely correct and the error persists, refresh symbols — a stale cache can produce the same message. See Publishing Failures.

Join Types

Type	Description
LeftOuterJoin	All parent records; missing children come back as blanks
InnerJoin	Only records that match on both sides
RightOuterJoin	All child records; missing parents come back as blanks
FullOuterJoin	Union of left + right outer
CrossJoin	Cartesian product (no link required)

Example Link

Parent: Customer
Child: Sales Invoice Header
Link: Customer.No. = Sales Invoice Header.Sell-to Customer No.

Adding Filters

Filters restrict which records an element includes. They are added on the same Filters page as links, using one of two filter types:

Filter Type	Description	Filter Value Example
Const	The field must equal a fixed value	`true`, `Posted`
Filter	The field must match a filter expression	`>=01012024`, `1000..2000`, `A*`

Steps:

Select the element and click Filters
Add a line: pick the field, choose Const or Filter, and enter the value
The value is validated as you enter it (for example, a Const on a Boolean field must be true or false, and Option/Enum values must match an existing member)

The filter is baked into the query and applies every time the query runs. API consumers can apply additional filters at request time.

Validating and Testing Queries

Before Publishing

Review query structure
Verify links are correct
Check column selection

Query definitions are validated when you publish. The most common publish errors are:

Query has no root element, cannot publish. — every query needs exactly one top-level data item. Add the root data item (Query Elements > first data item > primary table), or delete the incomplete query.
The query has no columns
A child element has neither a Link filter nor SQL Join Type CrossJoin
A CrossJoin element also has Link filters (remove one or the other)
Two identically named columns from a join (see Joining tables that share a field name)

An incomplete query blocks the entire publish — including unrelated API endpoints and features. When a publish error appears while you are working on a query, review the query definition for completeness before assuming a platform or symbol problem. Refreshing symbols and updating SOD will not fix a configuration gap.

This bites most often with queries created by accident: autosave can persist a query you started and never populated, and testing the Data Query function can leave an empty Query category behind. Delete anything you are not using.

Permission-set compile errors. A query that references custom feature tables can also fail at compilation with AL0185 (query missing from the permission set) and PTE0004 on the referenced tables. See Publishing Failures — including the option to send the .app file to support, renamed to .txt if mail filtering blocks it.

After Publishing

Access via API, or open the query in Business Central (if a Usage Category is set, the query can be found via Tell Me)
Verify data returns correctly
Check joins work properly

Common Query Patterns

Master-Detail

Customer with orders:

Customer
└── Sales Header
    ├── Customer No.
    ├── Order Date
    └── Amount

Summary with Aggregation

Sales by customer:

Customer
├── No.
├── Name
└── Sales Header (Sum Amount)

Multi-Level Join

Order with lines and items:

Sales Header
├── Header fields
└── Sales Line
    ├── Line fields
    └── Item
        └── Item fields

Source Table Limitations

Most tables in the 2000000xxx range — virtual and system tables — cannot be used as the source of a data query. They are not real tables in the database; the platform populates them on the fly, and they do not support the same operations as normal tables. Table 2000000138 (Table Information) is a concrete example.

Symptom when attempted: publishing crashes.

Workaround: create your own normal table, populate the fields you need into it (through a codeunit or a scheduled job), and point the data query at that table instead.

Working Out the Query Logic

SOD cannot show or export the SQL a query generates, and this is unlikely to change.

The reason is structural: the SQL string is not produced by SOD or by AL at all. Business Central query objects are compiled by the platform, and the actual SQL is generated by the runtime at execution time, taking security filters, company and table extensions into account. There is no design-time step where a ready-to-paste SQL string exists.

A practical workflow for iterating quickly: build and validate the join, filter and column logic first in a SQL tool such as Interactive SQL, and translate the result into the query card once you are satisfied. This avoids a publish cycle for every experiment. Business Central query joins map fairly directly onto SQL joins, so a query proven in SQL usually translates cleanly.

Best Practices

Query Design

Start with clear objective
Keep joins minimal — and remember only one element per level is supported today
Include only needed columns
Avoid FlowFields and BLOB fields entirely
Consider performance

Naming

Use descriptive names
Follow conventions
Document purpose

Testing

Test with various data
Verify aggregations
Check filter behavior

Housekeeping

Delete queries you started and did not finish — an empty or root-less query blocks every publish