How to expose Business Central data as an API and consume it with PowerApps

This video shows how easy you can expose any table in Business Central as an API using the Simple Object Designer. Then consume that API with PowerApps, all that in only 8 minutes. Check it out:

In this video, Erik demonstrates how to expose Business Central data as an API endpoint and consume it directly in Power Apps — all without writing a single line of code, thanks to the Simple Object Designer. He also covers the underlying AL code structure for API pages and shares a powerful tip about leveraging Microsoft’s read-only replica database for heavy read operations.

Exposing Business Central Data with the Simple Object Designer

Erik starts the demonstration inside the Simple Object Designer, which is part of an existing solution. The goal is to expose the Resources table (table 156) as an API endpoint that can be consumed externally.

Selecting the Table and Fields

The process is straightforward — select the source table, and the designer automatically suggests names for the API endpoint. From there, you pick the fields you want to expose:

  • Number and Name — the basic identifiers
  • Address and City — location data
  • Dimension Codes — for analytical grouping
  • FlowFields like Sales and Cost — computed values
  • Post Code — additional address information

Once the fields are selected, you simply close the field picker and hit Publish. The Simple Object Designer then writes the AL code, builds the extension app, and deploys it to the target environment — all automatically.

Understanding the API Page Structure

Behind the scenes, what gets generated is an AL API page. Here’s an example of what a custom API page looks like in AL code:

page 50140 APITest
{
    APIGroup = 'group';
    APIPublisher = 'hougaard';
    APIVersion = 'v2.0';
    ApplicationArea = All;
    Caption = 'apiTest';
    DelayedInsert = true;
    EntityName = 'salesheader';
    EntitySetName = 'salesheaders';
    PageType = API;
    SourceTable = "Sales Line";

    layout
    {
        area(content)
        {
            repeater(General)
            {
                field(documentNo; Rec."Document No.")
                {
                    Caption = 'Document No.';
                }
                field(documentType; Rec."Document Type")
                {
                    Caption = 'Document Type';
                }
                field("type"; Rec."Type")
                {
                    Caption = 'Type';
                }
                field(subtype; Rec.Subtype)
                {
                    Caption = 'Subtype';
                }
                field(vatCalculationType; Rec."VAT Calculation Type")
                {
                    Caption = 'VAT Calculation Type';
                }
                field(prepmtVATCalcType; Rec."Prepmt. VAT Calc. Type")
                {
                    Caption = 'Prepmt. VAT Calc. Type';
                }
                field(itemReferenceType; Rec."Item Reference Type")
                {
                    Caption = 'Item Reference Type';
                }
                field(icPartnerRefType; Rec."IC Partner Ref. Type")
                {
                    Caption = 'IC Partner Ref. Type';
                }
            }
        }
    }
}

Key properties to note in an API page:

  • PageType = API — this tells Business Central that this page is an API endpoint, not a UI page
  • APIGroup, APIPublisher, APIVersion — these properties define the URL path for the API endpoint
  • EntityName / EntitySetName — these define the OData entity names used when calling the API
  • DelayedInsert = true — a standard setting for API pages that ensures records aren’t inserted until all fields are populated
  • SourceTable — the Business Central table that provides the data

The extension is configured with the following app.json:

{
  "id": "276669e0-9a44-4938-a71c-728feaf4c7e8",
  "name": "APIsInBC24",
  "publisher": "Default Publisher",
  "version": "1.0.0.0",
  "platform": "1.0.0.0",
  "application": "23.0.0.0",
  "idRanges": [
    {
      "from": 50100,
      "to": 50149
    }
  ],
  "resourceExposurePolicy": {
    "allowDebugging": true,
    "allowDownloadingSource": true,
    "includeSourceInSymbolFile": true
  },
  "runtime": "12.0",
  "features": [
    "NoImplicitWith"
  ]
}

Consuming the API in Power Apps

With the API published, Erik switches over to Power Apps to demonstrate consumption. The steps are:

  1. Create a new app and choose to connect via the Business Central connector
  2. Select the connection — if you don’t already have one, you can add a connection to your Business Central instance
  3. Choose the data set — a data set in this context is a combination of your Business Central environment and company (e.g., the Cronus company on a sandbox)
  4. Select the table — scroll through the available endpoints and find the one you just exposed (in this case, “Resources”)

Power Apps auto-generates a working app with browse, detail, and edit screens. Erik demonstrates this is live data by going back into Business Central, creating a new resource called “Video Demo Guy,” and then refreshing the Power Apps view to see it appear immediately.

The Read-Only Replica Database

Erik wraps up with what he calls a “pretty cool” feature. When configuring an API endpoint in the Simple Object Designer, there’s a Read Only toggle option.

Here’s what’s happening behind the scenes: Microsoft maintains a hot live backup database that replicates your primary database in near real-time (just a few seconds behind). This replica sits on separate hardware — a different server and different storage.

By flipping the Read Only switch on your API endpoint, you redirect all read operations to this secondary replica database. This is incredibly useful when you need to:

  • Perform heavy read operations or pull large volumes of data
  • Run reporting queries without impacting production performance
  • Keep your primary system responsive while external applications consume data

The trade-off is that the data is read-only (no writes) and may be a few seconds behind, but for most reporting and data consumption scenarios, this is perfectly acceptable.

Summary

Exposing Business Central data as an API doesn’t have to involve writing complex AL code by hand. With the Simple Object Designer, it’s a point-and-click process: select your table, choose the fields, publish, and consume. The underlying AL code follows a well-defined pattern using PageType = API with the appropriate API properties. Power Apps connects seamlessly through the Business Central connector, and for read-heavy scenarios, you can leverage the read-only replica to keep your primary system running smoothly. The Simple Object Designer is available on AppSource — the demo version lets you add one field to a table and expose one API endpoint to try it out.

Picture of hougaard

hougaard