Simple Object Designer Documentation
Index

Query Overview

Data queries allow you to create structured data extracts that join multiple tables. This page introduces query concepts and capabilities.

What Are Queries

Queries in Business Central:

  • Combine data from multiple tables
  • Apply filters and grouping
  • Produce flat result sets
  • Can be exposed as API endpoints

Why Use Queries

Table APIs vs Queries

Table APIs Queries
Single table Multiple tables joined
Full record structure Selected fields
CRUD operations Read-only
Individual records Aggregated data

Query Benefits

  • Denormalized data - Join multiple tables into one result
  • Aggregation - Sum, average, min/max, row counts, and date breakdowns (day/month/year)
  • Filtered datasets - Pre-filter for specific purposes
  • Reporting - Feed Power BI and other tools
  • Performance - Optimized data retrieval

Query Concepts

Data Items

Data items are the tables in your query:

  • First data item is the root
  • Additional data items join to it
  • Hierarchical structure

Links define how tables relate:

  • Join conditions
  • Join type: LeftOuterJoin, InnerJoin, RightOuterJoin, FullOuterJoin, or CrossJoin
  • Field relationships

Columns

Columns are the fields in the output:

  • Selected from data items
  • Can be renamed
  • Determines result structure

Filters

Element filters restrict which records are included:

  • Const - the field must equal a fixed value
  • Filter - the field must match a filter expression (ranges, wildcards, etc.)

Joins are defined on the same page as filters, using the Link filter type (see Links above).

Query Structure Example

Scenario: Customer sales summary

Query: Customer Sales Summary
├── Data Item: Customer (root)
│   ├── Column: No.
│   ├── Column: Name
│   └── Data Item: Sales Invoice Header (linked)
│       ├── Link: Sell-to Customer No. = No.
│       ├── Column: Posting Date
│       └── Column: Amount (summed)

Result:

Customer_No Customer_Name Posting_Date Total_Amount
CUST001 Alpine Ski House 2024-01-15 5000
CUST002 Blue Yonder 2024-01-16 3500

Accessing Queries

  1. Open Expose Data > Data Queries
  2. The Query List opens
  3. View existing queries or create new ones

The Query List

Columns

Column Description
Query Name Identifier
Caption Display name
Query Category Grouping
Available as API Whether exposed

Actions

Action Purpose
New Create new query
Edit Modify query
Delete Remove query

Query Use Cases

Reporting

Feed Power BI with pre-joined data:

  • Sales by customer/region
  • Inventory by location
  • Financial summaries

Data Export

Extract data for:

  • External systems
  • Excel analysis
  • Data warehouses

Integration

Provide denormalized data for:

  • Power Apps
  • Third-party tools
  • Custom applications

API Exposure

Queries can be exposed as OData endpoints:

  • Read-only access
  • Filtered by consumers
  • Returns flat result set
  • Fields can be marked as filter fields to expose them as dedicated filter parameters

Query Limitations

What Queries Can't Do

  • Write data
  • Execute logic
  • Handle transactions
  • Complex calculations
  • Include FlowFields — not supported in Business Central queries. Use a table API endpoint when FlowField values must be exposed.
  • Include BLOB fields — the query fails at runtime with a NotSupported exception
  • Contain calculated or computed columns — no concatenation, no default-when-null, no CASE-style expressions. Only the documented Methods and the Add Count Column flag are available.
  • Set ODataKeyFields — a Business Central platform limitation. Query APIs are keyed automatically by SystemId, which makes them unsuitable for consumers that require ODataKeyFields, such as Dataverse virtual tables. Use a Table API for those.

Design Constraints

  • BC query object limits
  • Only one element per level — multiple siblings under the same parent are not currently supported. There is no limit on the total number of tables, only on tables at the same level.
  • Queries with more than two data items are currently unreliable
  • New elements must be added at the end of the query
  • Virtual/system tables in the 2000000xxx range cannot be used as a source
  • A query must contain at least one element. An empty query causes a publish error and must be populated or deleted.
  • Join complexity limits
  • Performance considerations

Queries Suddenly Stopped Working

Symptom: data queries and web services that worked yesterday stop working overnight, with no change on your side.

Cause: if another app your app uses fields or tables from is uninstalled, that dependency removal forces your app to be uninstalled too — and with it, your data queries are deactivated.

Detection: after any operation involving other apps (installs, uninstalls, upgrades), check that your SOD-generated app is still installed in Extension Management.

Resolution: run Publish again to reinstall the app and restore the queries and web services.