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

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

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.

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

1. 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 "".
2. 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.
3. 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.
4. 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;
   

5. 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

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:

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.