We’re on the lookout for lookups

In this video, I show how lookup and table relations works in Business Central. This is another video in my series for AL beginners.

https://youtu.be/sBkOiuEojcU

In this video, Erik walks through the different types of lookups available in AL and Business Central. He covers three approaches: simple table relation lookups, conditional table relation lookups, and custom lookups using the OnLookup trigger — explaining when and why you’d use each one.

What Is a Lookup?

A lookup in Business Central is the drop-down that appears when you click on a field, allowing you to select a value from a related table. For example, on a Customer Card, clicking the Customer Posting Group field displays a drop-down with the available options. You can also type in the field, and it acts as a search field with cross-column search capabilities.

In the current version of Business Central, the lookup arrow points downward. In older versions, lookup pointed up and drill-down pointed down — but that’s a topic for another video.

The keyboard shortcut to trigger a lookup is Alt + Arrow Down (it used to be F6 or F4 in older versions).

Type 1: Simple Table Relation Lookup

The simplest type of lookup comes completely free — you just define a table relation on the field and the system handles everything else. Here’s a basic table with an “Item No.” field that has a table relation to the Item table:

field(2; "Item No."; Code[20])
{
    Caption = 'Item No.';
    DataClassification = ToBeClassified;
    TableRelation = Item."No.";
}

With this in place, the field automatically gets a lookup arrow. Users can click it to see a list of items, select one, and even use the field as a search box — typing “chair” will do a cross-column search across the item list.

One important note: always specify the field you’re relating to. While AL allows you to write TableRelation = Item; without specifying the field (because this was how it was done in very early versions, and a lot of Microsoft’s base app code still does this), Erik is clear: this is bad practice. Always specify the field explicitly, like TableRelation = Item."No.";.

Type 2: Conditional Table Relation Lookup

The second type of lookup handles scenarios where the lookup target depends on another field’s value — like the Number field on a Sales Line, which looks up Items, G/L Accounts, Resources, or other tables depending on the Type field.

To demonstrate this, Erik creates a Type field and a No. field with a conditional table relation:

field(3; "Type"; Option)
{
    Caption = 'Type';
    OptionMembers = Item,GL;
}
field(4; "No."; Code[20])
{
    Caption = 'No.';
    TableRelation = if (Type = const(Item)) Item."No." else
    if (Type = const(GL)) "G/L Account"."No.";
}

The syntax reads very naturally: if the Type is Item, then relate to Item.”No.”, else if Type is GL, then relate to “G/L Account”.”No.”. The condition uses const to compare against a fixed value, but you can also use field (to compare against another field in the current record) or filter (for a hard-coded filter expression).

With this definition, when Type is set to Item, the lookup shows items. Change Type to GL, and the same No. field now shows G/L Accounts. The UI adapts automatically based on the data model — still no custom code needed.

A Real-World Example: Sales Line Table 37

Looking at the actual Sales Line table (Table 37) in the base app, the table relation on the No. field is considerably more complex. It handles blank types (looking up Standard Text), G/L Accounts with additional filters for direct posting and account type, Resources, Fixed Assets, Item Charges, and Items with different blocked-status filters depending on whether the document type is a Credit Memo or Return Order.

Even with all that complexity, it’s still defined entirely in the data model, and the UI adapts automatically. However, Erik notes one important limitation: you can only reference fields from the current record in these conditions. You cannot, for example, have a Sales Line lookup react to a field on the Sales Header without replicating that field into the Sales Line.

Erik also points out that the auto-formatter in AL does a beautiful job with most code, but these complex table relation statements are a challenge for it — the formatting can look messy.

Type 3: Custom Lookup with the OnLookup Trigger

The third type is the catch-all: when the table relation approach can’t achieve what you need, you implement the lookup yourself using the OnLookup trigger on the page field.

Understanding the OnLookup Trigger

The OnLookup trigger has a specific signature that’s worth understanding:

trigger OnLookup(var Text: Text): Boolean

Two important things to note:

  • The Text parameter (passed by reference) represents the search value coming in — it’s whatever the user has typed in the field. On the way out, it becomes the selected value that gets placed into the field.
  • The Boolean return value indicates whether the lookup was successful. Return true if the user made a valid selection; the default return is false.

When the system detects an OnLookup trigger, the field displays three dots (…) instead of the standard lookup arrow, signaling that custom code is behind the lookup.

Building a Custom Lookup

Here’s the complete implementation from the demo page:

field("Item No."; Rec."Item No.")
{
    ApplicationArea = All;
    trigger OnLookup(var Text: Text): Boolean
    var
        Item: Record Item;
    begin
        if Page.RunModal(PAGE::"Item List", Item) = Action::LookupOK then begin
            Text := Item."No.";
            exit(true);
        end;
    end;
}

The key elements:

  1. Page.RunModal opens the Item List page modally and waits for the user to act.
  2. Wrapping it in an if statement checking for Action::LookupOK is critical — this tells the page that it’s being used in a lookup scenario. Without this check, the page opens with just a “Close” button and no “OK” button. With the check, the system knows it’s in lookup mode and shows the proper OK/Cancel interface.
  3. Setting Text to the selected item’s number passes the value back to the field.
  4. exit(true) signals that the lookup was successful.
  5. If the user cancels, the function falls through to the implicit exit(false), and nothing changes.

Combining Table Relations with Custom Lookups

An interesting detail: you can have both a table relation and an OnLookup trigger on the same field. The table relation handles validation (ensuring the entered value actually exists in the related table), while the OnLookup trigger controls the lookup experience. The three dots still appear, and your custom lookup code runs, but the schema-level validation from the table relation remains in effect.

Advanced Custom Lookup Options

For more control, you can declare the lookup page as a page variable instead of using Page.RunModal with a page ID. This allows you to:

  • Set the page into lookup mode explicitly
  • Apply filters before showing the page
  • Call custom functions on the page to set additional filters or configure behavior

The Complete Source Code

Here’s the full table definition showing all three lookup approaches:

table 50148 "Lookup tester"
{
    Caption = 'Lookup tester';
    DataClassification = ToBeClassified;

    fields
    {
        field(1; PKEY; Code[20])
        {
            Caption = 'PKEY';
            DataClassification = ToBeClassified;
        }
        field(2; "Item No."; Code[20])
        {
            Caption = 'Item No.';
            DataClassification = ToBeClassified;
            TableRelation = Item."No.";
        }
        field(3; "Type"; Option)
        {
            Caption = 'Type';
            OptionMembers = Item,GL;
        }
        field(4; "No."; Code[20])
        {
            Caption = 'No.';
            TableRelation = if (Type = const(Item)) Item."No." else
            if (Type = const(GL)) "G/L Account"."No.";
        }
    }
    keys
    {
        key(PK; PKEY)
        {
            Clustered = true;
        }
    }
}

And the corresponding page with the custom lookup on the Item No. field:

page 50148 "Lookup tester"
{
    Caption = 'Lookup tester';
    PageType = Card;
    SourceTable = "Lookup tester";

    layout
    {
        area(content)
        {
            group(General)
            {
                field(Type; Rec.Type)
                {
                    ApplicationArea = All;
                }
                field("No."; Rec."No.")
                {
                    ApplicationArea = All;
                }
                field("Item No."; Rec."Item No.")
                {
                    ApplicationArea = All;
                    trigger OnLookup(var Text: Text): Boolean
                    var
                        Item: Record Item;
                    begin
                        if Page.RunModal(PAGE::"Item List", Item) = Action::LookupOK then begin
                            Text := Item."No.";
                            exit(true);
                        end;
                    end;
                }
            }
        }
    }
    trigger OnOpenPage()
    begin
        if Rec.IsEmpty() then
            Rec.Insert();
    end;
}

A Note on Option Fields

Erik mentions a practical tip about Option fields: they default to the first value in the OptionMembers list. If you want users to be able to explicitly select the first option (rather than it being pre-selected), add a blank space as the first member. If you want an “unselected” state that users can’t choose, add a comma at the beginning of the list.

Summary

There are three approaches to lookups in AL and Business Central, and you should favor them in order:

  1. Simple Table Relation — Define a TableRelation on the field and get the lookup for free. Always specify the target field explicitly.
  2. Conditional Table Relation — Use if/else syntax in the TableRelation to change the lookup target based on other fields in the current record. Still free, still schema-driven.
  3. Custom OnLookup Trigger — When the data model alone can’t express what you need, use the OnLookup trigger for full control. Remember that Text is both the search input and the output value, and return true for a successful lookup.

Erik uses the OnLookup trigger frequently — not just for complex scenarios, but also to be friendlier to users by adding extra filters that reduce errors from the standard lookup combined with validation code. Visual cue: when you see the three dots (…) instead of a dropdown arrow on a field, you know there’s custom lookup code behind it.

Picture of hougaard

hougaard