Expression Editor

The expression editor is where you write formulas for calculated fields. This page explains the editor interface and expression syntax.

Accessing the Expression Editor

Open Calculated Fields page
Select a calculated field
Click the Expression cell or Edit Expression action
The expression editor opens

Alternatively, from the Edit Expression page:

Navigate to Tasks > Page Calculated Fields
Find your calculated field
Click to edit expression

Expression Syntax Basics

Field References

Reference fields from the current record:

Rec."Field Name"

Examples:

Rec."Amount"
Rec."Customer Name"
Rec."Quantity"

Which fields are available is determined by the source table of the page the calculated field belongs to. If a field reference you expect is missing from the editor or the Cheat Sheet, it is because that field does not exist on this page's source table — a page based on a different table exposes an entirely different set.

FlowFields can be referenced the same way, with Rec.NameOfFlowField.

In report expressions, the prefix is the data item name rather than Rec:

DataItemName."Field Name"

If the data item or table name itself contains spaces, wrap it in double quotes too:


Correct	`"Cust. Ledger Entry"."Due Date"`
Incorrect	`Cust. Ledger Entry."Due Date"`

The unquoted form fails Verify Expression, and if it slips through it resolves to a blank value. That is why CalcDate('<5D>',Cust. Ledger Entry."Due Date") produces "You cannot base a date calculation on an undefined date. Date: 0D Formula: 5D." — the date resolved to 0D. Adding the quotes fixes it.

Literal Values

Use literal values directly:

100              // Integer
100.50           // Decimal
'Text Value'     // Text (single quotes)
"Text Value"     // Text (double quotes)
true             // Boolean
false            // Boolean
20240101D        // Date literal

Arithmetic Operators

Operator	Description	Example
+	Addition	`Rec."A" + Rec."B"`
-	Subtraction	`Rec."Total" - Rec."Discount"`
*	Multiplication	`Rec."Qty" * Rec."Price"`
/	Division	`Rec."Amount" / 100`
mod	Modulo	`Rec."Count" mod 10`
div	Integer division	`Rec."Total" div 4`

Comparison Operators

Operator	Description	Example
=	Equals	`Rec."Status" = 'Open'`
<>	Not equals	`Rec."Blocked" <> true`
>	Greater than	`Rec."Amount" > 0`
<	Less than	`Rec."Qty" < 10`
>=	Greater or equal	`Rec."Date" >= Today()`
<=	Less or equal	`Rec."Discount" <= 50`

Logical Operators

Operator	Description	Example
and	Logical AND	`(A > 0) and (B > 0)`
or	Logical OR	`(A = 0) or (B = 0)`
not	Logical NOT	`not Rec."Blocked"`

String Operators

Operator	Description	Example
+	Concatenation	`Rec."First" + ' ' + Rec."Last"`

Conditional Expressions

If you wrote if ... then ... else, you will see this error
Syntax error: is an unknown identifier (line 0)
This does not mean a field name is wrong or a quote is missing. It means the if / then / else statement form is not supported at all in calculated field expressions — nor is else if, CASE WHEN, or any other statement syntax.

Rewrite the logic with the ternary operator, chaining ternaries for multiple branches:

Before (fails):
if Rec."Field" = 'A' then Rec."X" * 1.255 else if Rec."Field" = 'B' then Rec."X" * 1.135 else 0
After (works):
Rec."Field" = 'A' ? Rec."X" * 1.255 :
Rec."Field" = 'B' ? Rec."X" * 1.135 :
0

Calculated field expressions do not support AL's if ... then ... else statement. Use the ternary operator instead — it is an expression (returns a value) rather than a statement.

Ternary Operator

[condition] ? [value1] : [value2]

Example:

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

The part before ? must be a Boolean condition — a comparison using =, <>, >, < and so on:

Rec."Source Type" = 37 ? Rec."Field A" : Rec."Field B"

Putting a bare field reference or a plain value there instead of a comparison is the most common mistake, and it produces a "Type mismatch" error at verification — often reported as "Boolean vs Integer" even when every field involved is an Integer. If you see a type mismatch on an expression whose types clearly agree, check the condition part of the ternary first.

Two things to know about the ternary operator:

It requires Business Central version 25 or later at runtime. On earlier versions it will not work.
The SOD expression verifier does not understand it. Verification may report an error on a perfectly valid ternary expression. Conversely, a genuinely malformed ternary will not be caught during verification — the error only surfaces at publish time.

Microsoft's reference is the Business Central release plan article "Use ternary operator when coding in AL".

Nested Conditions

Chain ternary operators to handle multiple branches:

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

Multiple Conditions

Combine with and / or — wrap each comparison in parentheses:

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

Working with Types

Type Compatibility

Ensure expression results match the field type:

Field Type	Expression Must Return
Decimal	Numeric value
Text	Text/String value
Boolean	true or false
Date	Date value

Type Conversion

Convert between types when needed:

Format(Rec."Amount")           // Decimal to Text
Format(Rec."Date")             // Date to Text

Handling Null and Empty Values

Checking for Empty

Rec."Customer Name" = '' ? 'No Customer' : Rec."Customer Name"

Avoiding Division by Zero

Rec."Total" <> 0 ? Rec."Part" / Rec."Total" * 100 : 0

Default Values

Rec."Description" = '' ? 'N/A' : Rec."Description"

Common Expression Patterns

Calculated Amount

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

Amount with Discount

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

Full Name

Rec."First Name" + ' ' + Rec."Last Name"

Address Block

Rec."Address" + ', ' + Rec."City" + ' ' + Rec."Post Code"

Days Until Due

Rec."Due Date" - Today

Days Overdue

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

Status Indicator

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

Percentage

Rec."Total Amount" <> 0 ?
  Round(Rec."Line Amount" / Rec."Total Amount" * 100, 0.01) :
  0

Expression Validation

Syntax Checking

The editor checks for:

Valid operator usage
Balanced parentheses
Correct field references
Type compatibility

Error Messages

Common validation errors:

Error	Cause	Fix
"Field not found"	Field name typo, or the field is not on this page's source table	Check spelling and the page's source table
"Type mismatch"	Incompatible types — or a ternary whose condition is not a comparison	Fix the condition, add conversion, or change the field's Type
"Invalid syntax"	Structural error	Check operators and brackets
`Syntax error: is an unknown identifier (line 0)`	`if / then / else` used instead of the ternary operator	Rewrite with `? :`
Type mismatch on a report expression, or a date resolving to `0D`	Multi-word data item name not wrapped in double quotes	Use `"Cust. Ledger Entry"."Due Date"`

The verifier is not always right

The expression verifier does not parse every valid construct, and it can report an error on an expression that compiles and runs perfectly well. Known cases:

Ternary expressions — the verifier does not understand the operator at all
Date subtraction — the verifier (which runs on the ToolBox app) has a known bug where it misreads date-minus-date and similar date-difference expressions. A fix is scheduled for an upcoming release.

Workaround: when you are confident the syntax is correct, publish anyway and check the field on the page. A verification error does not necessarily mean the expression will fail.

But check the syntax first. Many "verifier" errors are genuine mistakes — particularly a ternary whose condition is not a comparison. And remember that verification passing is no guarantee either: it does not validate every identifier, so a name starting with a number will still break the publish.

Expression Cheat Sheet

Access the Expression Cheat Sheet for quick reference:

Common patterns
Function examples
Syntax reminders

Testing Expressions

Verify Calculation

After publishing:

Open the page with test data
Check the calculated value
Manually verify the calculation
Test with different data scenarios

Edge Cases to Test

Zero values
Empty/null values
Negative numbers
Boundary dates
Very long text

Best Practices

Keep It Simple

Break complex logic into smaller parts
Use clear variable references
Avoid deeply nested conditions

Document Your Logic

Comment complex expressions (in external documentation)
Explain business rules
Track expression changes

Performance Awareness

Simple expressions = faster page load
Complex expressions = slower page load
Consider alternatives for heavy calculations