4.2: Table Extensions & Table Modifications

4.2 Table Extensions & Table Modifications

In modern Dynamics 365 Business Central development, the base application source code is immutable. To customize existing standard tables (such as Customer, Vendor, Item, or Sales Line), developers create tableextension objects. Table extensions allow you to introduce custom fields, build new secondary keys, modify permissible field properties, and hook custom logic into validation and table triggers.

1. Table Extension Architecture & Companion Tables

Under the non-intrusive extension model, Business Central implements table extensions using the Companion Table Pattern in Microsoft SQL Server.

How Companion Tables Work Under the Hood

1. When you publish a table extension that adds new fields to table Customer (Table 18), the database engine does not alter the core [dbo].[Customer] SQL table.
2. Instead, SQL Server creates a separate companion table named [dbo].[Customer$AppGuid].
3. The companion table shares the exact same primary key column(s) as the base table.
4. When an AL query or page reads a record that references both base fields and extension fields, the Business Central Server (NST) automatically issues an SQL JOIN on the primary key.

tableextension 50100 "Customer Loyalty Ext" extends Customer
{
    fields
    {
        field(50100; "Loyalty Tier Code"; Code[20])
        {
            Caption = 'Loyalty Tier Code';
            DataClassification = CustomerContent;
            TableRelation = "Loyalty Tier"."Code";

            trigger OnValidate()
            begin
                // Validation logic for custom field
            end;
        }
        field(50101; "Loyalty Points"; Integer)
        {
            Caption = 'Loyalty Points';
            DataClassification = CustomerContent;
            MinValue = 0;
        }
    }

    keys
    {
        key(LoyaltyKey; "Loyalty Tier Code", "Loyalty Points")
        {
        }
    }

    trigger OnBeforeInsert()
    begin
        if "Loyalty Tier Code" = '' then
            "Loyalty Tier Code" := 'BRONZE';
    end;

    trigger OnAfterInsert()
    begin
    end;
}

2. Modifying Existing Base Table Fields

AL table extensions allow developers to modify specific properties of existing base fields using the modify block. However, strict boundaries apply to ensure stability across extensions and base application upgrades.

Permissible vs Forbidden Modifications

Syntax for Modifying Base Fields and Triggers

tableextension 50101 "Item Modification Ext" extends Item
{
    fields
    {
        modify("Description 2")
        {
            Caption = 'Secondary Brand Name';
            TableRelation = "Brand Code"."Code";
            NotBlank = true;

            trigger OnBeforeValidate()
            begin
                // Executes BEFORE the base table OnValidate trigger
            end;

            trigger OnAfterValidate()
            begin
                // Executes AFTER the base table OnValidate trigger
            end;
        }
    }
}

3. Adding Keys & Indexing Rules in Table Extensions

Developers can define secondary keys in table extensions to support fast filtering and sorting on custom fields or combinations of custom and base fields.

Key Rules for Table Extensions

1. Secondary Keys Only: You can only add secondary keys. You cannot define a new primary key or alter the base clustered primary key.
2. Mixed Key Columns: A key in a table extension can contain:
   • Only custom fields defined in the same table extension.
   • A mixture of custom fields and base fields.
   • Only base fields (creating a new index on base columns).
3. SumIndexFields & IncludedFields: You can define SumIndexFields (for SIFT aggregates) and IncludedFields (for SQL covering indexes) on secondary keys in table extensions.
4. Unique Constraint Limitation: Defining Unique = true on a key in a table extension is only supported if all fields in the key belong to the extension table, or if the primary key fields are included. Unique indexes spanning base and companion tables cannot be enforced directly at the SQL level without primary key coverage.

4. Trigger Execution Sequence & Lifecycle Management

When a record operation occurs on an extended table, Business Central executes triggers and event subscribers in a strictly deterministic sequence.

Validation & Modification Lifecycle

1. Field :: OnBeforeValidate (Table Extension)
2. Field :: OnValidate (Base Table)
3. Field :: OnAfterValidate (Table Extension)
4. Table :: OnBeforeModify / OnBeforeInsert (Table Extension)
5. Table :: OnModify / OnInsert (Base Table)
6. Table :: OnAfterModify / OnAfterInsert (Table Extension)
7. Database Commit & Global Event Subscribers (Codeunits)

Schema Evolution with Obsolete Properties

When refactoring or deprecating fields in table extensions, deleting fields directly causes breaking schema changes. Business Central mandates non-destructive schema evolution using the ObsoleteState property:

field(50105; "Legacy Rating"; Code[10])
{
    Caption = 'Legacy Rating';
    DataClassification = CustomerContent;
    ObsoleteState = Pending;
    ObsoleteReason = 'Replaced by Loyalty Tier Code (field 50100) in v2.0.';
    ObsoleteTag = 'v2.0';
}

• ObsoleteState = Pending: Emits compiler warnings when developers reference the field. The field remains accessible in the database.
• ObsoleteState = Removed: The field can no longer be referenced in AL code. The runtime prevents access while preserving database migration pathways.