Users just love Lookup FlowFields in Business Central

A common request from users is the “add a field here from a related table”. In this video, I show how you can do that using Lookup FlowFields. Check it out:

https://youtu.be/OEnuVJY7FJ8

In this video, Erik demonstrates one of the most popular features users request in Business Central: Lookup FlowFields. Drawing from telemetry data in his Simple Object Designer and support tickets, Erik shows that lookup FlowFields are by far a user favorite — and for good reason. They act as a “poor man’s join,” letting you display data from related tables without storing anything extra in the database.

What Are Lookup FlowFields?

A lookup FlowField is a field that pulls its value from another table at runtime. It’s not stored in the database — it calculates on the fly only when needed. This makes it extremely lightweight:

  • No database storage — the value is calculated, not persisted
  • No population logic needed — you don’t have to write code to keep it in sync when related records change
  • Zero cost when not used — if the field isn’t displayed or read, it has no impact on performance
  • Perfect for “I need this field to show up here” requests — which, as Erik notes, users absolutely love to ask for

The Scenario

The example scenario is straightforward: a customer wants to see the Vendor Item No. from the Item card directly on the Sales Order lines. When entering a sales order, they need this information at their fingertips without having to navigate away to the item card.

Building the Lookup FlowField Step by Step

Step 1: Create the Table Extension

First, we need to add the field to the Sales Line table via a table extension. The key properties are:

  • FieldClass = FlowField — This tells Business Central the field is calculated, not stored. Normally you don’t specify FieldClass because the default is Normal, but for FlowFields you must explicitly set it.
  • Editable = false — FlowFields are typically not editable. Erik mentions a rare exception: the original Budget module allowed users to type a value into a FlowField, and the system would record the difference as a new budget entry. That’s a fascinating pattern, but not the norm.
  • CalcFormula — This is where the magic happens. The syntax is: lookup(DestinationTable."DestinationField" where("FilterField" = field("SourceField")))

Step 2: Create the Page Extension

Next, we place the field on the Sales Order Subform page. Erik takes a moment to explain the naming: “Sales Order Subform” is a legacy term from the old forms-based client in NAV, where a form could contain another form (a “subform”). The sales order lines live on this subform page, which is why we extend it rather than the main Sales Order page.

The field is placed right after the Description column using the addafter anchor.

The Complete Source Code

tableextension 50100 "My Sales Line" extends "Sales Line"
{
    fields
    {
        field(50100; VendorItemNo; Text[50])
        {
            Caption = 'Vendor Item No.';
            FieldClass = FlowField;
            Editable = false;
            CalcFormula = lookup(Item."Vendor Item No." where("No." = field("No.")));
        }
    }
}
pageextension 50100 "My Sales Line" extends "Sales Order Subform"
{
    layout
    {
        addafter(Description)
        {
            field(VendorItemNo; Rec.VendorItemNo)
            {
                ApplicationArea = all;
                DrillDownPageId = 30;
            }
        }
    }
}

Understanding the CalcFormula

Let’s break down the CalcFormula syntax:

  • lookup — We want to look up a single value from another table
  • Item."Vendor Item No." — The destination table and field we want to retrieve
  • where("No." = field("No.")) — The filter that links the Sales Line’s “No.” field (the item number) to the Item table’s “No.” field

Erik notes that the field type (Text[50]) must match the type of the field you’re looking up. He points out that Microsoft could be smarter here — since the type is fully determined by the CalcFormula for FlowFields, specifying it separately is redundant. But for now, you need to ensure they match.

A Note on the Filter Logic

Erik deliberately keeps the filter simple, matching only on the “No.” field. He acknowledges that this could theoretically break if you have a resource or G/L account with the same number as an item. However, in practice, he’s found that clashing numbers across entity types are extremely rare among his customers. And if the lookup doesn’t find a match, the field simply shows as blank — no error, no crash.

DrillDown vs. Lookup Page ID

An interesting discovery during the video: when clicking on the FlowField value, Erik initially tried setting LookupPageId to control which page opens, but found it didn’t work as expected. The correct property for FlowFields is DrillDownPageId. Setting DrillDownPageId = 30 (the Item Card) allows users to click the vendor item number and navigate directly to the related item card.

Performance Considerations

The Good News

Lookup FlowFields are very lightweight for typical use cases. On a sales order with a normal number of lines, the performance impact is negligible. Erik uses them extensively and reports no issues.

Erik also mentions — with the caveat that he’d like Microsoft to confirm — that multiple lookup FlowFields pointing to the same table with the same filters should be combined into a single SQL statement. This means pulling seven fields from the same Item record should cost roughly the same as pulling one. If anyone from Microsoft can confirm this optimization is still in place, Erik invites them to comment.

The Drawbacks

While FlowFields work well for display purposes, there are performance concerns when using them for filtering and sorting:

  • Filtering on a FlowField is expensive — The server has to calculate the value for every record before it can apply the filter, which gives SQL Server “quite a workout”
  • Not suitable for large datasets — If you’re filtering millions of records through a FlowField, you’re going to have a bad time
  • For typical sales order line counts, it works fine, but it’s something to be aware of for high-volume scenarios

Why Users Love Them

Erik’s telemetry from the Simple Object Designer shows that hundreds, even thousands of lookup FlowFields are being created by users. The reasons are clear:

  1. Immediate value — Users get the information they need right where they’re working
  2. No complex development — No need to write validation triggers or synchronization logic
  3. No database bloat — Nothing is stored; nothing needs to be migrated or maintained
  4. Low risk — If you don’t use it, it costs nothing; if the lookup finds no match, the field is simply blank

Summary

Lookup FlowFields are one of the simplest yet most valuable tools in the AL developer’s toolkit. They let you surface related data from other tables without storing anything in the database, without writing population logic, and with minimal performance impact for typical use cases. The next time a user says “I need this field to show up here,” a lookup FlowField is very likely the cleanest and most efficient solution. Just be mindful of the filtering and sorting limitations on large datasets, and you’ll be in great shape.

Picture of hougaard

hougaard