orchardcore-data-formulas

Orchard Core Data Formulas and Filters

The Data Pipelines Core feature (Orchard Core 4.0) includes a formula language, used by the Filter rows and Add calculated fields steps of data pipelines (see orchardcore-data-pipelines) and by features such as reports, and the filter operators that consumers offer to data sources (see orchardcore-data-sources). A formula computes a value from a row, such as [Price] * [Quantity] or UPPER(TRIM([Country])).

Code lives in the OrchardCore.DataSources.Expressions and OrchardCore.DataSources namespaces (packages OrchardCore.DataSources.Core and OrchardCore.DataSources.Abstractions, version 4.*). Register functions in a module that depends on OrchardCore.DataPipelines.Core, or in a [RequireFeatures("OrchardCore.DataPipelines.Core")] startup.

Syntax

Element	Syntax
Field	Technical name in brackets, [TitlePart.Title]. A ] in a name is doubled: [Size [cm]]] for Size [cm].
Text	Single or double quotes, 'CA' or "CA". Double a quote to escape it: 'It''s'.
Number	Digits with a dot, 42, 0.25. Without a dot it is an integer.
Constants	TRUE, FALSE, NULL
Function	ROUND([Amount], 2)
Parentheses	([Price] + [Shipping]) * 1.15

Keywords and function names are case-insensitive; field names are not.

Operators

From the lowest precedence to the highest:

1. OR or ||
2. AND or &&
3. NOT or !
4. Comparisons = or ==, != or <>, <, <=, >, >=
5. & joins two values as text (a missing value joins as empty text)
6. +, -
7. *, /, %
8. Unary -, +

Rules:

• A missing value in arithmetic gives a missing result, and so does a division by zero. Integer arithmetic that overflows continues with decimals.
• + joins text when one side is text.
• Date plus or minus a number adds or removes days; date minus date gives the number of days between them.
• A missing value equals another missing value or an empty text; <, <=, >, >= are false when either side is missing.
• A date compared with a text converts the text to a date: [CreatedUtc] >= '2025-01-01'. Text comparisons ignore case.
• A value is true when it is TRUE, a non-zero number, or a text other than empty, false or 0.
• Functions return a missing value rather than failing when an argument is missing or of the wrong type.
• A formula is checked before it runs: every field must exist, every function must get the right number of arguments, and operators must suit their operand types. Pipeline steps report an invalid formula as an error of the step.