AL development from scratch, part 3: keys, FlowFields, table relations and triggers

This is part 3 of AL development from scratch. In part 2 we built the stored fields of the Pallet Type table. This part covers the things that make a table behave properly: keys, fieldgroups, FlowFields, table relations and triggers. By the end the table is finished.

Keys

Keys do two jobs. The primary key identifies each record uniquely. Secondary keys become indexes in SQL Server, so that sorting and filtering on those fields is fast.

The primary key

The first key in the keys section is the primary key. Every table must have one, and no two records can have the same primary key value.

keys
{
    key(PK; Code)
    {
        Clustered = true;
    }
}

The name in brackets (PK) is just a name for the key; the fields after the semicolon are what matter. A key can have several fields, as Microsoft's document lines do:

key(PK; "Document Type", "Document No.", "Line No.")
{
    Clustered = true;
}

Design the primary key carefully, because changing it in production is a breaking change. For master data (customers, items, our pallet types) use a single Code field. For lines, use the header's key plus a "Line No." Integer. For log and entry tables, use an "Entry No." Integer with AutoIncrement = true.

Secondary keys

Add a secondary key when users or code will regularly filter or sort on fields other than the primary key. For pallet types, a key on Material and Code helps if people often list pallets by material:

key(Material; Material, Code)
{
}

Every key costs something: each insert, modify and delete has to update every index. A table with three sensible keys is fast; a table with twelve keys "just in case" is slow to write to. Add keys for real filters, not imagined ones.

Key properties

Fieldgroups

Fieldgroups decide which fields appear in two places users see all the time:

• DropDown: the columns shown in the drop-down list when a user types into a field that looks up this table. For pallet types, the user picking a default pallet on an item should see the code, description and material.
• Brick: the fields shown on a tile when a list page is viewed as tiles (on a phone or in tile view).

fieldgroups
{
    fieldgroup(DropDown; Code, Description, Material)
    {
    }
    fieldgroup(Brick; Code, Description, Material, "Max. Weight (kg)")
    {
    }
}

Without a DropDown fieldgroup, the drop-down shows the primary key and little else, and users end up opening the full lookup page to see what they are choosing. It takes two lines to fix.

FlowFields and FlowFilters

Every field has a FieldClass:

• Normal (the default): the value is stored in the table.
• FlowField: the value is not stored. It is calculated from other tables when needed, using a CalcFormula. The customer's Balance is a FlowField: a sum of customer ledger entries.
• FlowFilter: not stored and not calculated. It holds a filter value the user enters, which FlowFields can use. The "Date Filter" on customers and items is a FlowFilter.

FlowFields are one of the best things about Business Central. Instead of storing a total and writing code to keep it right whenever anything changes, you describe how to calculate it and the platform does the rest, efficiently, using the SIFT indexes from the keys section.

CalcFormula types

CalcFormula syntax

Each formula names a table (and a field, except for count and exist), then filters in a where clause. Filters use field() to compare with a field on the current record, const() for a fixed value and filter() for a filter expression:

// count: how many items use this pallet type
CalcFormula = count(Item where("BBX Default Pallet Type" = field(Code)));

// sum: inventory of an item, filtered by the item's own Date Filter and Location Filter
CalcFormula = sum("Item Ledger Entry".Quantity where("Item No." = field("No."),
                                                     "Posting Date" = field("Date Filter"),
                                                     "Location Code" = field("Location Filter")));

// exist: does the customer have any open entries?
CalcFormula = exist("Cust. Ledger Entry" where("Customer No." = field("No."),
                                               Open = const(true)));

// lookup: the name of the customer on a document
CalcFormula = lookup(Customer.Name where("No." = field("Sell-to Customer No.")));

// max: the latest posting date for an item
CalcFormula = max("Item Ledger Entry"."Posting Date" where("Item No." = field("No.")));

If your file has a namespace, every Microsoft table named in a formula needs a using line at the top of the file, exactly as it would anywhere else. For the first formula that is using Microsoft.Inventory.Item; and for the Item Ledger Entry ones using Microsoft.Inventory.Ledger;. For the others, press F12 on the table name to see its namespace.

A FlowField is declared like any other field, with FieldClass = FlowField, the formula, and Editable = false (users cannot type into a calculated value):

field(20; "No. of Items"; Integer)
{
    Caption = 'No. of Items';
    ToolTip = 'Specifies how many items use this pallet type as their default pallet.';
    FieldClass = FlowField;
    CalcFormula = count(Item where("BBX Default Pallet Type" = field(Code)));
    Editable = false;
    BlankZero = true;
}

This formula refers to the "BBX Default Pallet Type" field we add to the Item table in a table extension in part 4. Until that table extension exists, the table will not compile; if you are following along, add this field after part 4.

FlowFilters

A FlowFilter field lets the user (or code) set a filter that FlowFields then use. The classic example is a date filter, so a balance can be shown "as at" a date:

field(30; "Date Filter"; Date)
{
    Caption = 'Date Filter';
    FieldClass = FlowFilter;
}

A FlowField then refers to it with field("Date Filter"), as in the inventory example above. On a page, setting the date filter changes every FlowField that uses it.

Calculating FlowFields in code

Because FlowFields are not stored, reading a record does not calculate them. Pages calculate the FlowFields they show automatically; in code you must ask:

PalletType.Get('EURO');
PalletType.CalcFields("No. of Items");
if PalletType."No. of Items" > 0 then
    ...

If you loop through many records and need a FlowField on each, use SetAutoCalcFields before the loop instead. It calculates the field as part of reading each record, which is much faster than calling CalcFields on every row:

PalletType.SetAutoCalcFields("No. of Items");
if PalletType.FindSet() then
    repeat
        // "No. of Items" is already calculated here
    until PalletType.Next() = 0;

Forgetting CalcFields is the most common FlowField bug: the code runs, the value is always 0, and nothing tells you why.

Table relations

A TableRelation links a field to another table. It gives the field a lookup (the arrow and the drop-down) and, unless you say otherwise, checks that the value entered exists in the other table.

Simple relations

// relates to the primary key of the table
TableRelation = "BBX Pallet Type";

// relates to a specific field
TableRelation = "Unit of Measure".Code;

Filtered relations

Add a where clause to limit which records can be chosen, using the same const, field and filter functions as CalcFormulas:

// only pallet types that are not blocked
TableRelation = "BBX Pallet Type" where(Blocked = const(false));

// only units of measure set up for this item
TableRelation = "Item Unit of Measure".Code where("Item No." = field("No."));

// only G/L accounts that can be posted to
TableRelation = "G/L Account" where("Account Type" = const(Posting), Blocked = const(false));

Conditional relations

Sometimes a field relates to different tables depending on another field. Sales lines do this: the "No." field is an item, a G/L account, a resource and so on, depending on the line's Type. AL handles this with if and else if:

TableRelation = if (Type = const(Item)) Item."No."
                else if (Type = const(Resource)) Resource."No."
                else if (Type = const("G/L Account")) "G/L Account"."No.";

Relation properties

• ValidateTableRelation = false keeps the lookup but stops the check that the value exists. Use it rarely; it allows values that point at nothing.
• The page that opens for the lookup is the related table's LookupPageId, which is why we set it on the Pallet Type table in part 2.

Table relations are not foreign keys in the database. Business Central does not stop someone deleting a pallet type that items still use. If that matters, you protect it yourself in the OnDelete trigger, which is exactly what we do below.

Triggers

Triggers are code that runs when something happens to a record or a field. Tables have four record triggers and fields have two.

Table triggers

The true matters. Insert(true), Modify(true) and Delete(true) run the trigger; Insert(), Modify() and Delete() (or with false) do not. Pages always run the triggers. In code you choose, and the usual rule is: if you are doing what a user would do, run the triggers. Skipping them skips whatever checks and defaults they contain, which is a classic source of "how did this record get into that state?" problems.

Every table also has system fields that are filled in automatically, including SystemCreatedAt, SystemModifiedAt and the user who made each change, so you do not need triggers just to record when something changed. See system fields.

Field triggers

As with records, assigning a value directly does not run OnValidate:

PalletType.Blocked := true;              // no OnValidate
PalletType.Validate(Blocked, true);      // runs OnValidate

When copying values in code, use Validate for anything a user would normally type, so the same rules apply.

Procedures on tables

Tables can contain procedures. Put logic on the table when it is about that record and nothing else: calculating something from its own fields, checking it is in a valid state. Logic that involves several tables or a business process belongs in a codeunit (part 7).

procedure FootprintSqm(): Decimal
begin
    exit(Rec."Length (mm)" * Rec."Width (mm)" / 1000000);
end;

procedure CheckNotBlocked()
var
    BlockedErr: Label 'Pallet type %1 is blocked.', Comment = '%1 = pallet type code';
begin
    if Rec.Blocked then
        Error(BlockedErr, Rec.Code);
end;

Inside a table, Rec means "the current record". Writing Rec. explicitly is the modern style and makes the code clearer.

Every message a user can see goes into a Label, never a text string in the Error call. Labels can be translated, and the Comment tells translators what %1 and %2 stand for. Part 7 covers labels in more detail.

The finished Pallet Type table

The finished table refers to Microsoft's Item table (in the FlowField and the triggers), so it now needs a using line for Item's namespace. Without namespaces, simply leave both lines out.

namespace BusinessBoxed.PalletManager;

using Microsoft.Inventory.Item;

table 50100 "BBX Pallet Type"
{
    Caption = 'Pallet Type';
    DataClassification = CustomerContent;
    LookupPageId = "BBX Pallet Type List";
    DrillDownPageId = "BBX Pallet Type List";
    DataCaptionFields = Code, Description;

    fields
    {
        field(1; Code; Code[20])
        {
            Caption = 'Code';
            ToolTip = 'Specifies the code that identifies the pallet type.';
            NotBlank = true;
        }
        field(2; Description; Text[100])
        {
            Caption = 'Description';
            ToolTip = 'Specifies a description of the pallet type.';
        }
        field(3; Material; Enum "BBX Pallet Material")
        {
            Caption = 'Material';
            ToolTip = 'Specifies what the pallet is made of.';
        }
        field(4; "Length (mm)"; Integer)
        {
            Caption = 'Length (mm)';
            ToolTip = 'Specifies the length of the pallet in millimetres.';
            MinValue = 0;
            BlankZero = true;
        }
        field(5; "Width (mm)"; Integer)
        {
            Caption = 'Width (mm)';
            ToolTip = 'Specifies the width of the pallet in millimetres.';
            MinValue = 0;
            BlankZero = true;
        }
        field(6; "Max. Weight (kg)"; Decimal)
        {
            Caption = 'Max. Weight (kg)';
            ToolTip = 'Specifies the maximum weight the pallet can carry, in kilograms.';
            DecimalPlaces = 0 : 2;
            MinValue = 0;
            BlankZero = true;
        }
        field(7; "Unit Cost"; Decimal)
        {
            Caption = 'Unit Cost';
            ToolTip = 'Specifies the cost of one pallet of this type, in local currency.';
            AutoFormatType = 2;
            MinValue = 0;
        }
        field(8; Blocked; Boolean)
        {
            Caption = 'Blocked';
            ToolTip = 'Specifies that the pallet type can no longer be chosen as the default pallet for an item.';

            trigger OnValidate()
            var
                StillUsedMsg: Label '%1 item(s) still use pallet type %2 as their default. They keep it until you change them.', Comment = '%1 = number of items, %2 = pallet type code';
            begin
                if not Rec.Blocked then
                    exit;
                Rec.CalcFields("No. of Items");
                if Rec."No. of Items" > 0 then
                    Message(StillUsedMsg, Rec."No. of Items", Rec.Code);
            end;
        }
        field(20; "No. of Items"; Integer)
        {
            Caption = 'No. of Items';
            ToolTip = 'Specifies how many items use this pallet type as their default pallet.';
            FieldClass = FlowField;
            CalcFormula = count(Item where("BBX Default Pallet Type" = field(Code)));
            Editable = false;
            BlankZero = true;
        }
    }

    keys
    {
        key(PK; Code)
        {
            Clustered = true;
        }
        key(Material; Material, Code)
        {
        }
    }

    fieldgroups
    {
        fieldgroup(DropDown; Code, Description, Material)
        {
        }
        fieldgroup(Brick; Code, Description, Material, "Max. Weight (kg)")
        {
        }
    }

    trigger OnDelete()
    var
        Item: Record Item;
        InUseErr: Label 'You cannot delete pallet type %1 because %2 item(s) still use it as their default pallet. Change those items first, or block the pallet type instead.', Comment = '%1 = pallet type code, %2 = number of items';
    begin
        Item.SetRange("BBX Default Pallet Type", Rec.Code);
        if not Item.IsEmpty() then
            Error(InUseErr, Rec.Code, Item.Count());
    end;

    trigger OnRename()
    var
        NoRenameErr: Label 'You cannot rename a pallet type. Create a new one and block the old one instead.';
    begin
        Error(NoRenameErr);
    end;

    procedure FootprintSqm(): Decimal
    begin
        exit(Rec."Length (mm)" * Rec."Width (mm)" / 1000000);
    end;

    procedure CheckNotBlocked()
    var
        BlockedErr: Label 'Pallet type %1 is blocked.', Comment = '%1 = pallet type code';
    begin
        if Rec.Blocked then
            Error(BlockedErr, Rec.Code);
    end;
}

What each piece does:

• The OnDelete trigger stops anyone deleting a pallet type that items still use, because a table relation alone would not. It uses IsEmpty first, which is cheap, and only counts the items when it needs the number for the message.
• The OnRename trigger blocks renaming. Renaming a primary key updates every field that relates to it across the database, which on a large item table is slow and surprising. Blocking a pallet type and creating a new one is safer and clearer. This is a design choice; many master tables allow renaming.
• The OnValidate on Blocked warns the user, rather than stopping them, if items still use the pallet type they are blocking. The Message uses a label with a comment, like every other user-facing text.
• The "No. of Items" FlowField is never stored and never out of date. It relies on the table extension in part 4.
• The secondary key on Material supports sorting and filtering by material on the list page.
• The procedures keep record-level logic with the record. Codeunits and pages call PalletType.CheckNotBlocked() rather than repeating the check.

Common mistakes

• Forgetting CalcFields before reading a FlowField in code, so the value is always zero.
• Calling CalcFields inside a loop instead of SetAutoCalcFields before it.
• Storing totals in normal fields and writing code to keep them in step, instead of using a FlowField.
• Using Insert(), Modify() or Delete() without true in code that imitates a user, silently skipping the checks in the triggers.
• Assigning with := where Validate is needed, so dependent fields are not updated.
• Assuming a TableRelation stops deletion of the related record. It does not; use OnDelete.
• Too many secondary keys, slowing every write, or none at all on fields users filter by every day.
• A primary key that can change, such as a description, or one too short for real data.
• No DropDown fieldgroup, so users cannot tell lookup values apart without opening the full list.
• Error messages as literal text in the Error call instead of labels, so they cannot be translated and the analysers complain.
• Heavy logic in OnValidate that calls other systems or posts entries. Validation should be quick and predictable; processes belong in codeunits.

Where we are

The Pallet Type table is complete: stored fields, keys, fieldgroups, a FlowField, protection against deleting or renaming pallet types in use, and record-level procedures. In part 4 we add fields to Microsoft's Item table with a table extension, and look at enums and enum extensions in detail.

Previous: part 2: building a table, fields and data types · Next: part 4: table extensions, enums and enum extensions · Back to the series contents

Want help with your first extension? Book a 30-minute call.

Related posts