In this video, I show what FlowFields are, and how to create them. FlowFields are one of the cool technologies that ensures that working with totals, balances and other “calculated” fields are an awesome experience.
This is another video in my “Beginning AL” series.
In this video, Erik explores one of Business Central’s most powerful features: FlowFields. He walks through what FlowFields are, how they work behind the scenes, and demonstrates six different types of FlowField calc formulas — lookup, exist, sum, count, average, and max — by building a working example from scratch in AL.
What Are FlowFields?
FlowFields are Business Central’s way of calculating values dynamically. They’re virtual fields — not physically stored in the database in the traditional sense — but they appear and behave like regular fields in the UI. The key difference is that their values are computed on-the-fly based on a CalcFormula that you define.
A great example of FlowFields in action is the Chart of Accounts page. The “Net Change” column is a FlowField. When you click on the value, it drills down into all the ledger entries that make up that calculated amount. Even more powerful, you can apply a flow filter (like a date filter) to change which entries are included in the calculation — and the value updates instantly.
FlowFields and SIFT (SumIndexField Technology)
Behind the scenes, Business Central leverages SumIndexField Technology (SIFT) to make FlowField calculations extremely fast. In SQL Server, this is implemented using indexed views. The practical result is that no matter how many millions of transactions exist, the calculation of a sum FlowField happens in approximately the same (nearly instant) amount of time.
This is a major advantage over the alternative approach of maintaining a separate “balance” field that has to be manually updated every time entries change. With FlowFields, the data is always consistent because it’s always calculated from the source.
Building FlowFields Step by Step
Erik builds a simple test table with a Customer No. field and progressively adds six different FlowFields to demonstrate each calc formula type. Here’s the complete table definition:
table 50142 "FlowField Test Table"
{
fields
{
field(1; "Customer No."; Code[20])
{
TableRelation = Customer."No.";
}
field(2; Name; Text[250])
{
FieldClass = FlowField;
CalcFormula = lookup (Customer.Name where("No." = field("Customer No.")));
}
field(3; "Has Invoices"; Boolean)
{
FieldClass = FlowField;
CalcFormula = exist ("Sales Invoice Header" where("Sell-to Customer No." = field("Customer No.")));
}
field(4; "Invoices Total"; Decimal)
{
FieldClass = FlowField;
CalcFormula = sum ("Cust. Ledger Entry"."Sales (LCY)" where("Document Type" = const(Invoice), "Customer No." = field("Customer No.")));
}
field(5; "Invoices Count"; Integer)
{
FieldClass = FlowField;
CalcFormula = count ("Cust. Ledger Entry" where("Document Type" = const(Invoice), "Customer No." = field("Customer No.")));
}
field(6; "Average Invoice"; Decimal)
{
FieldClass = FlowField;
CalcFormula = average ("Cust. Ledger Entry"."Sales (LCY)" where("Document Type" = const(Invoice), "Customer No." = field("Customer No.")));
}
field(7; "Max Invoice Amount"; Decimal)
{
FieldClass = FlowField;
CalcFormula = max ("Cust. Ledger Entry"."Sales (LCY)" where("Document Type" = const(Invoice), "Customer No." = field("Customer No.")));
}
}
keys
{
key(PK; "Customer No.")
{
}
}
}1. Lookup — Getting a Customer Name
The first FlowField retrieves the customer name without storing it in the table. This is done using the lookup calc formula:
field(2; Name; Text[250])
{
FieldClass = FlowField;
CalcFormula = lookup (Customer.Name where("No." = field("Customer No.")));
}The formula reads: “Look up into the Customer table and grab the Name field where the Customer’s No. matches our Customer No. field.”
The where clause is critical here. Without it, the lookup simply returns the first record in the Customer table — which is clearly wrong. The field() keyword in the where clause creates the dynamic link between the current record’s Customer No. and the Customer table’s No. field. You can also use const() for fixed values.
One caveat: lookup FlowFields are not filter-friendly. They’re great for display purposes (showing a name next to a code), but you shouldn’t rely on filtering by them.
2. Exist — Checking If Invoices Exist
The exist formula returns a Boolean indicating whether any matching records exist in another table:
field(3; "Has Invoices"; Boolean)
{
FieldClass = FlowField;
CalcFormula = exist ("Sales Invoice Header" where("Sell-to Customer No." = field("Customer No.")));
}This checks whether there are any posted sales invoices for the customer. In Erik’s demo, all demo customers showed “Yes” except for one that was his own name — because nobody had invoiced him.
3. Sum — Calculating Invoice Totals
The sum formula is the classic FlowField use case, adding up values from ledger entries:
field(4; "Invoices Total"; Decimal)
{
FieldClass = FlowField;
CalcFormula = sum ("Cust. Ledger Entry"."Sales (LCY)" where("Document Type" = const(Invoice), "Customer No." = field("Customer No.")));
}Notice that this where clause has two conditions separated by a comma: one using const(Invoice) for the document type (IntelliSense is smart enough to recognize enum values here) and one using field("Customer No.") to match the customer.
This particular sum benefits from SIFT because the Customer Ledger Entry table has a key defined with a SumIndexFields property that includes Sales (LCY):
// Key #8 on Cust. Ledger Entry:
// Document Type, Customer No., Posting Date, Currency Code
// SumIndexFields: Sales (LCY), Profit (LCY), Inv. Discount (LCY)Important warning: If you create a sum FlowField that doesn’t hit an existing SumIndexField key, Business Central will still calculate it — but it will do so by scanning the table directly. On a small demo database this feels instant, but on production data with millions of records, it will severely degrade performance. Always make sure your sum FlowFields align with existing SIFT keys.
Erik also notes a current limitation of the extension model: you cannot add SumIndexFields to existing tables via table extensions. This is a highly requested feature but not yet available.
4. Count — Counting Invoice Entries
The count formula counts records rather than summing a specific field, so you don’t need to specify a field name — just the table:
field(5; "Invoices Count"; Integer)
{
FieldClass = FlowField;
CalcFormula = count ("Cust. Ledger Entry" where("Document Type" = const(Invoice), "Customer No." = field("Customer No.")));
}This is similar to the exist formula in that it only references the table (not a specific field), but instead of returning a Boolean, it returns the actual count of matching records.
5. Average — Computing Average Invoice Amount
The average formula works exactly like sum but returns the mean value instead:
field(6; "Average Invoice"; Decimal)
{
FieldClass = FlowField;
CalcFormula = average ("Cust. Ledger Entry"."Sales (LCY)" where("Document Type" = const(Invoice), "Customer No." = field("Customer No.")));
}Erik verified this by dividing the invoices total by the count — the math checked out.
6. Max — Finding the Largest Invoice
The max formula returns the largest value of the specified field among matching records. There’s also a min variant that works identically but returns the smallest value:
field(7; "Max Invoice Amount"; Decimal)
{
FieldClass = FlowField;
CalcFormula = max ("Cust. Ledger Entry"."Sales (LCY)" where("Document Type" = const(Invoice), "Customer No." = field("Customer No.")));
}The Page Definition
All six FlowFields are displayed on a simple list page. One recurring lesson from the video: don’t forget to add your fields to the page after defining them in the table!
page 50142 "FlowField Test"
{
PageType = List;
SourceTable = "FlowField Test Table";
layout
{
area(Content)
{
repeater(Rep)
{
field("Customer No."; "Customer No.")
{
ApplicationArea = All;
}
field(Name; Name)
{
ApplicationArea = All;
}
field("Has Invoices"; "Has Invoices")
{
ApplicationArea = All;
}
field("Invoices Total"; "Invoices Total")
{
ApplicationArea = All;
}
field("Invoices Count"; "Invoices Count")
{
ApplicationArea = all;
}
field("Average Invoice"; "Average Invoice")
{
ApplicationArea = All;
}
field("Max Invoice Amount"; "Max Invoice Amount")
{
ApplicationArea = all;
}
}
}
}
}FlowFields vs. Flow Filters
Erik briefly touches on a related concept that wasn’t fully demonstrated: Flow Filters. These are special filter fields (like the Date Filter on the Chart of Accounts) that feed into FlowField where clauses using the filter keyword instead of field or const. This allows end users to dynamically change which records are included in the FlowField calculation — exactly like filtering the Net Change by date on the Chart of Accounts.
Summary of CalcFormula Types
- Lookup — Retrieves a single field value from a related table
- Exist — Returns true/false based on whether matching records exist
- Sum — Totals a numeric field across matching records
- Count — Counts matching records
- Average — Computes the average of a numeric field across matching records
- Max / Min — Returns the maximum or minimum value of a field across matching records
Key Takeaways
- Use FlowFields instead of maintaining duplicate data. They keep your data consistent and eliminate the risk of balances getting out of sync with entries.
- SIFT makes sums blazing fast — but only if your FlowField aligns with an existing SumIndexField key. Always verify this before deploying to production.
- You cannot add SumIndexFields to existing tables via table extensions — this is a known limitation of the current extension model.
- Lookup FlowFields are for display, not filtering. They’re great for showing related information but perform poorly when used as filter criteria.
- Don’t make cheap imitations. FlowFields are a proven, performant mechanism built into the platform. Use them rather than trying to replicate their behavior with custom code.
