Even with all the great documentation that Business Central has gained over the last few years, there are still corners that remain mostly undocumented. While adding support for FlowFields in the Simple Object Designer, I came across one such thing. Check out the video:
In this video, Erik explores one of the most obscure and underdocumented keywords in AL: upperlimit. After polling his Twitter audience, he found that roughly 80% of respondents had no idea what it does. With virtually no official documentation available, Erik walks through a practical demonstration showing exactly how upperlimit works in FlowField calc formulas and why it exists — primarily to enable “Balance at Date” calculations in Business Central.
The Mystery of UPPERLIMIT
Erik stumbled into this topic while implementing FlowField support in the Simple Object Designer. He wanted to understand every option available for calc formulas, and upperlimit turned out to be one of the most obscure features he encountered. A quick search for “Business Central upperlimit” yields very little — it’s mentioned in passing within calc formula documentation, but there’s no dedicated explanation of what it actually does.
The keyword is so rarely discussed that even the AL Language extension’s tooltip simply describes it as the “upper limit formula keyword token” — clearly a technical term from the parser rather than a user-friendly description.
Understanding the Problem: Net Change vs. Balance at Date
To understand why upperlimit exists, Erik demonstrates using the Chart of Accounts page. There are two key FlowFields on the G/L Account table:
- Net Change — Shows the sum of entries within a specific date range
- Balance at Date — Shows the cumulative balance up to a specific date
When no date filter is applied, both fields show the same values. But when you set a date filter (for example, 01/01/2022..12/31/2022), they behave very differently:
- Net Change updates to show only entries within that date range
- Balance at Date shows the cumulative balance up to the end date of the filter, ignoring the start date
When Erik drills down into the Balance at Date value, it includes entries from prior years (2021 and 2022), while Net Change only shows entries from the filtered year. This is the core difference that upperlimit makes possible.
Building a FlowField Step by Step
To demonstrate, Erik creates a table extension on the G/L Account table with a new FlowField. He starts with a basic sum formula:
tableextension 66100 "My COA" extends "G/L Account"
{
fields
{
field(66100; MyBalance; Decimal)
{
FieldClass = FlowField;
CalcFormula = sum("G/L Entry".Amount where("G/L Account No." = field("No."),
"Posting Date" = field("Date Filter")));
}
}
}This basic version — using field("Date Filter") without upperlimit — behaves exactly like Net Change. When you apply a date filter of 01/01/2021..12/31/2021, it only sums entries within that range. Both the start and end of the filter are applied to the Posting Date.
How UPPERLIMIT Changes the Behavior
The magic happens when you wrap the field reference with upperlimit():
tableextension 66100 "My COA" extends "G/L Account"
{
fields
{
field(66100; MyBalance; Decimal)
{
FieldClass = FlowField;
CalcFormula = sum("G/L Entry".Amount where("G/L Account No." = field("No."),
"Posting Date" = field(upperlimit("Date Filter"))));
}
}
}What upperlimit does is take a range filter and only use the upper (end) portion of it. When a user sets a date filter of 01/01/2022..12/31/2022:
- Without upperlimit: The query filters Posting Date from January 1, 2022 to December 31, 2022 — giving you a net change
- With upperlimit: The query filters Posting Date from the earliest possible date up to December 31, 2022 — giving you a balance at date
In other words, upperlimit slices the filter in half and only applies the top (upper) boundary, effectively converting a range filter into a “less than or equal to” filter.
The Page Extension
To visualize the results, Erik adds the new field to the Chart of Accounts page:
pageextension 66100 "My COA Page" extends "Chart of Accounts"
{
layout
{
addafter("Net Change")
{
field(MyBalance; Rec.MyBalance)
{
ApplicationArea = all;
}
}
}
}How the Base Application Uses It
Looking at the base application’s G/L Account table (Table 15) confirms this pattern. The Net Change and Balance at Date fields have nearly identical calc formulas, with one key difference:
- Net Change: Uses
"Posting Date" = field("Date Filter")— applies the full date range - Balance at Date: Uses
"Posting Date" = field(upperlimit("Date Filter"))— only applies the upper end of the date range
This is the mechanism that makes the “Balance at Date” concept work throughout Business Central.
Summary
The upperlimit keyword is a specialized calc formula modifier that takes a range filter and only applies the upper boundary. Its primary use case is building “Balance at Date” FlowFields, where you want to sum all entries from the beginning of time up to a specified date, rather than summing entries within a specific date range. While it’s an extremely niche feature — Erik notes it’s “beyond simple” and likely used in very few spots — understanding it demystifies one of the more obscure corners of AL development and explains how one of the most fundamental concepts in financial reporting actually works under the hood in Business Central.
