Tables in AL

What Is a Table in AL?

A table in AL represents structured business data stored in the database. Every master record, document, ledger entry, setup value, or transaction in Business Central ultimately exists as a record in a table.

A table defines:

• What data is stored
• How that data is structured
• Which fields are mandatory
• How records are uniquely identified
• Which business rules are enforced at the data level

Tables do not define:

• UI layout
• User actions
• Process flow

Those responsibilities belong to other objects.

Why Tables Are the Foundation of Business Central

Business Central is a data-driven ERP. All business activity eventually results in:

• Records being created
• Records being modified
• Records being posted
• Records being read for reporting

Tables are the single source of truth.

If data is incorrect at the table level, no amount of UI or logic can fully fix it. That is why table design is conservative, strict, and highly controlled.

Types of Tables in Business Central

Conceptually, tables fall into a few broad categories:

• Master data tables (Customer, Vendor, Item)
• Document tables (Sales Header, Purchase Line)
• Ledger tables (G/L Entry, Item Ledger Entry)
• Setup tables (General Ledger Setup)
• Buffer / temporary tables (used during processing)

Although they serve different purposes, they all follow the same AL table structure rules.

Basic Structure of a Table in AL

An AL table is defined using the table object and consists of:

• Object ID and name
• Properties
• Fields
• Keys
• Optional triggers

table 50100 "Customer Feedback"
{
    DataClassification = CustomerContent;

    fields
    {
        field(1; "Entry No."; Integer)
        {
            DataClassification = SystemMetadata;
            AutoIncrement = true;
        }

        field(2; "Customer No."; Code[20])
        {
            DataClassification = CustomerContent;
            TableRelation = Customer;
            NotBlank = true;
        }

        field(3; "Feedback Text"; Text[250])
        {
            DataClassification = CustomerContent;
        }

        field(4; "Created Date"; Date)
        {
            DataClassification = SystemMetadata;
            InitValue = Today();
        }
    }

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

Understanding Table Components

• The ID must be within your extension’s object range
• The name should reflect business meaning, not technical purpose

Good naming improves maintainability and readability.

Fields define individual pieces of data stored in the table.

Each field has:

• A unique field number
• A name
• A data type
• Properties that control behavior

Field numbers are important for:

• Upgrade safety
• Extensions
• Database schema consistency

Every field and table must declare its data sensitivity.

DataClassification = CustomerContent;

This supports compliance, auditing, and privacy tooling.

TableRelation enforces valid references.

TableRelation = Customer;

This ensures that only existing customers can be selected and improves lookup behavior automatically.

Ensures mandatory data is entered.

NotBlank = true;

This enforces business rules before code executes.

Keys in AL Tables

Keys define:

• How records are uniquely identified
• How data is indexed
• How fast data can be retrieved

Every table must have at least one primary key.

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

Primary keys:

• Must uniquely identify records
• Should be stable
• Should not change once assigned

Secondary Keys (Indexes)

Tables can have additional keys for performance.

key(CustomerKey; "Customer No.")
{
}
    

Secondary keys:

• Improve filtering and sorting
• Do not enforce uniqueness (unless specified)
• Must be designed carefully to avoid overhead

Table Triggers (High-Level Intro)

Tables can contain triggers such as:

• OnInsert
• OnModify
• OnDelete
• OnValidate

At this stage, it’s important to understand when they should be used:

• Only for data-level rules
• Not for UI behavior
• Not for complex process flow

Detailed trigger usage is covered in later sections.

Temporary Tables

Some tables are used only in memory during processing.

Temporary tables:

• Do not store data permanently
• Are session-scoped
• Are commonly used in reports and batch processing

Common Beginner Mistakes With Tables

• Putting UI logic in table triggers
• Using Text instead of Code for identifiers
• Forgetting DataClassification
• Designing poor primary keys
• Overusing triggers instead of properties

Avoiding these mistakes leads to stable and upgrade-safe solutions.

Summary

Tables in AL define the structure and integrity of business data in Business Central. They are the foundation on which all processes, logic, and reporting depend.

A well-designed table:

• Enforces correct data
• Minimizes code
• Improves performance
• Survives upgrades

Understanding tables properly is the most important step in becoming a confident Business Central developer.