ELI5 How to use FlowFields

Another installment in the Explain (it) Like I’m 5. This time, I tackle FlowFields, what they are, and how to use them. Together, we decipher the somewhat cryptic syntax for defining them. Check it out:

https://youtu.be/GBzfySWmL94

In this ELI5 (Explain Like I’m 5) video, Erik walks through everything you need to know about FlowFields in Business Central — what they are, how they work under the hood, the different types you can create, and when you should (or shouldn’t) use them. He demonstrates building FlowFields from scratch using a table extension on the Customer table, covering all the major CalcFormula types: Sum, Count, Exist, and Lookup.

What Is a FlowField?

A FlowField is a field that gets calculated on the fly. It’s defined in AL as part of a table, but it does not exist as a column in the SQL database. When you look at the Customer list in Business Central, fields like Balance, Balance Due, Sales, and Payments are all FlowFields — they’re calculated each time the page renders or the cursor moves to a new record.

Because FlowFields don’t exist in the database, certain database operations can’t be performed on them directly. That said, Microsoft has provided the ability to sort on FlowFields, even though sorting is traditionally a database operation.

Fun fact: there are 607 summarizing FlowFields in the base application. (Erik counted them for unrelated reasons.)

How FlowFields Are Calculated

When you click on a FlowField value in Business Central, it becomes a drill-down link. Clicking it unfolds the calculation — showing you the individual records that make up the total. For example, clicking on a customer’s Balance field shows you the individual customer ledger entries that were summed together.

This is the magic of FlowFields: no totals or balances are physically stored anywhere in the SQL database. Everything is calculated when needed.

FlowFilters: Passing Filters to FlowFields

Business Central also has FlowFilter fields. These are not real database fields either — they exist only to hold filter values that can be passed to FlowField calculations. When you use “Filter Totals” on a list page, you’re setting FlowFilter values.

For example, setting a Date Filter on the Customer list will affect FlowFields that reference the date filter in their CalcFormula — but it will have no effect on FlowFields that don’t use it. The Balance field on the Customer table, for instance, doesn’t use the Date Filter, so changing it won’t change the Balance. But Balance Due does.

In the source code, a FlowFilter field looks like this — it has FieldClass = FlowFilter and typically has a table relation for lookups, but you can only assign a filter to it, never a value.

Performance: SumIndex Fields (SIFT)

A common question is: “If there are a million entries behind a FlowField, isn’t it slow?” The answer is nuanced. If a FlowField is backed by a SumIndex field (part of the SIFT — Sum Index Field Technology), then it doesn’t matter whether there are two entries or two million entries — the calculation takes roughly the same amount of time.

SumIndex fields are defined on table keys. For example, in the Detailed Customer Ledger Entry table, certain keys have SumIndexFields specified for Amount and Amount (LCY). Business Central and SQL Server work together to maintain optimized aggregation structures behind the scenes.

The trade-off is that maintaining SumIndex fields adds a small overhead to every insert and update operation, and uses slightly more database space. So you should consider:

  • Use SumIndex fields when the FlowField appears on a list page or is calculated frequently, especially with large datasets.
  • Skip SumIndex fields when the FlowField is rarely used (e.g., only on a specific card page), or when you know the number of underlying records is small and fixed (e.g., 10–20 records).

How FlowFields Are Defined in AL

Let’s look at how Microsoft defines the Balance field on the Customer table. The key properties are:

  • FieldClass = FlowField — This marks the field as a FlowField rather than a normal (stored) field.
  • CalcFormula — The formula that describes how to calculate the value and what type of FlowField it is.
  • Editable = false — FlowFields should almost always be non-editable.

The CalcFormula for Balance is a Sum of the Amount field on Detailed Customer Ledger Entries, filtered by the customer number, global dimension filters, and currency filter. The filters reference FlowFilter fields on the Customer table, creating the connection between user-specified filters and the FlowField calculation.

Building FlowFields: A Hands-On Walkthrough

Erik demonstrates building FlowFields by creating a table extension on the Customer table. Here’s the complete source code from the video:

namespace DefaultPublisher.ELI5FlowFields;

using Microsoft.Sales.Customer;
using Microsoft.Purchases.Vendor;
using Microsoft.Purchases.Payables;

tableextension 50100 CustomerTable extends Customer
{
    fields
    {
        field(50100; MyFlow; Decimal)
        {
            FieldClass = FlowField;
            //CalcFormula = count(Customer where("Customer Posting Group" = filter('DOMESTIC')));
            //CalcFormula = exist(Customer where("Customer Posting Group" = filter('FOREIGN')));
            //CalcFormula = lookup(Vendor.Name where("No." = field("No.")));
            CalcFormula = - sum("Detailed Vendor Ledg. Entry".Amount where("Vendor No." = field("No.")));
            Editable = false;
        }
    }
}
pageextension 50100 CustomerList extends "Customer List"
{
    layout
    {
        addafter(Name)
        {
            field(MyFlow; Rec.MyFlow)
            {
                ApplicationArea = all;
                //DrillDownPageId = "Vendor Card";
            }
        }
    }
}

Erik iterates through several versions of the CalcFormula during the video, demonstrating each FlowField type. Let’s walk through them.

Count

The Count type counts the number of records matching the specified filters. It must return an Integer or BigInteger — you can’t have half a record!

field(50100; MyFlow; Integer)
{
    FieldClass = FlowField;
    CalcFormula = count(Customer where("Customer Posting Group" = filter('DOMESTIC')));
    Editable = false;
}

With no filters, this simply counts all customers (returning 5 for every record in Erik’s demo). Adding the where clause with a filter on Customer Posting Group narrows it down to only domestic customers.

An important syntax note: when using filter(), the value must be a string in single quotes. Writing filter(DOMESTIC) without quotes produces a confusing error message about the field reference not existing — but the real issue is just missing quotes.

Exist

The Exist type returns a Boolean — either records matching the criteria exist (true) or they don’t (false). This is handy when you simply need to know whether something is present in the database.

field(50100; MyFlow; Boolean)
{
    FieldClass = FlowField;
    CalcFormula = exist(Customer where("Customer Posting Group" = filter('FOREIGN')));
    Editable = false;
}

Lookup

The Lookup type pulls a single field value from another table. The field type must match the type of the field being looked up. For example, looking up a Vendor’s Name requires Text[100]:

field(50100; MyFlow; Text[100])
{
    FieldClass = FlowField;
    CalcFormula = lookup(Vendor.Name where("No." = field("No.")));
    Editable = false;
}

This looks up the name of the vendor whose number matches the current customer’s number. When you use field() in the where clause, you’re referencing a field on the current record (the customer in this case).

Lookup FlowFields are also drillable. By default, clicking the value opens the default list page for the source table, but you can customize this with the DrillDownPageId property on the page field:

field(MyFlow; Rec.MyFlow)
{
    ApplicationArea = all;
    DrillDownPageId = "Vendor Card";
}

Lookup FlowFields are excellent for pulling in display information (like names or descriptions) from related tables without duplicating data. Erik notes he uses them a lot. The caveat is performance — they’re great for navigation and display purposes, but if you need to heavily filter or sort on a value, a real (stored) field is a better choice.

Sum (with Sign Reversal)

The Sum type adds together values from a specified field across matching records. The final version in the video sums vendor ledger entry amounts, reversing the sign with a minus prefix:

field(50100; MyFlow; Decimal)
{
    FieldClass = FlowField;
    CalcFormula = - sum("Detailed Vendor Ledg. Entry".Amount where("Vendor No." = field("No.")));
    Editable = false;
}

The minus sign before sum reverses the sign of the result. This is commonly used when accounting conventions mean the stored values are negative but you want to display them as positive (or vice versa).

Other Types: Average, Min, Max

Erik also mentions three math-inspired FlowField types that he doesn’t demo in detail:

  • Average — calculates the average of a field across matching records (e.g., average invoice size)
  • Min — finds the smallest value
  • Max — finds the largest value

Special CalcFormula Options: Upper Limit

There are some rarely used CalcFormula options that Erik briefly mentions. You can filter to the upper limit of a filter range. Microsoft uses this primarily on the G/L Account table for fields like “Balance at Date” and “Budget at Date” — these take only the upper limit of the Date Filter regardless of the starting date. It’s a very specific scenario that most developers won’t encounter often.

The Three Filter Types in CalcFormula

When specifying filters in a where clause, you have three options:

  1. const() — A fixed constant value
  2. field() — References a field on the current record, creating a dynamic filter
  3. filter() — A filter expression (string), allowing wildcards and ranges

Important: Always Set Editable = false

Whenever you create a FlowField, you should always set Editable = false. There are some edge cases where an editable FlowField makes sense (Erik mentions there’s a separate video on that topic), but as a general rule, making FlowFields non-editable prevents unexpected behavior when users attempt to type values into a calculated field.

Summary

FlowFields are one of the defining features of Business Central’s architecture. They allow totals, counts, existence checks, and lookups to be calculated on the fly without storing redundant data in the database. Here are the key takeaways:

  • FlowFields don’t exist in the SQL database — they’re calculated at runtime.
  • There are six types: Sum, Count, Exist, Lookup, Average, Min, and Max.
  • FlowFilters are companion fields that hold filter values passed to FlowField calculations.
  • SumIndex fields (SIFT) make Sum FlowFields fast regardless of data volume — but add overhead to inserts and updates.
  • Use SumIndex fields for frequently calculated FlowFields with large datasets; skip them for rare or small-scale calculations.
  • Always set Editable = false on your FlowFields.
  • Lookup FlowFields are great for pulling display data from related tables without duplicating it.
Picture of hougaard

hougaard