Chapter 7: Creating and Managing Tables

Objectives

By the end of this chapter you will be able to:

• Design a table with reliable keys and field constraints
• Use TableRelation, NotBlank, and validation triggers correctly
• Apply trigger logic that protects data integrity without blocking valid updates
• Prepare a table structure that later chapters can build on safely

7.1 Defining tables in AL

Tables hold your extension’s source of truth. A good table design enforces rules close to the data instead of leaving them all to UI code.

table 50100 "Loan Repayment Schedule"
{
    DataClassification = CustomerContent;

    fields
    {
        field(1; "Loan No."; Code[20]) { DataClassification = CustomerContent; }
        field(2; "Installment No."; Integer) { DataClassification = CustomerContent; }
        field(3; "Vendor No."; Code[20]) { DataClassification = CustomerContent; TableRelation = Vendor."No."; }
        field(4; "Due Date"; Date) { DataClassification = CustomerContent; }
        field(5; "Payment Date"; Date) { DataClassification = CustomerContent; }
        field(6; "Principal Amount"; Decimal) { DataClassification = CustomerContent; }
        field(7; "Interest Amount"; Decimal) { DataClassification = CustomerContent; }
        field(8; "Total Amount"; Decimal) { DataClassification = CustomerContent; }
        field(9; Paid; Boolean) { DataClassification = CustomerContent; }
    }

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

7.2 Keys and indexing strategy

The primary key should reflect what makes a record unique in business terms. Here an installment number is unique only within its loan, so a composite key is appropriate.

Add secondary keys only for queries you actually run. Extra keys speed up reads but slow down writes.

7.3 Field properties and validation

Use field properties to move obvious rules out of procedural code:

• NotBlank = true where empty values are invalid
• TableRelation for foreign-key style lookups, like "Vendor No." above, which must match a record in the standard Vendor table
• MinValue where negative values are impossible

That cuts down on duplicated checks in pages and codeunits.

7.4 Triggers for data integrity

Keep trigger logic minimal and deterministic.

trigger OnInsert()
begin
    "Total Amount" := "Principal Amount" + "Interest Amount";
end;

trigger OnModify()
begin
    if xRec.Paid then
        Error('A paid installment cannot be changed.');
    "Total Amount" := "Principal Amount" + "Interest Amount";
end;

Use xRec to check the previous state. Checking Rec here would block valid state transitions (Chapter 9 covers this in depth).

7.5 Table design practices

• Keep derived values consistent (Total Amount from principal plus interest).
• Leave side effects that belong in codeunits out of the table.
• Set field classification and permissions to match Chapters 11 and 15.

Chapter summary

In this chapter you:

• Designed a table with keys that reflect business meaning
• Used properties and relations to enforce integrity
• Implemented trigger logic with correct Rec/xRec semantics
• Laid a table foundation for the page and codeunit work

Tasks

1. Create the table. Implement Loan Repayment Schedule with composite key and core fields.
2. Add constraints. Apply TableRelation, NotBlank, and value guards where needed.
3. Harden triggers. Ensure OnModify checks xRec.Paid before allowing edits.
4. Try it yourself. Add one additional field and justify its DataClassification.

Check your work: Chapter 9’s codeunit and Chapter 14’s tests should run against this table design without trigger-related regressions.