Functions and Patterns

This page lists the functions known to be available in calculated field expressions and shows common calculation patterns.

Verifying what's available. SOD ships an authoritative Cheat Sheet in the expression editor (click the Cheat Sheet action). Functions shown there are the ones SOD's interpreter is known to expose. Anything not in the cheat sheet should be tested before relying on it — verify with the Verify Expression action in the editor.

Math Functions

The interpreter exposes the Math.* namespace for numeric work. Available functions (from the cheat sheet):

Function	Purpose
`Math.Abs(x)`	Absolute value
`Math.Sign(x)`	Sign of x (-1, 0, 1)
`Math.Truncate(x)`	Drops decimals toward zero
`Math.Floor(x)`	Largest integer <= x
`Math.Ceiling(x)`	Smallest integer >= x
`Math.Min(a, b)`	Smaller of two values
`Math.Max(a, b)`	Larger of two values
`Math.Pow(b, e)`	b raised to the e
`Math.Sqrt(x)`	Square root
`Math.Log(x)`	Natural log
`Math.Log10(x)`	Base-10 log
`Math.Exp(x)`	e^x
`Math.E()`	Constant e
`Math.Pi()`	Constant π
`Math.IEEERemainder(a, b)`	IEEE 754 remainder
`Math.BigMul(a, b)`	64-bit product of two 32-bit ints
`Math.Sin(x)` / `Math.Cos(x)` / `Math.Tan(x)`	Trig
`Math.Asin(x)` / `Math.Acos(x)` / `Math.Atan(x)` / `Math.Atan2(y, x)`	Inverse trig
`Math.Sinh(x)` / `Math.Cosh(x)` / `Math.Tanh(x)`	Hyperbolic trig

Examples:

Math.Abs(Rec."Variance")           // Positive variance
Math.Pow(Rec."Value", 2)           // Value squared
Math.Sqrt(Rec."Area")              // Square root of area
Math.Floor(Rec."Amount")           // Round down to integer
Math.Min(Rec."Qty Available", Rec."Qty Requested")

Note: the cheat sheet does not list a generic Round(value, precision). To round to a given precision, scale and use Math.Truncate / Math.Floor / Math.Ceiling:
Math.Truncate(Rec."Amount" * 100) / 100   // truncate to 2 decimals
Math.Floor(Rec."Amount" * 100 + 0.5) / 100 // round to 2 decimals

Text Functions

The interpreter is observed to support these AL-style text helpers (used internally by SOD when generating mandatory-field expressions, and listed in the cheat sheet):

Function	Purpose	Example
`strlen(text)`	Length of text (lowercase)	`strlen(Rec."Name")`
`strpos(text, substr)`	Position of substring (0 = not found)	`strpos(Rec."Description", 'urgent')`

Standard AL text functions such as Format, CopyStr, StrSubstNo, UpperCase, LowerCase, PadStr, and DelChr are common in BC and typically also work, but they are not in SOD's curated cheat sheet — verify each one in the expression editor before relying on it:

Format(Rec."Amount")
StrSubstNo('%1 - %2', Rec."No.", Rec."Name")
CopyStr(Rec."Description", 1, 50)
UpperCase(Rec."Code")

Date and Time Functions

Function	Purpose
`Today()`	Current system date
`CurrentDateTime()`	Current system datetime
`Time()`	Current system time
`CalcDate(formula, base)`	Date arithmetic using a date formula

Examples:

Today()                                  // Current date
CalcDate('<1W>', Today())                // One week from today
CalcDate('<-1W>', Today())               // One week ago
CalcDate('<3D>', Rec."Posting Date")     // 3 days after posting date
Rec."Due Date" - Today()                 // Days until due (Integer)

Today() requires the parentheses — bare Today is not the same call.

WorkDate and Date2DMY are common AL functions but are not in SOD's cheat sheet — verify in the expression editor if you need them.

Date minus date returns an Integer — set the field Type accordingly. Subtracting one Date from another (Rec."Due Date" - Today()) yields a number of days, so the calculated field's Type must be Integer. Leaving it as Date, Decimal or Duration produces a data conversion / cast error ("Integer vs Date") at publish. Business Central does not implicitly coerce the result to the declared type.

Note also that the expression verifier has a known bug with date-difference expressions and may flag a correct one — see Expression Editor.

Type Literals

These literals can be written directly into an expression:

Form	Type	Example
`123`	Integer	`123`
`123.456`	Decimal	`123.456`
`'text'`	Text	`'Hello'`
`''`	Empty text	`''`
`true` / `false`	Boolean	`true`
`20240101D`	Date	`20240101D`
`0D`	Blank date	`0D`
`170000T`	Time	`170000T`
`0T`	Midnight	`0T`
`0DT`	Blank DateTime	`0DT`

Boolean Expressions

Simple Conditions

Rec."Amount" > 0
Rec."Status" = 'Open'
Rec."Blocked"
not Rec."Active"

Combined Conditions

(Rec."Amount" > 0) and (Rec."Status" = 'Open')
(Rec."Type" = 'A') or (Rec."Type" = 'B')

Conditional Values (Ternary Operator)

Calculated field expressions do not support AL's if ... then ... else statement. Use the ternary operator instead:

condition ? valueIfTrue : valueIfFalse

The ternary operator is an expression (it returns a value), unlike if/then/else which is a statement. Every branch must return a value of a type compatible with the calculated field.

Three things that trip people up:

Writing if, then, else or else if produces the verification error Syntax error: is an unknown identifier (line 0). That is the symptom of using statement syntax where an expression is required.
The part before ? must be a comparison, not a bare field reference — Rec."Source Type" = 37, not Rec."Source Type". Getting this wrong produces a type mismatch error at verification.
The SOD verifier does not recognize the ternary operator, so a verification error does not necessarily mean the expression is invalid. Ternary expressions need Business Central 25 or later at runtime.

Simple example:

Rec."Amount" > 1000 ? 'Large' : 'Small'

Nested ternary (chained conditions):

Rec."Amount" > 10000 ? 'Extra Large' :
Rec."Amount" > 1000  ? 'Large' :
                       'Standard'

Combine with and / or for multiple conditions — wrap each sub-condition in parentheses:

(Rec."Status" = 'Open') and (Rec."Amount" > 0) ? 'Active' : 'Inactive'

Referencing Other Calculated Fields

Within an expression you can call another calculated field on the same page or report by appending Calc() to its name (case-insensitive):

TotalCalc() * 0.1

Limitation — same row scope only. A <Name>Calc() reference works only within the same data item and the same row. It does not aggregate across the rows of a different data item: calling a line-scoped calculated field's Calc() from a header-scoped expression returns a single per-row value (effectively the first line), not a total. To total values across lines, use a FlowField.

Common Calculation Patterns

Line Amount

Rec."Quantity" * Rec."Unit Price"

Line Amount with Discount

Rec."Quantity" * Rec."Unit Price" * (1 - Rec."Line Discount %" / 100)

Profit Margin (to two decimals)

Rec."Sales Amount" <> 0 ?
  Math.Truncate((Rec."Sales Amount" - Rec."Cost Amount") / Rec."Sales Amount" * 10000) / 100 :
  0

Days Overdue

(Rec."Due Date" < Today()) and (Rec."Due Date" <> 0D) ?
  Today() - Rec."Due Date" :
  0

Age in Days

Rec."Created Date" <> 0D ? Today() - Rec."Created Date" : 0

Status Text

Rec."Blocked" ? 'Blocked' :
Rec."On Hold" ? 'On Hold' :
Rec."Active"  ? 'Active' :
                'Inactive'

Priority Indicator

Rec."Priority" = 1 ? 'High' :
Rec."Priority" = 2 ? 'Medium' :
                     'Low'

Percentage Complete

Rec."Total Quantity" <> 0 ?
  Math.Truncate(Rec."Completed Quantity" / Rec."Total Quantity" * 1000) / 10 :
  0

Yes/No Display

Rec."Boolean Field" ? 'Yes' : 'No'

Absolute Variance

Math.Abs(Rec."Actual" - Rec."Planned")

Larger of Two Quantities

Math.Max(Rec."Qty On Hand", Rec."Qty Reserved")

Expression Tips

Operator Precedence

Operations evaluate in this order:

Parentheses (highest)
not
*, /, div, mod, and
+, -, or
=, <>, <, >, <=, >=
? : (ternary, lowest)

Use parentheses to clarify — especially around the condition of a ternary when it combines comparisons with and / or:

(Rec."A" + Rec."B") * Rec."C"
(Rec."Amount" > 0) and (Rec."Status" = 'Open') ? 'Active' : 'Inactive'

Avoiding Errors

Always check for:

Division by zero (use a ternary guard with <> 0)
Empty strings when concatenating
Blank dates (<> 0D) before subtracting

Type Consistency

Ensure your expression returns the correct type:

Decimal / Integer fields need numeric results
Text fields need string results
Boolean fields need true/false
Date fields need a date result
Date minus date returns an Integer — set the field Type to Integer
Duration fields need a duration result — but note that Duration expressions are not currently evaluable by the interpreter/verifier. Support is planned for a future release; until then do not create calculated fields with the Duration result type.

More Calculation Patterns

Difference Between Two Amounts

The simplest and most-requested pattern — a remaining or outstanding amount:

Rec."Field A" - Rec."Field B"

Set the field Type to Decimal. Both fields must exist on the page's source table.

Category-Driven Multiplier

Choose a factor based on a code field:

Rec."VAT Prod. Posting Group" = '25,5' ? Rec."Unit Price" * 1.255 :
Rec."VAT Prod. Posting Group" = '13,5' ? Rec."Unit Price" * 1.135 :
0

Note the quoting: code literals are text and use single quotes ('25,5'); double quotes are reserved for field names (Rec."Unit Price").

Months Contained in a DateFormula

Limitation: you cannot convert a text value to an integer inside an expression — AL does not support it — so a DateFormula such as 36M cannot be parsed directly into the number 36.

Workaround: apply the formula to today with CalcDate, then use Date2DMY to compare the components. Date2DMY(date, 3) returns the year, (date, 2) the month, (date, 1) the day.

With the DateFormula in Rec."Initial Term":

((Date2DMY(CalcDate(Rec."Initial Term", Today()), 3) - Date2DMY(Today(), 3)) * 12)
  + (Date2DMY(CalcDate(Rec."Initial Term", Today()), 2) - Date2DMY(Today(), 2))

Date2DMY is not in SOD's curated cheat sheet, so verify it in the expression editor before relying on it.

The resulting month count can then drive further calculations — multiplying it by a price field to produce a whole-of-life total, for example.

Formatting Values as Text

Business Central does not support adding a $ prefix, a % suffix, or similar symbol formatting directly to a numeric field.

To display a symbol alongside a computed value, create a Text-typed calculated field and use the FORMAT command, which converts a number into text:

'$' + FORMAT(<numerical calculation>,0,<formatting>)
FORMAT(<numerical calculation>,0,<formatting>) + '%'

The field's Type must be Text. Leaving it as Decimal produces:

Syntax error: Line X - Type mismatch (Decimal vs. Text)

Trade-off: a text field loses the right justification a numeric field has.

FlowFields on cues and the home page. A Sum FlowField — for example one totalling Cust. Ledger Entries for an Activities cue — cannot itself be converted to text, because changing the Type away from FlowField removes the definition of what it sums. Business Central does not support a $ prefix on such a field either.

The approach is a dual solution: keep the numeric FlowField doing the sum, and add a separate Text calculated field that formats its value for display.

Microsoft references:

Currency Factor and Local Currency

By design, the Currency Factor is 1 when the currency is the local currency.

That means Amount * Currency Factor normally works for all records without any conditional logic — the local-currency case simply resolves to Amount * 1.

If Currency Factor shows as 0 on local-currency records, that is not expected behaviour and points to a separate data issue. In that situation a ternary guard may be needed as a workaround.