Business Central API Query: Complete Beginner Guide

Dynexal • Beginner to Intermediate • 14 min read

Business Central API Queries let you expose read-only data through a versioned API endpoint. They are especially useful when an external application needs data combined from multiple Business Central tables, calculated values, or a reporting-style dataset instead of a simple record from one API page.

What is an API Query in Business Central?

A Business Central Query object retrieves data from one or more tables and combines it into rows and columns. When you set QueryType = API;, the query becomes an API web service endpoint rather than a normal query intended for application UI scenarios.

Business Central has Normal and API query types. An API Query is read-only and cannot be displayed directly in the Business Central user interface. It is a strong fit when an integration needs joined or aggregated data.

API Query vs API Page

Feature	API Page	API Query
Primary purpose	Expose business entities	Expose combined/query data
Multiple data sources	Not the main pattern	Yes, queries can join data sources
Read	Yes	Yes
Create/Update/Delete	Supported by API page patterns	No, read-only
UI display	No	No
Aggregation	Not the main purpose	Strong fit for sums and grouped datasets

Use an API Page when you need an entity endpoint for integration operations. Use an API Query when the external consumer mainly needs a read-only dataset assembled from one or more sources.

When should you use an API Query?

Expose customer sales summaries to a reporting application.
Join Customer and Customer Ledger Entry data.
Return calculated totals without making several API calls.
Provide a read-only integration endpoint for a dashboard.
Expose a stable versioned dataset to middleware or analytics tools.

Instead of calling a customer API and then making separate calls for ledger entries, an API Query can return customer information together with an aggregated sales value in one read-oriented endpoint.

Basic API Query structure

query 50200 "Customer Sales API"
{
    QueryType = API;
    APIPublisher = 'dynexal';
    APIGroup = 'integration';
    APIVersion = 'v1.0';
    Caption = 'customerSales', Locked = true;
    EntityName = 'customerSale';
    EntitySetName = 'customerSales';

    elements
    {
        dataitem(Customer; Customer)
        {
            column(id; SystemId)
            {
                Caption = 'id', Locked = true;
            }
            column(number; "No.")
            {
                Caption = 'number', Locked = true;
            }
            column(name; Name)
            {
                Caption = 'name', Locked = true;
            }
        }
    }
}

The important API properties define how the query is exposed. QueryType makes it an API Query, while APIPublisher, APIGroup, APIVersion, EntityName and EntitySetName define the API metadata and route.

Understanding the main properties

QueryType

QueryType = API;

This tells Business Central that the query is an API Query.

APIPublisher

APIPublisher = 'dynexal';

Defines the publisher portion of a partner-created API route.

APIGroup

APIGroup = 'integration';

Defines the API group used in the endpoint route.

APIVersion

APIVersion = 'v1.0';

Defines the version of the API exposed by the query. Multiple versions can be specified when an API needs to support more than one version.

EntityName and EntitySetName

EntityName = 'customerSale';
EntitySetName = 'customerSales';

These properties provide the entity naming used by the API endpoint and service metadata.

Joining multiple tables

The biggest advantage of an API Query is its ability to combine data from multiple data sources. For example, Customer can be the parent dataitem and Customer Ledger Entry can be linked to it.

query 50200 "Customer Sales API"
{
    QueryType = API;
    APIPublisher = 'dynexal';
    APIGroup = 'integration';
    APIVersion = 'v1.0';
    Caption = 'customerSales', Locked = true;
    EntityName = 'customerSale';
    EntitySetName = 'customerSales';

    elements
    {
        dataitem(Customer; Customer)
        {
            column(id; SystemId)
            {
                Caption = 'id', Locked = true;
            }
            column(number; "No.")
            {
                Caption = 'number', Locked = true;
            }
            column(name; Name)
            {
                Caption = 'name', Locked = true;
            }
            dataitem(LedgerEntry; "Cust. Ledger Entry")
            {
                DataItemLink = "Customer No." = Customer."No.";
                SqlJoinType = LeftOuterJoin;
                column(totalSalesAmount; "Sales (LCY)")
                {
                    Caption = 'totalSalesAmount', Locked = true;
                    Method = Sum;
                }
            }
        }
    }
}

Here, DataItemLink connects ledger entries to the current customer. SqlJoinType = LeftOuterJoin can keep the parent customer in the result even when there is no matching child record.

Aggregation with Method = Sum

Queries can perform calculations such as sums and averages. A common integration scenario is returning a total sales amount per customer.

column(totalSalesAmount; "Sales (LCY)")
{
    Caption = 'totalSalesAmount', Locked = true;
    Method = Sum;
}

Aggregation can reduce the number of API calls required by an external system. Always test grouping and join behavior with your actual data before relying on totals in production.

Filtering API Query data

API Queries can expose filters as part of the query definition. For example:

filter(postingDateFilter; "Posting Date")
{
    Caption = 'postingDateFilter', Locked = true;
}

Filters are useful when an integration needs controlled filtering of a dataset. Test the deployed endpoint to confirm the exact request syntax supported by your Business Central version.

API endpoint structure

Partner-created Business Central APIs use a route based on the publisher, group and version. In simplified form:

{Base URL}/{Environment}/api/{publisher}/{group}/{version}/{endpoint}

For example:

https://api.businesscentral.dynamics.com/v2.0/{environment}/api/dynexal/integration/v1.0/companies({companyId})/customerSales

The exact base URL and environment information depend on the Business Central environment and tenant. The company context is commonly included in the endpoint when accessing business data.

Testing an API Query

Publish the AL extension to a development or sandbox environment.
Confirm that the API Query compiles and is available.
Obtain the required OAuth access token.
Call the endpoint with Postman, Insomnia, curl or your integration application.
Inspect the JSON response.
Verify joins, totals, filters and empty-result behavior.
Test with realistic data volumes.

For general API testing and authentication concepts, see the Business Central API Integration guide and OAuth 2.0 Authentication guide.

Example GET request

GET https://api.businesscentral.dynamics.com/v2.0/{environment}/api/dynexal/integration/v1.0/companies({companyId})/customerSales
Authorization: Bearer {access-token}

A successful response is JSON representing the query dataset. The exact metadata and property names depend on your query definition.

API Query vs OData query

Do not confuse an API Query object with a normal Query object published as an OData web service. A normal query can be registered and published as a web service, while an API Query is specifically designed to generate an API endpoint.

API Query vs normal Query

Normal Query: useful for application and reporting scenarios and can also be published as an OData web service.
API Query: generates a versioned API endpoint and is read-only.
Normal Query: can be used as a data source for other Business Central development scenarios.
API Query: is intended for integration consumers and cannot be displayed directly in the UI.

Common API Query mistakes

1. Forgetting QueryType

If you want an API Query, make sure the object explicitly uses:

QueryType = API;

2. Incorrect API route

Check APIPublisher, APIGroup, APIVersion, EntityName and EntitySetName. A mismatch can cause endpoint or 404 errors.

3. Incorrect DataItemLink

When joining tables, verify that the fields represent the intended relationship. An incorrect link can produce missing or unexpectedly duplicated data.

4. Assuming an API Query supports updates

API Queries are read-only. If your integration needs create, update or delete operations, consider an appropriate API page or another supported integration design.

5. Ignoring aggregation behavior

When using Sum or other calculations, test grouping and joins carefully. Incorrect joins can make totals larger than expected.

6. Returning too much data

Large datasets can increase response time and integration load. Expose only the fields needed by the consumer and design filters carefully.

Performance best practices

Return only required columns.
Use sensible filters for large datasets.
Keep joins focused on meaningful relationships.
Test queries with realistic production-like volumes.
Avoid forcing every consumer to download a huge dataset.
Monitor API response times and integration failures.
Use a dedicated version when making breaking API changes.

Security considerations

Use Microsoft Entra ID and OAuth-based authentication for Business Central online integrations.
Grant only the permissions required by the integration.
Never place client secrets or access tokens in source code.
Protect integration logs from exposing tokens or sensitive business data.
Validate the endpoint and environment before sharing it with external consumers.

Real-world example: e-commerce sales dashboard

Imagine an e-commerce platform needs a daily customer sales dashboard. Instead of making separate requests for every customer and ledger entry, Business Central can expose a read-only API Query that combines customer information with aggregated sales data.

Customer is the main dataitem.
Customer Ledger Entry is linked to Customer.
Sales amount is aggregated with Method = Sum.
The API Query exposes a versioned endpoint.
The dashboard calls the endpoint with OAuth authentication.
The external application displays the returned dataset.

Recommended architecture

External Dashboard / Middleware
            |
            | GET + OAuth
            v
Business Central API Query
            |
      +-----+-----+
      |           |
  Customer   Ledger Entries
      |           |
      +-----+-----+
            |
       Aggregated data

API Query checklist

Define the consumer's data requirement.
Decide whether an API Page or API Query is the better fit.
Define the required tables and relationships.
Create the query with QueryType = API.
Set publisher, group and version properties.
Define entity names and columns.
Add joins and aggregation carefully.
Publish to a sandbox.
Test authentication and endpoint discovery.
Test realistic filters and data volumes.
Document the endpoint contract.
Version the API before making breaking changes.

FAQ

Can an API Query update Business Central records?

No. API Queries are read-only.

Can an API Query join multiple tables?

Yes. Joining data from different data sources is one of the main reasons to use an API Query.

Can an API Query calculate totals?

Yes. Query objects support calculations such as sums and averages, including aggregation methods such as Sum.

Can an API Query be shown on a Business Central page?

No. API Query objects generate web service endpoints and aren't used to display data directly in the user interface.

Should I use an API Query for every integration?

No. Choose the object based on the requirement. API pages are generally better for entity-based CRUD integrations, while API Queries are useful for read-only, combined or aggregated datasets.