ELI5 Create a new table in Business Central with AL

Another installment in the ELI5 series, this time, I’m covering how to create a table. Check it out:https://youtu.be/MhckAPaJPFw

https://youtu.be/MhckAPaJPFw

In this video, Erik walks through the complete process of creating a new table in Business Central using AL — from scratch. Starting with a blank VS Code project, he builds a Pet table field by field, explains the syntax and structure, adds a list page to display the data, implements a flow field lookup, and finishes by wiring up automatic number series assignment using the OnInsert trigger. This is a fantastic beginner-friendly walkthrough that demystifies AL table development.

Starting Point: A Fresh AL Project

Before writing any table code, you need a working AL project. That means having:

  • An app.json file that defines your app’s identity and object ID ranges
  • A launch.json file that connects you to either a cloud sandbox or a Docker-based Business Central instance
  • Downloaded symbols so the compiler knows about existing Business Central objects

Erik recommends creating a source folder at the root of your project to keep your AL code organized, since your app might contain other non-source assets. Inside source, he creates a subfolder called PetTable for this specific feature.

A Note on Object ID Ranges

Every AL object needs a unique ID number. Your app.json defines the range of IDs available to your app. In the video, Erik updates the default range to avoid clashing with other extensions:

"idRanges": [
    {
        "from": 84500,
        "to": 84549
    }
]

When you press Ctrl+Space in your AL file, VS Code will suggest the first available number from your configured range.

Building the Table Step by Step

Rather than using a snippet template, Erik builds the table from a completely blank file to show every piece of the syntax. The file is named PetTable.al — while the filename doesn’t affect compilation, naming files meaningfully helps you and other developers navigate the codebase.

The Table Declaration

Start by typing table and pressing Ctrl+Space to see available completions. The compiler runs continuously in the background, so you’ll see error counts decrease as you fill in each required piece:

table 84500 Pet
{
    Caption = 'Pet';
    TableType = Normal;

A few important points here:

  • Naming convention: If you want to follow Microsoft’s conventions, use singular names. Microsoft’s base tables are named Customer, Vendor, Item — not plurals. So this is the Pet table, not Pets.
  • Caption: The caption is what gets shown to users and is translatable. The table name is what you reference in code. Even if they’re identical, specifying a caption enables future translation.
  • TableType: Setting TableType = Normal is technically the default, but Erik likes to include it explicitly so anyone reading the code knows this is a standard database table — not a temporary table, CRM table, or external SQL table.

Adding Fields

Fields live inside a fields block. Each field declaration uses parentheses (not curly braces) and specifies a number, name, and type:

    fields
    {
        field(1; PetNumber; Code[20])
        {
            Caption = 'Pet Number';
            NotBlank = true;
        }
        field(2; Name; Text[100])
        {
            Caption = 'Name';
        }
        field(3; VetVendorNumber; Code[20])
        {
            Caption = 'Vet Number';
            TableRelation = Vendor."No.";
        }
    }

Key takeaways about fields:

  • Code vs. Text: Code fields are always stored as uppercase and are typically used for identifiers like numbers and codes. Text fields preserve case and are used for descriptive content like names.
  • Standard lengths: Customer numbers, vendor numbers, item numbers — these are all Code[20] in standard Business Central.
  • NotBlank: On primary key fields, you should set NotBlank = true to prevent users from creating records with an empty key. This is proper etiquette for primary keys.
  • TableRelation: The TableRelation property on the vet vendor number field creates a relationship to the Vendor table. This gives users a dropdown lookup when they enter data in that field.

The Mixed Syntax of Parentheses

Erik points out an admittedly odd aspect of AL syntax: the table, fields block, and keys block all use curly braces {}, but individual field and key declarations use parentheses (). There’s no deep reason for it — it’s just how the language is designed. You get used to it.

Defining the Primary Key

After the fields block, you define keys. The first key you specify automatically becomes the primary key:

    keys
    {
        key(PK; PetNumber)
        {
            // Clustered = true;
        }
    }

If you don’t specify a key, Business Central will create one for you — and you don’t want that. Always define your primary key explicitly. The Clustered property tells SQL Server how to physically organize records on disk, but for simple tables it’s not critical.

Erik also demonstrates comments here: two forward slashes // turn the rest of a line into a comment that the compiler ignores. The editor helpfully colors commented code differently.

System Fields

When pressing Ctrl+Space inside a key definition, you’ll see not only your own fields but also five system fields that Business Central automatically adds to every table:

  • SystemId — a GUID-based alternative primary key, great for web services
  • SystemCreatedAt — when the record was created
  • SystemCreatedBy — who created it
  • SystemModifiedAt — when it was last modified
  • SystemModifiedBy — who last modified it

Adding a Flow Field (Lookup)

Erik then adds a VetName field that doesn’t store data in the database — it dynamically looks up the vendor name based on the vet vendor number:

        field(4; VetName; Text[100])
        {
            Caption = 'Vet Name';
            FieldClass = FlowField;
            CalcFormula = lookup(Vendor.Name where("No." = field(VetVendorNumber)));
            Editable = false;
        }

What Makes a Field “Normal” vs. “Flow”

Remember that early compiler error: “a table has to have at least one normal field”? A normal field stores its value directly in the database. A flow field calculates its value on the fly from other data. The FieldClass property controls this distinction.

Classic examples from standard Business Central: the Balance field on a GL Account doesn’t exist in the database — it’s a flow field that sums GL Entries. The date filter fields on many tables are FieldClass = FlowFilter, used to pass filter values to flow field calculations.

CalcFormula Options

The CalcFormula property supports several calculation types:

  • Sum — classic for balance fields (sum of ledger entries)
  • Lookup — fetch a single value from a related table
  • Count — count records matching a filter
  • Exist — check if any matching records exist
  • Average, Min, Max — statistical calculations across matching records

The lookup formula reads: “Look up the Name field on the Vendor table where the vendor’s No. equals our VetVendorNumber field.” Setting Editable = false prevents users from trying to type directly into a calculated field.

Creating a List Page

A table alone isn’t visible to users — you need a page. Erik uses the ASet AL Dev Tools extension (a free VS Code extension by Andrzej) to quickly scaffold a list page via the “New AL File Wizard.” The wizard prompts for:

  • Object type (Page)
  • Object ID and name
  • Source table
  • Page type (List)
  • Application area and usage category (so it appears in the search function)
  • Which fields to include

The generated page includes the Pet Number, Name, Vet Vendor Number, and later the Vet Name flow field. When adding fields to the page, don’t forget the ApplicationArea property on each field — otherwise it won’t be visible. On Business Central version 21 and later, you can set the application area at the page level to avoid repeating it on every field.

Configuring launch.json for Testing

To speed up development, set your new page as the startup object in launch.json:

"startupObjectId": 84500,
"startupObjectType": "Page"

Now when you press F5, VS Code compiles, deploys, and opens Business Central directly on your Pet List page.

Implementing Automatic Number Series

The final piece is wiring up automatic number assignment so users don’t have to manually type a pet number. This requires writing code in a trigger.

Understanding Triggers and the Insert/Modify Pattern

Tables in AL have four triggers: OnDelete, OnInsert, OnModify, and OnRename. The standard Business Central data entry pattern works like this:

  1. User clicks “New” — they’re in insert mode
  2. User types a primary key value and tabs to the next field — the record is inserted into the database
  3. User fills in remaining fields — each time they leave the line, the record is modified

So the OnInsert trigger is the right place to auto-assign a number when the user leaves the primary key blank.

The OnInsert Code

    trigger OnInsert()
    var
        NoSeriesManagement: Codeunit "No. Series Management";
        NewNoSeriesCode: Code[20];
    begin
        if Rec.PetNumber = '' then
            NoSeriesManagement.InitSeries('PETS', '', Today(), Rec.PetNumber, NewNoSeriesCode);
    end;

Here’s what’s happening:

  • If PetNumber is blank (two single quotes '' — not a double quote), we call InitSeries
  • The first parameter 'PETS' is the number series code (normally this would come from a setup table, but here it’s hardcoded for simplicity)
  • The second parameter is for an old number series (not needed here)
  • Today() handles date-based number series that might restart each year
  • Rec.PetNumber is passed as a var parameter — the function writes the new number directly into this field
  • NewNoSeriesCode captures the series code used (required by the function signature)

You also need to create the PETS number series in Business Central itself — go to the Number Series page and set up a series with a starting number like PET0001.

How Did Erik Know What to Write?

This is a great tip for AL development: look at how Microsoft does the same thing. Using the ASet AL Dev Tools extension, you can browse the base application symbols, open the Customer table, find the OnInsert trigger, and see nearly identical code:

if "No." = '' then
    NoSeriesManagement.InitSeries(...

Microsoft’s version has a few extra fields for setup table references, but the pattern is the same. Exploring the base application code is one of the best ways to learn AL development.

The Complete Table

Putting it all together, the final Pet table looks like this:

table 84500 Pet
{
    Caption = 'Pet';
    TableType = Normal;

    fields
    {
        field(1; PetNumber; Code[20])
        {
            Caption = 'Pet Number';
            NotBlank = true;
        }
        field(2; Name; Text[100])
        {
            Caption = 'Name';
        }
        field(3; VetVendorNumber; Code[20])
        {
            Caption = 'Vet Number';
            TableRelation = Vendor."No.";
        }
        field(4; VetName; Text[100])
        {
            Caption = 'Vet Name';
            FieldClass = FlowField;
            CalcFormula = lookup(Vendor.Name where("No." = field(VetVendorNumber)));
            Editable = false;
        }
    }

    keys
    {
        key(PK; PetNumber)
        {
        }
    }

    trigger OnInsert()
    var
        NoSeriesManagement: Codeunit "No. Series Management";
        NewNoSeriesCode: Code[20];
    begin
        if Rec.PetNumber = '' then
            NoSeriesManagement.InitSeries('PETS', '', Today(), Rec.PetNumber, NewNoSeriesCode);
    end;
}

Summary

In this ELI5 walkthrough, Erik covered the full lifecycle of creating a new table in Business Central with AL:

  • Project setup: app.json, launch.json, symbols, and a clean folder structure
  • Table structure: declaration, fields block, and keys block — each with their own syntax rules
  • Field types: Code for identifiers, Text for descriptive content, and the difference between normal fields and flow fields
  • Table relations: Linking fields to other tables for dropdown lookups
  • Flow fields: Using CalcFormula with lookup to dynamically display related data without storing it
  • Page creation: Building a list page to expose the table to users, with application areas for searchability
  • Number series: Using the OnInsert trigger and NoSeriesManagement codeunit to auto-assign numbers
  • Learning strategy: Exploring Microsoft’s base application code to understand patterns and conventions

The compiler running in the background is your best friend — watch the error count decrease as you fill in each required piece, and use Ctrl+Space liberally to discover what the language expects next.

Picture of hougaard

hougaard