Simple Object Designer Documentation
Index

Queries as APIs

Queries can be exposed as OData endpoints for external consumption. This page explains how to configure and use query APIs.

Enabling API Access

In Query Configuration

  1. Open the Query Card
  2. Enable Available as API
  3. Configure API settings

API Settings

Setting Description Example
Entity Name Singular name customerSalesSummary
Entity Set Name Collection name customerSalesSummaries
Entity Caption Display name Customer Sales Summary
Entity Set Caption Collection caption Customer Sales Summaries
Group API grouping Sales

API Endpoint

URL Structure

https://api.businesscentral.dynamics.com/v2.0/{tenant}/{environment}/api/hougaard/{group}/v2.0/companies({companyId})/{entitySetName}?Company={CompanyName}

{group} is the Group value from the query's API settings. It defaults to the Object Prefix from SOD Setup (e.g. SOD). The API publisher (hougaard) and version (v2.0) are fixed.

Example

/api/hougaard/SOD/v2.0/companies({id})/customerSalesSummaries?Company={CompanyName}

Consuming Query APIs

GET Request

GET /api/hougaard/SOD/v2.0/companies({id})/customerSalesSummaries?Company={CompanyName}
Authorization: Bearer {token}

Response

{
  "value": [
    {
      "customerNo": "CUST001",
      "customerName": "Alpine Ski House",
      "totalAmount": 15000.00
    },
    {
      "customerNo": "CUST002",
      "customerName": "Blue Yonder",
      "totalAmount": 8500.00
    }
  ]
}

Query-Specific Behavior

Read-Only

Query APIs are read-only:

  • GET operations supported
  • POST, PATCH, DELETE not supported
  • Cannot modify data

Pre-Joined Data

Results are flattened:

  • Joined tables appear as single record
  • No need for $expand
  • Aggregations already calculated

Fixed Structure

Query structure is fixed:

  • Columns defined at design time
  • Cannot dynamically change structure
  • $select can limit returned fields

Filter Fields

Fields marked as Filter field in Query in the query designer are exposed as dedicated filter parameters. Marking a field as a filter field also includes it as an output column by default — untick Included in Query afterward if the field should only be filterable, not returned.

This setting matters only when the query is exposed as an API and you want URL-based (OData) filtering. It does not produce a filter dialog for users and has no effect on the query list in the Business Central UI.

ODataKeyFields and Record Keys

Query APIs generated by SOD are keyed automatically using the SystemId field — SOD adds a systemid column to the query for you, and there is no key field to configure.

The ODataKeyFields property is not supported on query-type APIs. This is a Business Central platform limitation; Microsoft does not currently provide it for queries.

Consequence: any consumer that requires ODataKeyFields — creating Dataverse virtual tables from the endpoint, for example — cannot be served by a query API. Use a Table API endpoint instead, since table APIs are keyed by their record key.

OData Options

Supported Options

Option Support
$select Yes
$filter Yes
$orderby Yes
$top Yes
$skip Yes
$count Yes
$expand No (already joined)

Filtering Examples

?$filter=customerNo eq 'CUST001'
?$filter=totalAmount gt 10000
?$filter=contains(customerName,'Alpine')

Sorting

?$orderby=totalAmount desc
?$orderby=customerName asc

Pagination

?$top=10&$skip=0
?$top=10&$skip=10

About @odata.nextLink: when paging with $top/$skip, the absence of a returned next-link is expected Business Central behaviour. Increment $skip yourself rather than relying on a next-link being present.

The 20,000-record limit applies to query APIs too. When a query returns more than roughly 20,000 rows, a single call silently returns a truncated set. Use OData paging to retrieve everything. See Consuming APIs.

Getting the full result set into Excel

Opening a query in Business Central and using Open in Excel from the page returns only the visible page of records — around a hundred rows — not the full result set of thousands.

To get the complete dataset, consume the query as an OData API endpoint instead: Data > Get Data > From OData Feed with the query API URL. The feed pages through all records.

See also the known issues with Open in Excel on query list pages in Consuming APIs.

Power BI Integration

Connecting

  1. Get Data > OData Feed
  2. Enter query API URL
  3. Authenticate
  4. Select entity

Benefits for Power BI

  • Pre-aggregated data
  • Simplified model
  • Better performance
  • Less transformation needed

Power Query (Excel)

Connecting

  1. Data > Get Data > OData
  2. Enter query API URL
  3. Authenticate
  4. Load data

Use Cases

  • Periodic data exports
  • Ad-hoc analysis
  • Report building

Comparison to Table APIs

When to Use Query APIs

  • Need joined data from multiple tables
  • Want pre-aggregated values
  • Need optimized reporting data
  • Simpler consumer code

When to Use Table APIs

  • Need CRUD operations
  • Working with single table
  • Need full record details
  • Require write access

Performance Considerations

Query Design Impact

  • More joins = slower response
  • Large result sets = more data transfer
  • Complex aggregations = more processing

Best Practices

  • Filter data appropriately
  • Limit columns to needed ones
  • Consider result set size
  • Use pagination for large data

Security

Same as Table APIs

  • OAuth 2.0 authentication
  • BC user permissions
  • API access controls

Data Exposure

Consider:

  • What data is combined in query
  • Who can access the API
  • Audit and compliance

Troubleshooting

No Data Returned

Check:

  • Query design (filters, joins)
  • Data exists in source tables
  • Permissions to view data

Missing Records

Check:

  • Does the result exceed ~20,000 rows? Business Central truncates a single call at that point — implement OData paging
  • Are you exporting from the page instead of the API? In-page Excel export returns only one page of records

Wrong Data

Check:

  • Join conditions correct
  • Aggregations configured right
  • Filters as expected

The API Errors After Adding a Field

Symptom: a query API that worked returns an error when consumed — in Excel or Power Query, for instance — after a new field was added to the query.

Check whether the new field is a FlowField. FlowFields are not supported in Business Central queries.

Solution: remove the FlowField column. If that value must be in the output, expose the source table through a regular table API endpoint instead, where FlowFields are supported and calculated automatically. See Create API Endpoints.

Cause: the query includes a field whose type is not query-compatible — a BLOB field, typically.

Solution: remove the incompatible field(s) from the query columns and the query runs.

Diagnostics: use Share details in Business Central to capture the full error, and send the .app file to support if the offending field is not obvious.

Performance Issues

Check:

  • Query complexity
  • Data volume
  • Index availability