Functions

This is the reference for everything callable from a formula: the spreadsheet functions, the AL built-ins, the finance functions on the GL global, the members of table-region record globals, and your own init-code procedures.

Spreadsheet Functions

These five are recognised by name and computed by the engine (any other Excel-style need is served by AL's own built-ins):

Function	Result
`SUM(A1:A10)`	Adds the numeric cells. Mixed arguments work: `SUM(A1:A3, B5, 10)`.
`AVERAGE(A1:A10)` / `AVG(...)`	The sum divided by the number of arguments.
`PRODUCT(A1:A10)`	Multiplies the arguments. Each argument keeps its own precedence: `PRODUCT(1+2, 3)` is 9.
`COUNT(A1:A10)`	Counts the cells of the range (the number of arguments after range expansion) — unlike Excel's COUNT, it does not skip empty or non-numeric cells.
`XLOOKUP(Value, LookupRange, ReturnRange [, IfNotFound])`	See below.

Ranges expand up to 20,000 cells per argument. Aggregates nest (SUM(A1:A3, SUM(B1:B3))), and a function name inside a text literal is left alone.

XLOOKUP

=XLOOKUP(Value, LookupRange, ReturnRange [, IfNotFound]) finds the first cell in LookupRange equal to Value and returns the same-position cell from ReturnRange.

Value is an AL expression — B1, a literal, or a nested function.
Numeric values match numeric cells; text matches trimmed and case-insensitively (so Inventory matches a financial report's indented Inventory).
Without a match, the optional fourth argument (an AL expression) is used; without one, the cell shows an error.
The lookup range is limited to 10,000 cells.

Region-aware lookups: when both ranges point inside the same table region (one data column each — one row in the Columns layout), the lookup searches the region's whole filtered table, not just the rendered page. Virtual columns match on their stored plain values. Ranges that cross a region's edge, mix a region with plain cells, or span two regions are errors. Query, report, SQL and pivot regions have no backing table — ranges over them scan the rendered cells like plain ranges.

Practical examples:

A customer's name from a customers region beside the sheet: with a table region CUST rendering No. in A5:A200 and Name in B5:B200, =XLOOKUP(E2, A5:A200, B5:B200, 'Unknown') finds the customer picked in slicer E2 — searching every filtered customer, even beyond the rendered page. This works with either form of the slicer: a ?FIELD cell always hands formulas the customer number, even when a display field (?FIELD(Customer, No., Name)) makes the cell itself show the name.
A figure out of a financial report region: =XLOOKUP('Total Revenue', A5:A40, D5:D40, 0) pulls the revenue amount from the column D figures next to the row descriptions — indentation on the report rows is ignored by the match.
A rate table typed on the sheet: salesperson codes in AA2:AA5 and their commission rates in AB2:AB5, then =B10 * XLOOKUP(C10, AA2:AA5, AB2:AB5, 0.01) — the code in C10 finds its rate, falling back to 1%. Matches are exact (numeric, or trimmed case-insensitive text) — there is no approximate/banded match.

AL Built-ins

Formulas are plain AL expressions, so anything the interpreter evaluates works. The ones the editor offers in IntelliSense:

Function	Result
`Abs(Number)`	Absolute value
`Round(Number, Precision)`	Rounds, e.g. `Round(B2, 0.01)`
`Power(Number, Exponent)`	Exponentiation
`Today()` / `WorkDate()` / `CurrentDateTime()`	Current date / session work date / current date-time
`CalcDate('<1M>', Date)`	Date arithmetic with a date formula
`Format(Value)`	The value as text
`StrLen(Text)` / `CopyStr(Text, Pos, Len)`	Length / substring
`UpperCase(Text)` / `LowerCase(Text)`	Case conversion
`StrSubstNo('%1 of %2', A1, B1)`	Placeholder substitution

Other AL built-ins (e.g. DMY2Date) also work.

The `GL` Global

Every sheet has a built-in GL record global over G/L entries — no region setup needed:

Function	Result
`GL.GLSum(AccountFilter [, DateFilter] [, Dim1Filter] [, Dim2Filter])`	Sum of G/L entry amounts for the account filter, computed as one server-side sum. Example: `=GL.GLSum('40000..49999', C2)` with `C2` a date-range slicer.
`GL.GLBudget(AccountFilter [, DateFilter] [, BudgetName])`	Sum of G/L budget entry amounts the same way.

Arguments are ordinary AL expressions, so a cell reference works: =GL.GLSum('40000..49999', B1). An empty argument skips that filter. Budget-vs-actual in one cell:

=GL.GLBudget('40000..49999', B1) - GL.GLSum('40000..49999', B1)

GL also exposes G/L Entry fields like any record global (=GL.Amount reads the first record). If a table region is itself named GL, that region claims the name.

Table-Region Record Globals

Every table region's ID is a record variable positioned on the region's filtered set:

Member	Result
`ID.Count`	The number of records after the region's filters.
`ID.TableSum("Field Name")`	The sum of a numeric field over the whole filtered set (not just the rendered page). Works on FlowFields. Also sums a virtual column by its name or caption — stored values count directly, per-record formulas are evaluated per record, text is skipped.
`ID.FieldName` / `ID."Quoted Field Name"`	A field of the region's first record.

TableSum only accepts numeric fields; an unknown name reports "neither a field nor a virtual field" on the cell.

Init-Code Procedures

Procedures defined in the sheet's init code are callable from any formula: =SafeDiv(B2, B3), =MonthRevenue('2026-01-01..2026-01-31', E2). They appear in IntelliSense automatically. Pass slicer values as plain cell references — the [Address] token belongs to region filters, not formulas.