bc-api-query-generator

name: bc-api-query-generator description: Generates API query objects for Business Central that expose data via OData/REST endpoints using the QueryType = API pattern. Creates queries with single or multiple dataitems (table joins), proper API properties, column mappings, aggregation methods (Sum, Count, Average, Min, Max), filters, DataItemLink joins (InnerJoin, LeftOuterJoin, CrossJoin), and DataItemTableFilter. Supports multi-version APIs, date filters, and dimension columns. Use when asked to create an API query, generate a query endpoint, build a read-only API from joined tables, expose aggregated data via API, create reporting queries, or implement OData query endpoints.

Business Central API Query Generator

Generates production-ready AL API query objects following Microsoft's API standards. Handles single-table and multi-table join patterns, aggregation, filtering, and proper OData endpoint configuration.

Overview

API queries (QueryType = API) expose read-only data via OData endpoints. Unlike API pages, queries:

• Support joining multiple tables via nested dataitems
• Support aggregation methods (Sum, Count, Average, Min, Max)
• Support filters exposed as OData query parameters
• Are read-only — no INSERT, UPDATE, DELETE
• Cannot be extended

This skill generates:

• API query with proper metadata properties
• Single or multi-dataitem layouts with join configuration
• Column mappings with camelCase naming
• Aggregation and filter elements
• File placed following feature-based folder convention

Advanced patterns and join examples: references/query-examples.md

Prerequisites

• AL workspace with established object ID range
• Source table(s) identified with join relationships
• Clear list of columns to expose
• API publisher, group, and version defined

Quick Start

query [ID] "[Prefix] [Entity] API"
{
    QueryType = API;
    APIPublisher = '[publisher]';
    APIGroup = '[group]';
    APIVersion = 'v2.0';
    Caption = '[entityCaption]', Locked = true;
    EntityName = '[entityName]';         // camelCase, singular
    EntitySetName = '[entityNames]';     // camelCase, plural

    elements
    {
        dataitem([dataItemName]; "[Source Table]")
        {
            column([fieldAlias]; "[Field Name]")
            {
                Caption = '[Caption]', Locked = true;
            }
        }
    }
}

Endpoint URL: ../[publisher]/[group]/[version]/companies({id})/[entitySetName]

API Query Properties

DataItem Pattern — Single Table

Use for simple read-only exposure of a single table:

query [ID] "[Prefix] [Entity] API"
{
    QueryType = API;
    APIPublisher = '[publisher]';
    APIGroup = '[group]';
    APIVersion = 'v2.0';
    Caption = '[entityCaption]', Locked = true;
    EntityName = '[entityName]';
    EntitySetName = '[entityNames]';

    elements
    {
        dataitem([dataItemName]; "[Source Table]")
        {
            // Primary key / identifier columns
            column([alias]; "[Key Field]")
            {
                Caption = '[Caption]', Locked = true;
            }

            // Business data columns
            column([alias]; "[Business Field]")
            {
                Caption = '[Caption]', Locked = true;
            }

            // Date columns
            column([alias]; "[Date Field]")
            {
                Caption = '[Caption]', Locked = true;
            }

            // Amount / numeric columns
            column([alias]; "[Amount Field]")
            {
                Caption = '[Caption]', Locked = true;
            }

            // Dimension columns (if applicable)
            column([alias]; "[Dimension Field]")
            {
                Caption = '[Caption]', Locked = true;
            }
        }
    }
}

DataItem Pattern — Multi-Table Join

Use for joining related tables. Nest dataitems to create SQL JOINs:

query [ID] "[Prefix] [Entity] API"
{
    QueryType = API;
    APIPublisher = '[publisher]';
    APIGroup = '[group]';
    APIVersion = 'v2.0';
    Caption = '[entityCaption]', Locked = true;
    EntityName = '[entityName]';
    EntitySetName = '[entityNames]';

    elements
    {
        dataitem([parentDataItem]; "[Parent Table]")
        {
            // Parent table columns
            column([alias]; "[Parent Field]")
            {
                Caption = '[Caption]', Locked = true;
            }

            dataitem([childDataItem]; "[Child Table]")
            {
                DataItemLink = "[Child FK Field]" = [parentDataItem]."[Parent PK Field]";
                SqlJoinType = InnerJoin;

                // Child table columns
                column([alias]; "[Child Field]")
                {
                    Caption = '[Caption]', Locked = true;
                }

                // Aggregation column example
                column([alias]; "[Amount Field]")
                {
                    Caption = '[Caption]', Locked = true;
                    Method = Sum;
                }

                // Filter element example
                filter([filterAlias]; "[Filter Field]")
                {
                    Caption = '[Caption]', Locked = true;
                }
            }
        }
    }
}

SqlJoinType Property

Sets the data item link type between data items to determine the records included in the resulting dataset. Always set on the lower (nested) data item. Works together with DataItemLink to combine records — except for CrossJoin, which requires DataItemLink to be blank.

SqlJoinType = InnerJoin;

Remarks:

• The SqlJoinType property is always set on the lower (nested) data item, never on the root data item.
• For LeftOuterJoin, InnerJoin, RightOuterJoin, and FullOuterJoin, the DataItemLink property establishes an "equal to" (=) comparison between fields.
• For CrossJoin, the DataItemLink property must be left blank — no field comparisons are made.
• When no SqlJoinType is specified, the default is LeftOuterJoin.

Choosing the right join type:

LeftOuterJoin (most common for APIs):
  Parent: [A, B, C]    Child: [A, B]
  Result: [A+A, B+B, C+null]  ← All parents included

InnerJoin:
  Parent: [A, B, C]    Child: [A, B]
  Result: [A+A, B+B]          ← Only matches

RightOuterJoin:
  Parent: [A, B]       Child: [A, B, D]
  Result: [A+A, B+B, null+D]  ← All children included

FullOuterJoin:
  Parent: [A, B, C]    Child: [A, B, D]
  Result: [A+A, B+B, C+null, null+D]  ← All records from both

CrossJoin (no DataItemLink):
  Parent: [A, B]       Child: [X, Y]
  Result: [A+X, A+Y, B+X, B+Y]  ← Every combination

DataItemLink Syntax

DataItemLink = "[ChildField]" = [ParentDataItemName]."[ParentField]";

• Links child dataitem to parent using foreign key relationship
• Multiple links separated by commas:

  DataItemLink = "Document Type" = [parentItem]."Document Type",
                 "Document No." = [parentItem]."No.";
  

DataItemTableFilter

Apply fixed filters to restrict data at the dataitem level:

dataitem([name]; "[Table]")
{
    DataItemTableFilter = "Document Type" = FILTER(Invoice | "Credit Memo");
    DataItemTableFilter = "Posting Date" = FILTER(<> 0D);
}

Aggregation Methods

Use the Method property on columns for aggregation:

column(totalAmount; "Amount (LCY)")
{
    Caption = 'TotalAmount', Locked = true;
    Method = Sum;
}

Rule: When using aggregation methods, non-aggregated columns become implicit GROUP BY fields.

Filter Elements

Expose filters as OData query parameters:

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

• Filters appear as query parameters in the OData URL
• Use for date ranges, document type filtering, and status filtering
• Filter elements do not produce output columns

Column Naming Conventions

Rules:

• Use camelCase for all column aliases
• Set Caption with Locked = true for API queries to prevent translation issues
• Use descriptive aliases for aggregated columns (e.g., totalSalesAmount instead of salesLCY)

API Query Design Workflow

1. Define purpose — What data needs exposure? Who consumes it? What aggregations?
2. Identify tables — Primary table and related tables for joins
3. Plan joins — Define DataItemLink relationships and SqlJoinType
4. Select columns — Pick fields to expose, decide on aggregations
5. Add filters — Expose date ranges and key filtering parameters
6. Allocate object ID — Query objects typically in dedicated range
7. Implement query — Create .Query.al file following naming convention
8. Test endpoint — Verify OData response structure and data

File Naming Convention

Follow the pattern: [Prefix][EntityName]API.Query.al

Examples:

• BCSStatLedgerEntryAPI.Query.al
• BCSCustomerSalesAPI.Query.al
• BCSItemInventoryAPI.Query.al

Place in feature folder: src/[Feature]/Query/ or alongside related feature files.

Checklist

Before completing API query generation:

• QueryType = API set
• API properties defined (publisher, group, version, entity names)
• Caption set with Locked = true
• All dataitems have meaningful names
• DataItemLink defined for multi-table joins
• SqlJoinType specified for each nested dataitem
• DataItemTableFilter applied where needed
• Column aliases use camelCase
• Column Captions with Locked = true
• Aggregation Methods applied where needed
• Filter elements added for date ranges and key parameters
• File follows naming convention: [Prefix][Entity]API.Query.al

References

For complete examples:

• references/query-examples.md — Full working examples (single table, multi-table joins, aggregation patterns, dimension queries)

External Resources

• API Query Type — Microsoft Docs
• SqlJoinType Property — Join type reference
• Linking and Joining Data Items — Join patterns
• Query Object — Query fundamentals
• Query Properties — Full property reference