Two tables, One list Page, What to do?

What do you do when you have two tables, and you want to show them on the same list? Asked Steve on Twitter. Check out one solution to this issue in this video:

https://youtu.be/blXCX2rXDwQ

In this video, Erik tackles a common question in Business Central AL development: how do you display data from two different tables on a single list page? Inspired by a question from Steve Endow on Twitter, Erik walks through the approach of using a global variable to pull in fields from a secondary table, explaining the technique step by step, including the pitfalls and trade-offs.

The Problem: Two Tables, One List Page

A list page in Business Central can only have one source table. But what if you need to show fields from a related table alongside the primary table’s data? For example, you might want to display customer balance and payment terms on a sales document list.

Erik outlines three approaches to solving this:

  1. Query Object — Use AL’s query object to join the tables. Erik has covered this in other videos on the channel.
  2. Flow Fields — Create flow fields on the primary table (Table A) that pull values from the secondary table (Table B). This has the advantage that users can filter and sort on those fields. Microsoft is also smart enough to optimize multiple flow fields pointing to the same table with the same filters into a single call, so the performance cost doesn’t scale linearly with the number of flow fields.
  3. Global Variable Approach — Use a global record variable for Table B, populate it in the page triggers, and bind columns to that variable’s fields. This is the approach demonstrated in the video.

Building the List Page

Erik starts by creating a list page sourced from the Sales Header table with a few basic columns: Document Type, No., Document Date, Posting Date, and Sell-to Customer No. He uses the page wizard from André Arnaud de Calavon’s AL Dev Tools VS Code extension to scaffold the page quickly.

Adding Columns from the Customer Table

To display customer data, Erik declares a global Customer record variable and adds two columns bound to fields on that variable: Balance (LCY) and Payment Terms Code:

field(CustomerBalanceLCY; Customer."Balance (LCY)")
{
    BlankZero = true;
}
field(CustomerPaymentTermsCode; Customer."Payment Terms Code")
{
    trigger OnValidate()
    begin
        Customer.Validate("Payment Terms Code");
        Customer.Modify();
    end;
}

Note that these columns reference the global variable rather than Rec. The field names (CustomerBalanceLCY, CustomerPaymentTermsCode) are custom identifiers since these aren’t fields on the source table — you’ll want to set appropriate captions.

Populating the Global Variable

The key to making this work is populating the Customer variable every time the page displays a new row. Erik creates a RefreshLine procedure and calls it from two triggers:

trigger OnAfterGetRecord()
begin
    RefreshLine();
end;

trigger OnAfterGetCurrRecord()
begin
    RefreshLine();
end;

procedure RefreshLine()
begin
    Customer.SetAutoCalcFields("Balance (LCY)");
    if not Customer.Get(Rec."Sell-to Customer No.") then
        clear(Customer);
end;

Why Both Triggers?

OnAfterGetRecord fires every time the list fetches a row for display. OnAfterGetCurrRecord fires when the user navigates to a specific row. Because Customer is a global variable, its value persists between rows. If you only populate it in one trigger, you can end up in situations where stale data from a previous row is displayed. Calling RefreshLine from both triggers ensures the customer data is always correct for the current row.

Handling Missing Customers

If a sales header has a blank or invalid customer number, the Customer.Get call will fail. The if not ... then clear(Customer) pattern ensures the global variable is reset to blank values rather than showing leftover data from the previous row. The BlankZero = true property on the balance field prevents displaying “0” when there’s no customer.

Auto-Calculating Flow Fields

Balance (LCY) is itself a flow field on the Customer table. By calling Customer.SetAutoCalcFields("Balance (LCY)") before the Get, the balance is automatically calculated when the record is retrieved — no separate CalcFields call needed.

Making Fields Editable

An interesting aspect of this approach is that you can make the Table B fields editable. When the user modifies a value, you need to handle the write-back yourself in an OnValidate trigger:

trigger OnValidate()
begin
    Customer.Validate("Payment Terms Code");
    Customer.Modify();
end;

Erik demonstrates an important subtlety here: simply assigning a value to the field doesn’t trigger validation logic on the Customer record. You need to explicitly call Customer.Validate to run the field’s validation logic (which may enforce table relations, run additional business logic, etc.) and then Customer.Modify() to persist the change. Without the Validate call, you could write invalid data — for example, a non-existent payment terms code — directly to the customer record.

The Complete Source Code

page 50100 OneListTwoTables
{
    ApplicationArea = All;
    Caption = 'OneListTwoTables';
    PageType = List;
    SourceTable = "Sales Header";
    UsageCategory = Administration;

    layout
    {
        area(content)
        {
            repeater(General)
            {
                field("Document Type"; Rec."Document Type")
                {
                }
                field("No."; Rec."No.")
                {
                }
                field("Document Date"; Rec."Document Date")
                {
                }
                field("Posting Date"; Rec."Posting Date")
                {
                }
                field("Sell-to Customer No."; Rec."Sell-to Customer No.")
                {
                }
                field(CustomerBalanceLCY; Customer."Balance (LCY)")
                {
                    BlankZero = true;
                }
                field(CustomerPaymentTermsCode; Customer."Payment Terms Code")
                {
                    trigger OnValidate()
                    begin
                        Customer.Validate("Payment Terms Code");
                        Customer.Modify();
                    end;
                }
            }
        }
    }
    trigger OnAfterGetRecord()
    begin
        RefreshLine();
    end;

    trigger OnAfterGetCurrRecord()
    begin
        RefreshLine();
    end;

    procedure RefreshLine()
    begin
        Customer.SetAutoCalcFields("Balance (LCY)");
        if not Customer.Get(Rec."Sell-to Customer No.") then
            clear(Customer);
    end;

    var
        Customer: Record Customer;
}

Pros and Cons

Advantages of the Global Variable Approach

  • Lightweight — For each row, you’re doing one Get call to fetch the related record. The database cost is comparable to flow fields.
  • Editable — You can make Table B fields editable with OnValidate triggers, which is actually harder to achieve with flow fields.
  • Simple to implement — No schema changes required; you don’t need to add flow fields to the source table.

Disadvantages

  • No sorting or filtering — Users cannot sort or filter on the Table B columns. The column headers for Table B fields won’t offer the same interactive behavior as source table fields.
  • Global variable pitfalls — Because the variable is global, you must be diligent about clearing it when there’s no matching record to avoid showing stale data.

When to Use Which Approach

Erik’s rule of thumb: if you know for certain that users do not need to filter or sort on the secondary table’s fields, the global variable approach works great and is quick to implement. If users do need filtering and sorting, flow fields are the better choice since they behave like native fields on the source table. And for more complex joins or reporting scenarios, consider the query object.

Summary

Displaying data from two tables on a single list page is a common requirement in Business Central development. The global variable approach demonstrated here — declaring a record variable, populating it in OnAfterGetRecord and OnAfterGetCurrRecord, and binding page fields to that variable — is a lightweight and effective pattern. Just remember to clear the variable when no matching record exists, call SetAutoCalcFields for any flow fields on the secondary table, and handle validation and modification explicitly if you make those fields editable.

Picture of hougaard

hougaard