API Overview
Simple Object Designer can expose Business Central tables as OData API endpoints. This page introduces the API capabilities and concepts.
What Are Table APIs
Table APIs provide:
- RESTful endpoints for table data
- Standard CRUD operations (Create, Read, Update, Delete)
- OData protocol compliance
- External system access
Why Use APIs
Integration Scenarios
| Scenario | Benefit |
|---|---|
| Power BI reporting | Live data dashboards |
| Power Apps | Custom mobile/web apps |
| Power Automate | Workflow automation |
| Excel connections | Real-time data in spreadsheets |
| Third-party systems | ERP integration |
Advantages Over Web Services
| API Pages | Classic Web Services |
|---|---|
| Modern OData protocol | SOAP-based |
| Designed for integration | Page-centric |
| Better performance | Higher overhead |
| Industry standard | BC-specific |
API Concepts
Entity
An API entity represents a table exposed for external access:
- Maps to a BC table
- Has a unique entity name
- Provides CRUD operations
Entity Set
The collection endpoint for all records:
https://api.../companies({id})/customers(entity set)- Returns multiple records
- Supports filtering and pagination
Entity Name vs Entity Set Name
| Property | Purpose | Example |
|---|---|---|
| Entity Name | Single record operations | customer |
| Entity Set Name | Collection operations | customers |
OData Protocol
APIs use OData (Open Data Protocol):
- Standard REST conventions
- Query options ($filter, $select, $expand)
- JSON format
- Widely supported
Choosing the Right API Type
- To expose fields of a table — Item No. and Price to an external vendor, for instance — create a Table API under Expose Data > Expose data as web service APIs and include only the fields you want.
- Business Logic APIs are for calling codeunit methods. They are not a way to expose table fields.
What You Can Expose
Standard BC Tables
Any table in your environment:
- Customers, Vendors, Items
- Sales Orders, Purchase Orders
- Custom tables from other extensions
Tables that cannot be exposed
Not every standard table can be exposed. Some Microsoft tables contain fields with a protection level that makes them inaccessible to generated code, and publishing fails with AL0161 ... is inaccessible due to its protection level.
Table 8889 Sent Emails is a known example — its "Message Id" field is protected.
This restriction comes from Microsoft, not from SOD, and there is no workaround inside SOD. Deselecting the individual field does not help when the table itself is off limits.
SOD Feature Tables
Tables created with SOD features:
- Data Table features
- Related Table features
- Document features
Fields Selection
Choose which fields to include:
- Select relevant fields only
- Exclude sensitive data
- Optimize for consumers
- FlowFields are supported and automatically calculated when included (see performance note below)
API Grouping
Group related APIs for organization:
Standard Groups
| Group | Contains |
|---|---|
| Sales | Customer, Sales Order APIs |
| Purchasing | Vendor, Purchase Order APIs |
| Inventory | Item, Location APIs |
| Custom | Your custom APIs |
Benefits of Grouping
- Logical organization
- Easier discovery
- Access control potential
- Documentation structure
Accessing API Configuration
From Main Menu
- Open Expose Data > Expose data as web service APIs
- The API Endpoints page opens
From Features
- Open a Feature Card
- Click API EndPoints
- Configure API for that feature
API Endpoint Structure
Base URL
https://api.businesscentral.dynamics.com/v2.0/{tenant}/{environment}/api/hougaard/{objectPrefix}/v2.0/{objectPrefix} is the Object Prefix you configured in SOD Setup (e.g. SOD).
Full Endpoint
{base}/companies({companyId})/{entitySetName}?Company={CompanyName}Example
https://api.businesscentral.dynamics.com/v2.0/contoso/production/api/hougaard/SOD/v2.0/companies(12345)/customEntities?Company=CRONUSOperations Supported
| Operation | HTTP Method | Purpose |
|---|---|---|
| Read all | GET /entitySet | List records |
| Read one | GET /entitySet(id) | Get single record |
| Create | POST /entitySet | Insert new record |
| Update | PATCH /entitySet(id) | Modify record |
| Delete | DELETE /entitySet(id) | Remove record |
Security
Authentication
API access requires:
- Azure AD authentication
- Valid Business Central license
- Appropriate permissions
Authorization
Control via:
- BC permission sets
- API-level restrictions
- Field-level filtering
Limitations
What APIs Can't Do
- Execute business logic (use Business Logic APIs instead)
- Handle complex transactions
- Provide real-time push notifications
- Replace all integration needs
- Consume external APIs. SOD only exposes Business Central data outward; it cannot call or read a third-party API. See Limitations.
- Edit dimension values. Even on an editable OData API, dimensions cannot be edited through it, because they are stored as a Dimension Set ID representing a unique combination.
- Return Media/MediaSet content as Base64. Such fields return a GUID reference, not the encoded image. MediaSet is considerably more complex than Media, and converting either to Base64 is outside SOD's no-code scope — exporting image content as Base64, for example to show an item image in a Power App, requires AL development.
- Apply Business Central templates. A POST performs a standard CRUD insert; no template is applied. See API Configuration.
No $expand and no nested entities
Custom APIs generated by SOD do not support the OData $expand query option, and therefore do not support deep insert either.
- You cannot retrieve a record together with its related entities — a Customer with its Dimensions, say — in one request.
- This is a deliberate design decision, "a sacrifice made to stay Simple", not a bug or a configuration issue. There is no setting that enables it.
The standard Business Central v2.0 endpoints do show nested sub-table columns — linked dimension values, document lines and so on — because Microsoft hand-coded those APIs in AL. SOD exposes each entity independently.
To reproduce a linked structure: create a separate endpoint for the child/detail table and combine the datasets in the consuming tool on the shared key.
Feature table limitations
For DataRelated (anchored) feature tables, the inherited anchor/parent key does not project over the API — only explicitly defined feature fields and system fields are exposed. See Related Table Template for the mirror-field approach.
APIs generated from Features also differ from standalone API cards: they have no Force lowercase option, so their field names are case sensitive in OData. An error such as
OData: The property '<fieldname>' does not exist on type 'Microsoft.NAV.<entity>'.is a case mismatch. The fix is to name the fields on the feature in lowercase so they match the OData property names.
Never use the "add it to Web Services" workaround. Manually adding SOD-created tables to the standard Business Central Web Services page and consuming that OData link produces errors and is not a supported access method. Use the API endpoint, or the Power BI web-source method:
Json.Document(Web.Contents("https://api.businesscentral.dynamics.com/v2.0/<tenant>/<environment>/api/hougaard/SOD/v2.0/<entity>?company=<company-id>"))If the OData connector fails while the web source works, suspect a field-name casing mismatch first.
Performance Considerations
- Large data sets may paginate
- Complex queries may timeout
- Consider data volume and frequency
- FlowFields are automatically calculated for every record returned, which adds overhead. Only include FlowFields that consumers need
The 20,000-record limit
SOD-exposed APIs apply no filtering or limitation of their own. The only limits are the ones Business Central applies to every API call — including a default limit of approximately 20,000 records per call.
Consumers must page through larger result sets using @odata.nextLink. See Consuming APIs.