11.1 Functional Table Types: Master Data, Supplemental & Setup Patterns

Microsoft Dynamics 365 Business Central Developer MB-820

Track your progress as you read

Finished sections get a check mark.

1 Chapter 1: Business Central Architecture & Platform Foundation

1.1 Business Central Architecture & Extensions Model 1.2 Customization Lifecycle: Cloud (SaaS) vs. On-Premises 1.3 Base Application, System Application & AppSource

2 Chapter 2: Development Environment & Configuration

2.1: Development Environment Setup: VS Code, AL: Go! Projects & Sandboxes 2.2: AL Configuration Files: app.json & launch.json Parameters 2.3: Multi-Root Workspaces & Project Dependencies

3 Chapter 3: Extension Lifecycle: Debugging, Deployment, Multilanguage & Maintenance

3.1 Debugging & Snapshot Debugging in Visual Studio Code 3.2 Extension Deployment & Schema Synchronization Modes 3.3 Multilanguage Development with XLIFF & Translation Files 3.4 Extension Maintenance, Deprecation & Breaking Changes

4 Chapter 4: Tables, Table Extensions & Data Modeling

4.1: Building Tables, Fields, Keys & Data Types 4.2: Table Extensions & Table Modifications 4.3: FlowFields, FlowFilters & Table Relations 4.4: Enums, Enum Extensions & Extensible Types

5 Chapter 5: Pages, Page Extensions & Role Centers

5.1: Page Types, Anatomy & Control Structure 5.2: Building Card, List & Document Pages 5.3: Page Extensions & Page Customizations 5.4: Role Centers, Headlines & Cue Data Modeling

6 Chapter 6: Reports & Document Generation

6.1 Report Architecture, DataItems & Dataset Design 6.2 Report Layouts: RDLC, Word, Excel & Multilanguage 6.3 Document Reports & Request Page Programming 6.4 Processing-Only Reports & Report Substitutions

7 Chapter 7: XMLports & Data Exchange

7.1 XMLport Architecture, Formats & Properties 7.2 XMLport Node Hierarchy, Triggers & Stream Manipulation

8 Chapter 8: Codeunits, Events, Triggers & Interfaces

8.1 Codeunit Types, Execution & SingleInstance Codeunits 8.2 Events, Publishers, Subscribers & Event Isolation 8.3 Interfaces, Polymorphism & Dynamic Codeunit Invocation 8.4 Installation & Upgrade Codeunits, Data Transfer & Archive

9 Chapter 9: Entitlements, Permissions & Queries

9.1 Permission Sets & Permission Set Extensions 9.2 Inherent Permissions, Inherent Entitlements & Security Scoping 9.3 Query Objects: Joins, Aggregation & Query as Report Data Source

10 Chapter 10: UI Customization, User Assistance & Onboarding

10.1 Profile Objects & Custom Page Views in AL 10.2 User Assistance: ToolTips, Teaching Tips & In-App Tours 10.3 Assisted Setup, Guided Experience & Onboarding Checklists

11 Chapter 11: Business Central Design Patterns & Data Process Model

11.1 Functional Table Types: Master Data, Supplemental & Setup Patterns 11.2 Document Standards: Header, Line, History & Journal Architectures 11.3 Data Process Model & Posting Routine Architecture

12 Chapter 12: AL Language Fundamentals, Data Types & Variables

12.1 AL Syntax, Variable Scope, Primitive & Complex Data Types 12.2 Expressions, Control Flow Statements & Built-in Functions 12.3 Access Modifiers (Public, Internal, Local) & Code Scoping

13 Chapter 13: Data Manipulation, Streams & Error Handling

13.1 Record Operations: Get, FindSet, ModifyAll & Filter Functions 13.2 Streams, File Management & TempBlob 13.3 Error Handling: ErrorInfo, TryFunctions & Collectible Errors

14 Chapter 14: Testing, Test Toolkit & Automation

14.1 Testing Architecture & the Test Toolkit 14.2 Authoring Test Codeunits & UI Handler Functions

15 Chapter 15: Telemetry, Performance Optimization & Analyzers

15.1 Telemetry Configuration & Azure Application Insights 15.2 Custom Telemetry Signals with Session.LogMessage & KQL 15.3 Performance Profiler, SetLoadFields & Query Optimization 15.4 AL Analyzers: CodeCop, AppSourceCop, UICop & PTECop

16 Chapter 16: REST Web Services & JSON in AL

16.1 HttpClient, HttpRequestMessage & REST API Consumption 16.2 JSON Processing: JsonObject, JsonArray, JsonValue & JsonToken

17 Chapter 17: API Pages, OData, Read Scale-Out & OAuth

17.1 API Pages (PageType=API), OData Web Services & REST Endpoints 17.2 Bound and Unbound OData Actions with [ServiceEnabled] 17.3 Read Scale-Out Architecture & DataAccessIntent Configuration 17.4 Microsoft Entra ID App Registrations & OAuth 2.0 Integration

11.1 Functional Table Types: Master Data, Supplemental & Setup Patterns

Key Takeaways Explain This Topic AI

Business Central classifies database tables into eight functional types: Master, Supplemental, Setup (Singleton), Subsidiary, Journal, Document (Header/Line), Ledger, and Register tables, each governing a specific stage in the enterprise data lifecycle.

Master tables (Customer, Vendor, Item, G/L Account) encapsulate core business entities using a single Code[20] primary key, default dimension synchronization, statistical FlowFields, and operational blocking enums.

Setup tables implement the Singleton design pattern with a single blank Code[10] primary key, storing global tenant parameters accessed via cached GetRecordOnce routines or single-instance codeunits.

Identifier generation is decoupled via the No. Series System Application module, utilizing GetNextNo for sequence incrementation, TestManual for user-entry validation, and AreRelated for relationship navigation.

Last updated: August 2026

11.1 Functional Table Types: Master Data, Supplemental & Setup Patterns

In Microsoft Dynamics 365 Business Central, the data tier is structured around standardized table paradigms refined over decades of enterprise ERP engineering. Understanding these functional table classifications and their associated AL design patterns is essential for the MB-820 certification exam. Mastering table taxonomy ensures developers create extensions that seamlessly integrate with standard application logic, maintain relational integrity, and follow platform conventions.

1. Business Central Functional Table Taxonomy

Every table in Business Central serves a distinct operational purpose within the business process lifecycle. Microsoft classifies tables into eight primary functional types: +---------------------------------------------------------------------------------------------------+

| FUNCTIONAL TABLE TAXONOMY IN AL |
+-------------------+-------------------------------------------------------------------------------+
| Master Tables | Core business entities (Customer, Vendor, Item, G/L Account, Fixed Asset). |
| Supplemental | Reference lookups & categories (Payment Terms, Shipping Agent, Unit of Measure)|
| Setup Tables | System singletons storing global parameters (Sales & Receivables Setup, G/L). |
| Subsidiary Tables | Sub-entities linked 1:N to master records (Customer Bank Account, Ship-to). |
| Journal Tables | Unposted batch processing & staging queues (Gen. Journal Line, Item Jnl Line).|
| Document Tables | Transient unposted business transactions (Sales Header/Line, Purch Header/Line)|
| Ledger Tables | Immutable posted audit entries (G/L Entry, Cust. Ledger Entry, Item Ledg Entry)|
| Register Tables | Posting batch audit trail envelopes (G/L Register, Item Register, Job Register)|
+-------------------+-------------------------------------------------------------------------------+
Detailed Breakdown of Table Classifications

Table Classification Primary Key Convention Typical Field Contents Mutability & Lifecycle Rules Standard Examples

Master Tables Code[20] (named "No." ) Entity names, addresses, posting groups, blocking flags, statistical FlowFields Persistent records modified throughout entity lifecycle. Deletion blocked if open ledger entries exist. Customer (18), Vendor (23), Item (27), G/L Account (15)

Supplemental Tables Code[10] or Code[20] (named "Code" ) Code, Description, calculation formulas, operational properties Static reference data configured during setup and referenced by master and document tables. Payment Terms (3), Unit of Measure (204), Shipping Agent (291)

Setup Tables Code[10] (named "Primary Key" , blank "" ) Number series codes, posting policies, default posting groups, feature toggles Singleton Pattern: Exactly one row per company. Created during extension installation and rarely deleted. Sales & Receivables Setup (311), General Ledger Setup (98)

Subsidiary Tables Compound: Master PK + Code[10] / Code[20] Sub-entity addresses, bank account numbers, packaging units Child records linked 1:N to a parent master record. Cascaded deletion or deletion validation enforced by parent. Customer Bank Account (287), Ship-to Address (222), Item Unit of Measure (5404)

Journal Tables Compound: "Journal Template Name" , "Journal Batch Name" , "Line No." Posting dates, account numbers, amounts, dimension set IDs, balancing accounts Transient staging buffers. Records are validated and deleted immediately upon successful ledger posting. Gen. Journal Line (81), Item Journal Line (83), Res. Journal Line (207)

Document Tables Header: "Document Type" , "No." ; Line: "Document Type" , "Document No." , "Line No." Header: Customer/Vendor info, dates, currency.; Line: Item/Account, quantities, unit prices, discounts Transient multi-line commercial transactions. Replaced by posted document history tables upon posting. Sales Header (36) / Sales Line (37), Purchase Header (38) / Purchase Line (39)

Ledger Tables Integer (named "Entry No." ) Transaction dates, document numbers, account numbers, financial amounts, quantities Immutable Truth: Records are strictly insert-only. Updating or deleting ledger entries via AL is prohibited. G/L Entry (17), Cust. Ledger Entry (21), Item Ledger Entry (32)

Register Tables Integer (named "No." ) "From Entry No." , "To Entry No." , "Creation Date" , "Source Code" , "User ID" Audit headers created per posting transaction batch. Links all ledger entries created in a single atomic posting. G/L Register (45), Item Register (46), Resource Register (240)

FlowFields vs. Physical Data Storage in Master Tables

Master tables avoid storing redundant transactional balances (such as total customer balance or item inventory on hand). Instead, they define virtual calculation fields known as FlowFields :

"Balance (LCY)" on Customer: Dynamically calculates the sum of "Amount (LCY)" from Cust. Ledger Entry where "Customer No." = FIELD("No.") .

"Inventory" on Item: Dynamically calculates the sum of "Quantity" from Item Ledger Entry where "Item No." = FIELD("No.") .

SumIndexField Technology (SIFT): FlowField calculations are accelerated in SQL Server through indexed views maintaining pre-aggregated subtotals, allowing real-time balance inquiries across millions of ledger entries in milliseconds without locking base tables.

Loading diagram... Business Central Functional Table Taxonomy & Flow Architecture

2. The Singleton Setup Table Design Pattern

Setup tables store global company-wide parameters, number series relationships, posting policies, and feature toggles. Because only one record ever exists per company in a setup table, developers must enforce singleton mechanics at the table, codeunit, and page levels.

Table Implementation Pattern table 50100 "Custom App Setup"
{
Caption = 'Custom App Setup';
DataClassification = CustomerContent;

fields
{
field(1; "Primary Key"; Code[10])
{
Caption = 'Primary Key';
DataClassification = CustomerContent;
}
field(2; "Default Batch Name"; Code[10])
{
Caption = 'Default Batch Name';
TableRelation = "Gen. Journal Batch".Name WHERE("Journal Template Name" = CONST('GENERAL'));
DataClassification = CustomerContent;
}
field(3; "Auto-Post Invoices"; Boolean)
{
Caption = 'Auto-Post Invoices';
DataClassification = CustomerContent;
}
field(4; "Document No. Series"; Code[20])
{
Caption = 'Document No. Series';
TableRelation = "No. Series";
DataClassification = CustomerContent;
}
field(5; "Posting Policy"; Enum "Custom Posting Policy")
{
Caption = 'Posting Policy';
DataClassification = CustomerContent;
}
}

keys
{
key(PK; "Primary Key")
{
Clustered = true;
}
}

procedure GetRecordOnce()
begin
if RecordHasBeenRead then
exit;
Get();
RecordHasBeenRead := true;
end;

var
RecordHasBeenRead: Boolean;
}
Singleton Implementation Rules in AL

Primary Key Definition: Always declare a single Code[10] field named "Primary Key" . The primary key value in the database must always remain blank string "" .

Direct Retrieval via Get() : To retrieve the singleton record in AL, invoke SetupRec.Get() with no arguments. Because "Primary Key" is blank, Get() defaults to looking for "" and loads the configuration row.

In-Memory Caching with GetRecordOnce : In high-frequency posting routines, calling SetupRec.Get() on every transaction triggers redundant database round-trips if not cached. Implementing an in-memory Boolean flag ( RecordHasBeenRead ) on the table variable or encapsulating access in a SingleInstance codeunit eliminates redundant SQL reads.

Card Page Properties for Singletons: Pages displaying setup tables must be configured with PageType = Card , InsertAllowed = false , DeleteAllowed = false , and an OnOpenPage trigger containing: trigger OnOpenPage()

begin
Rec.Reset();
if not Rec.Get() then begin
Rec.Init();
Rec.Insert();
end;
end;

Automated Initialization in Install Codeunits: Custom extensions should ensure the setup record exists upon deployment by initializing default values inside an Install Codeunit ( Subtype = Install ): codeunit 50100 "Custom App Install"

{
Subtype = Install;

trigger OnInstallAppPerCompany()
var
CustomAppSetup: Record "Custom App Setup";
begin
if not CustomAppSetup.Get() then begin
CustomAppSetup.Init();
CustomAppSetup."Auto-Post Invoices" := false;
CustomAppSetup.Insert();
end;
end;
}

3. Master Data Design Patterns

Master entities (such as Customer , Vendor , Item , G/L Account , or custom entities like Equipment Asset ) must implement standardized AL patterns for numbering, dimension inheritance, comment tracking, and operational blocking.

Pattern A: Modern Number Series Management ( No. Series Module)

Business Central assigns unique identifiers using the No. Series engine. In modern AL (Business Central 2023 Wave 2 and later), Microsoft refactored number series management into the No. Series System Application module using the No. Series codeunit interface ( Codeunit "No. Series" ). table 50101 "Equipment Asset"

{
Caption = 'Equipment Asset';
DataClassification = CustomerContent;

fields
{
field(1; "No."; Code[20])
{
Caption = 'No.';
DataClassification = CustomerContent;

trigger OnValidate()
var
CustomSetup: Record "Custom App Setup";
NoSeries: Codeunit "No. Series";
begin
if "No." <> xRec."No." then begin
CustomSetup.GetRecordOnce();
NoSeries.TestManual(CustomSetup."Document No. Series");
"No. Series" := '';
end;
end;
}
field(2; Description; Text[100])
{
Caption = 'Description';
DataClassification = CustomerContent;
}
field(3; "No. Series"; Code[20])
{
Caption = 'No. Series';
TableRelation = "No. Series";
DataClassification = CustomerContent;
Editable = false;
}
field(4; "Blocked"; Enum "Equipment Blocked Status")
{
Caption = 'Blocked';
DataClassification = CustomerContent;
}
field(5; "Global Dimension 1 Code"; Code[20])
{
CaptionClass = '1,1,1';
Caption = 'Global Dimension 1 Code';
TableRelation = "Dimension Value".Code WHERE("Global Dimension No." = CONST(1));
DataClassification = CustomerContent;

trigger OnValidate()
begin
ValidateShortcutDimCode(1, "Global Dimension 1 Code");
end;
}
field(6; "Global Dimension 2 Code"; Code[20])
{
CaptionClass = '1,1,2';
Caption = 'Global Dimension 2 Code';
TableRelation = "Dimension Value".Code WHERE("Global Dimension No." = CONST(2));
DataClassification = CustomerContent;

trigger OnValidate()
begin
ValidateShortcutDimCode(2, "Global Dimension 2 Code");
end;
}
}

keys
{
key(PK; "No.")
{
Clustered = true;
}
}

trigger OnInsert()
var
CustomSetup: Record "Custom App Setup";
NoSeries: Codeunit "No. Series";
begin
if "No." = '' then begin
CustomSetup.GetRecordOnce();
CustomSetup.TestField("Document No. Series");
"No. Series" := CustomSetup."Document No. Series";
if NoSeries.AreRelated(CustomSetup."Document No. Series", xRec."No. Series") then
"No. Series" := xRec."No. Series";
"No." := NoSeries.GetNextNo("No. Series", WorkDate());
end;
end;

local procedure ValidateShortcutDimCode(FieldNumber: Integer; var ShortcutDimCode: Code[20])
var
DimMgt: Codeunit DimensionManagement;
begin
DimMgt.ValidateDimValueCode(FieldNumber, ShortcutDimCode);
DimMgt.SaveDefaultDim(Database::"Equipment Asset", "No.", FieldNumber, ShortcutDimCode);
end;
}
Key Number Series Functions Reference

Function Signature Description & Operational Behavior

NoSeries.GetNextNo(SeriesCode, UsageDate) Retrieves and auto-increments the next available sequence number from the specified series line based on the work date.

NoSeries.TestManual(SeriesCode) Verifies that the No. Series line configuration permits manual number assignment ( Manual Nos. = true ). Throws an error if manual entry is disallowed.

NoSeries.AreRelated(DefaultSeriesCode, SelectedSeriesCode) Evaluates whether two series belong to the same relationship group (configured in No. Series Relationship ), allowing users to toggle between related series.

NoSeries.LookupRelatedNoSeries(DefaultCode, var SelectedCode) Opens a modal lookup page displaying all related number series for user selection.

Pattern B: Entity Blocking & Operational Integrity

Master records integrate a standardized Blocked Pattern to prevent inactive, suspended, or delinquent entities from being processed in transactional workflows:

Extensible Enums: Rather than simple Boolean flags, modern master records utilize extensible Enums (e.g., Enum "Customer Blocked" : " " , Ship , Invoice , All ).

Trigger-Level Validation: In transactional tables (such as Sales Line or Gen. Journal Line ), field OnValidate triggers and posting routines validate that the master record is not blocked before proceeding: Customer.Get("Sell-to Customer No.");

Customer.TestField(Blocked, Customer.Blocked::" ");
Pattern C: Comment Line Pattern

Rather than adding unindexed memo fields to master tables, Business Central centralizes unstructured text notes in Table 97 Comment Line :

Primary Key Structure: ("Table Name", "No.", "Sub No.", "Line No.") .

Child Page Integration: Master card pages expose comments via standard actions opening Page 124 "Comment Sheet" , filtered to "Table Name" = CONST(Customer) and "No." = FIELD("No.") .

Cascade Deletion: In the master table's OnDelete trigger, developer code must invoke CommentLine.SetRange("Table Name", CommentLine."Table Name"::Customer); CommentLine.SetRange("No.", "No."); CommentLine.DeleteAll(); to prevent orphaned comment records.

4. Dimension Integration & Default Dimensions on Master Data

Dimensions provide slice-and-dice financial and management reporting across the General Ledger without proliferating separate accounts in the Chart of Accounts.

Global Dimensions vs. Shortcut Dimensions

Global Dimensions (1 and 2): Stored directly as physical database fields on Master, Document, and Ledger tables ( "Global Dimension 1 Code" , "Global Dimension 2 Code" ). Configured with CaptionClass = '1,1,1' and CaptionClass = '1,1,2' so the Web Client dynamically renders the user-defined dimension names (e.g., Department , Project ).

Shortcut Dimensions (3 through 8): Stored virtually and retrieved via the centralized Dimension Set Entry engine.

Default Dimensions (Table 352 Default Dimension )

When a master record is created, default dimension values and posting rules are defined in Table 352 Default Dimension . Key fields include:

"Table ID" : The numeric database ID of the master entity (e.g., Database::Customer ).

"No." : The primary key code of the master record.

"Dimension Code" : The identifier of the dimension (e.g., 'DEPARTMENT' ).

"Dimension Value Code" : The default value (e.g., 'SALES' ).

"Value Posting" Enum: Dictates how transactions referencing this master record handle dimension rules:

Value Posting Option Enforcement Rule & Validation Behavior

" " (Blank) The default dimension value is proposed on documents and journal lines, but the user is permitted to change the value or clear it entirely.

Code Mandatory A dimension value code must be present on the transaction line before posting. The line can use the default value or any other valid dimension value for that dimension code.

Same Code The transaction line must use the exact "Dimension Value Code" configured on the master record. Any deviation throws a validation error upon release or posting.

No Code The transaction line must not contain any dimension value for this dimension code. If a value is entered, posting is blocked.

The Dimension Set Entry Engine (Table 480)

When master entities are referenced on transactional documents or journals, DimensionManagement.GetDefaultDimID calculates a combined integer pointer known as the Dimension Set ID :

Hash Tree Normalization: Table 480 Dimension Set Entry stores immutable dimension combinations. If 10,000 sales lines share the exact same combination of 5 dimensions, all 10,000 lines store the identical integer "Dimension Set ID" = 412 , rather than duplicating 50,000 child records in SQL Server.

High-Speed Joins: Analysis views and G/L reporting join directly on "Dimension Set ID" , delivering high-performance financial analytics.