4.1: Building Tables, Fields, Keys & Data Types

4.1 Building Tables, Fields, Keys & Data Types

In Microsoft Dynamics 365 Business Central, tables represent the structural foundation of the application data model. Every business entity—from Master Data (such as Customers and Items) to Transactional Headers and Lines (such as Sales Headers and Sales Lines)—is declared as a strongly typed AL table object. Mastering table architecture, data types, indexing mechanics, and trigger lifecycles is a fundamental prerequisite for passing the MB-820 certification exam.

1. Table Object Architecture & Physical Storage

When an AL table object is compiled and published in an extension, the Business Central Server (runtime service tier) maps the table definition directly to a physical Microsoft SQL Server (or Azure SQL Database) table.

Table Declaration Syntax

table 50100 "Reward Level"
{
    DataClassification = CustomerContent;
    Caption = 'Reward Level';
    LookupPageId = "Reward Level List";
    DrillDownPageId = "Reward Level List";
    DataPerCompany = true;

    fields
    {
        field(1; "Level Code"; Code[20])
        {
            Caption = 'Level Code';
            DataClassification = CustomerContent;
            NotBlank = true;
        }
        field(2; "Minimum Points"; Decimal)
        {
            Caption = 'Minimum Points';
            DataClassification = CustomerContent;
            DecimalPlaces = 0 : 2;
            MinValue = 0;
        }
        field(3; "Discount Percentage"; Decimal)
        {
            Caption = 'Discount Percentage';
            DataClassification = CustomerContent;
            MinValue = 0;
            MaxValue = 100;
            DecimalPlaces = 0 : 5;
        }
        field(4; "Active"; Boolean)
        {
            Caption = 'Active';
            DataClassification = CustomerContent;
            InitValue = true;
        }
    }

    keys
    {
        key(PK; "Level Code")
        {
            Clustered = true;
        }
        key(PointsKey; "Minimum Points", "Active")
        {
            IncludedFields = "Discount Percentage";
        }
    }

    trigger OnInsert()
    begin
        TestField("Level Code");
    end;

    trigger OnModify()
    begin
    end;

    trigger OnDelete()
    begin
    end;

    trigger OnRename()
    begin
    end;
}

Table Types in AL

The TableType property specifies the physical behavior and storage mechanism of the table object:

Exam Watch: When a table is declared with TableType = Temporary, all instances of that table across the entire AL project behave as temporary records without writing to SQL. However, if a standard table (TableType = Normal) is declared as a variable in AL code, you can make that specific variable temporary using the Temporary keyword: var TempCustomer: Record Customer temporary;.

2. Field Data Types, ID Ranges & Properties

Fields define the data columns of a table. In Business Central, field numbers determine licensing boundaries, schema stability, and extension namespaces.

Field Number Ranges

• 1 to 49,999: Base Application and Microsoft system fields.
• 50,000 to 99,999: Per-Tenant Extensions (PTE) created for specific customer environments.
• 1,000,000 to 69,999,999: ISV / AppSource publisher ranges allocated by Microsoft Partner Center.
• 2,000,000,000 to 2,147,483,647: System fields automatically generated by the platform (e.g., SystemId, SystemCreatedAt, SystemCreatedBy, SystemModifiedAt, SystemModifiedBy, SystemRowVersion).

Core AL Data Types

Essential Field Properties

1. DataClassification: Mandatory for every field under modern AL rules. Options include:

   • CustomerContent: Data entered by users during business operations (e.g., customer names, transaction amounts).
   • EndUserIdentifiableInformation (EUII): Personally Identifiable Information (PII) such as email addresses, home addresses, phone numbers, or national IDs.
   • AccountData: Billing, bank account, and payment information.
   • EndUserPseudonymousIdentifiers (EUPI): User IDs, GUIDs, or telemetry identifiers.
   • SystemMetadata: System-generated configuration and technical metadata.
   • OrganizationIdentifiableInformation (OII): Public company numbers, VAT registration numbers.
   • ToBeClassified: Temporary placeholder during initial development; rejected for AppSource by AS0016 (Fields of field class 'Normal' must use the DataClassification property and its value should be different from ToBeClassified).

2. ExtendedDataType: Enhances UI presentation and client behavior:

   • PhoneNo: Formats as telephone link and triggers telephony integration on mobile devices.
   • URL: Renders as clickable hyperlink in the web client.
   • Email: Renders as clickable mailto: link.
   • Barcode: Formats with barcode font.
   • Masked: Obscures characters on UI input (for passwords/tokens).
   • Ratio: Displays a graphical progress bar or ratio indicator.

3. Validation & Numeric Constraints:

   • NotBlank = true: Prevents the user from entering an empty value in UI pages.
   • MinValue / MaxValue: Sets boundary validation limits at the data layer.
   • DecimalPlaces = 0:5: Sets minimum and maximum decimal precision displayed on pages and stored in database operations.
   • AutoIncrement = true: Generates automatically increasing 32-bit/64-bit integer values on record insertion. Only allowed on primary key fields of type Integer or BigInteger.

3. Keys, Indexes & SIFT Architecture

Keys define the ordering and indexing of table records in SQL Server. AL supports primary keys, secondary keys, covering indexes, and SIFT (SumIndexFields Technology) indexes.

Primary Key (PK)

• Every table must have exactly one primary key defined as the first key in the keys block.
• The primary key enforces row uniqueness.
• In Business Central, the primary key is Clustered = true by default. This physically arranges the table rows on SQL Server data pages in ascending order of the primary key fields.
• Primary key fields cannot be modified or dropped in table extensions.

Secondary Keys & SQL Index Optimization

Secondary keys create non-clustered indexes on the SQL Server table to accelerate search, filtering, and sorting operations.

key(CustSearchKey; "Country/Region Code", "City", "Name")
{
    Unique = false;
    IncludedFields = "Balance (LCY)", "Contact";
}

Key Properties Breakdown

• Unique: When set to true, the SQL Server engine enforces a unique constraint on the key columns, preventing duplicate combinations across records.
• IncludedFields (Covering Index): Adds specified non-key fields to the leaf level of the non-clustered index tree. When a query filters by Country/Region Code and selects Contact, SQL Server fulfills the entire query from the index pages without performing an expensive clustered index key lookup (bookmark lookup) against the physical table data page.
• SumIndexFields: Designates numeric fields (Decimal, Integer, BigInteger) for real-time aggregate calculation via SIFT (SumIndexFields Technology). SQL Server maintains indexed views for these aggregates, enabling instantaneous calculations of totals across millions of records.
• MaintainSQLIndex / MaintainSIFTIndex: Setting either property to false disables the creation of physical SQL Server index structures, saving write performance when the key is used solely for AL in-memory sorting.

4. Table Triggers, Field Triggers & Validation Order

Business Central enforces business rules through a structured event and trigger execution hierarchy.

Table-Level Triggers

trigger OnInsert()
begin
    // Executed before a new record is committed to the database
    // Rec represents the record being inserted
end;

trigger OnModify()
begin
    // Executed before an existing record is updated in the database
    // xRec contains the record state before modification
end;

trigger OnDelete()
begin
    // Executed before a record is removed
    // Used to verify referential integrity and delete child lines
end;

trigger OnRename()
begin
    // Executed when primary key fields are modified
    // xRec holds the OLD primary key, Rec holds the NEW primary key
end;

The RunTrigger Parameter in AL Data Operations

When inserting, modifying, or deleting records in AL code, the developer explicitly controls trigger execution:

// Trigger bypassed: OnInsert trigger does NOT run
RewardLevel.Insert(); 

// Trigger executed: OnInsert trigger runs
RewardLevel.Insert(true); 

// Direct field assignment: OnValidate does NOT run
RewardLevel."Minimum Points" := 500; 

// Field validation: OnValidate trigger runs
RewardLevel.Validate("Minimum Points", 500); 

Exam Trap: Rec.Insert(false) bypasses the AL OnInsert trigger written inside the table object. However, platform-level telemetry events, SQL constraints, and event subscribers bound to OnDatabaseInsert continue to fire at the server tier. Always use .Validate() and Insert(true) when creating business transactions to prevent data corruption.