Mapping functions v2

Mapping functions transform, test, select, and aggregate values in Expressions. Console obtains this list and its signatures from the current runtime, so autocomplete is the final check when a newer runtime adds or changes a function.

Function names are shown in their canonical uppercase form. Parameters ending in ... accept more than one value. Parameters with a displayed default are optional.

Text functions

Function	Purpose
`LEN(string value)`	Returns the string length; `null` returns `0`.
`LEFT(string value, number numChars)`	Returns the leftmost characters, clamping the requested length.
`RIGHT(string value, number numChars)`	Returns the rightmost characters, clamping the requested length.
`MID(string value, number start, number numChars)`	Returns characters from a zero-based start position.
`LEFTOF(string value, string text, boolean caseSensitive = false)`	Returns text before the first match, or an empty string.
`RIGHTOF(string value, string text, boolean caseSensitive = false)`	Returns text after the first match, or an empty string.
`MIDOF(string value, string startString, string endString, boolean caseSensitive = false)`	Returns text between the first ordered pair of markers.
`TEXTJOIN(string delimiter, boolean ignoreEmpty, object... items)`	Joins scalar values or materialized `PLUCK` results.
`CONCAT(string... items)`	Concatenates strings; `null` items contribute an empty string.
`SPLIT(string list, string separator)`	Splits text into an array and preserves empty fields.
`SPLIT_NUMBER(string list, string separator)`	Splits text into invariant-culture numbers; an invalid item becomes `0`.
`REPLACE(string value, string find, string replaceWith)`	Replaces all matching text.
`FIND(string findText, string withinText, boolean caseSensitive = false, number startAt = null)`	Returns the zero-based first match, or `-1`.
`FINDLAST(string findText, string withinText, boolean caseSensitive = false, number startAt = null)`	Returns the zero-based last match, or `-1`.
`LOWER(string value)`	Converts text to invariant lower case.
`UPPER(string value)`	Converts text to invariant upper case.
`TRIM(string value)`	Removes leading and trailing whitespace.
`TEXTVALUE(object value)`	Converts a value to text; `null` becomes an empty string.
`URLENCODE(string value)`	Encodes a URL query-parameter value.
`URIENCODE(string value)`	Escapes a valid absolute URI.
`STRING_EMPTY()`	Returns an empty string.

Text searches and marker functions are case-insensitive by default when they expose caseSensitive. CONTAINS, EQUALS, and ordinary string equality use their own case-sensitive rules described below.

Date and time functions

Function	Purpose
`DATEVALUE(string value, string dateFormat = null)`	Parses a date. An explicit format uses invariant exact parsing; otherwise ISO/invariant parsing is tried before the current culture.
`SECOND(datetime value)`	Returns the second component from `0` to `59`.
`MINUTE(datetime value)`	Returns the minute component from `0` to `59`.
`HOUR(datetime value)`	Returns the hour component from `0` to `23`.
`DAY(datetime value)`	Returns the day-of-month component.
`MONTH(datetime value)`	Returns the month component from `1` to `12`.
`YEAR(datetime value)`	Returns the year component.
`WEEKDAY(datetime value)`	Returns Sunday as `0` through Saturday as `6`.
`NOW(string timeZoneId = "")`	Returns the current UTC time, or the current time in the supplied system time-zone ID.
`FORMATDATE(datetime value, string dateFormat)`	Formats a date with an invariant .NET format string.
`DATEADD(datetime date, string unit, number number)`	Adds a signed interval; decimal counts are truncated.
`DATEDIF(datetime startDate, datetime endDate, string unit)`	Returns a signed elapsed interval; months and years count complete calendar intervals.

DATEADD and DATEDIF accept ms or millisecond, s or second, m or minutes, h or hours, d or days, M or months, and y or years. Units are case-sensitive because m means minutes and M means months:

DATEADD(DATEVALUE("2026-08-27"), "d", 2)

Time-zone IDs accepted by NOW depend on the operating system running the Workflow. Prefer UTC unless the target deployment's ID is known.

Logical and conditional functions

Function	Purpose
`NOT(boolean value)`	Negates a boolean.
`ISTEXT(object value)`	Tests whether a runtime or JSON scalar is text. Numeric text remains text.
`ISNUMBER(object value)`	Tests whether a runtime or JSON scalar is numeric. Numeric text returns `false`.
`CONTAINS(object list, object value)`	Tests membership in a list using case-sensitive ordinal text comparison and numeric value equality.
`CONTAINS_TEXT(string text, string value, boolean caseSensitive = false)`	Tests whether text contains a substring.
`IIF(boolean condition, object trueValue, object falseValue)`	Returns one of two type-compatible values.
`IFS(object... conditionValuePairs)`	Returns the result for the first true condition/result pair.
`SWITCH(object expression, object... caseResultPairs)`	Returns the first matching case result, or an optional final default.
`AND(boolean... conditions)`	Returns `true` when every condition is true.
`OR(boolean... conditions)`	Returns `true` when any condition is true.
`EQUALS(object a, object b)`	Compares strings ordinally, numbers by value, and unwraps JSON scalars.
`ISEMPTY(object value)`	Tests for `null`, empty text, an empty JSON value, or an empty collection. Whitespace-only text is not empty.
`ISNULLORWHITESPACE(object value)`	Tests for `null`, empty text, or whitespace-only text.

IFS requires complete condition/result pairs and fails when no condition is true. SWITCH requires at least one case/result pair; an unmatched final argument is the default. It fails when no case matches and no default is supplied.

The && and || operators short-circuit. AND and OR are ordinary function calls, so use the operators when later operands must not be evaluated after the result is known.

Numeric and aggregate functions

Function	Purpose
`NUMBERVALUE(string value)`	Parses an invariant-culture number; invalid or empty input returns `0`.
`SUM(array items)`	Sums numeric items in a source collection and ignores non-numeric items.
`SUM_OF(number... list)`	Sums the supplied numeric arguments.
`STDEV(array items)`	Returns the population standard deviation of numeric source items; fewer than two values return `0`.
`STDEV_OF(number... list)`	Returns the population standard deviation of supplied arguments.
`RAND()`	Returns a random number greater than or equal to `0` and less than `1`.
`GUID()`	Returns a new canonical GUID as text.
`ROUND(number value, number numDigits)`	Rounds using the runtime's default midpoint-to-even behavior.
`MIN(array items)`	Returns the lowest numeric source value, or `0` when none exists.
`MIN_OF(number... numbers)`	Returns the lowest supplied number, or `0` when empty.
`MAX(array items)`	Returns the highest numeric source value, or `0` when none exists.
`MAX_OF(number... numbers)`	Returns the highest supplied number, or `0` when empty.
`MIN_TEXT(array items)`	Returns the ordinally lowest source text, or an empty string.
`MIN_TEXT_OF(string... items)`	Returns the ordinally lowest supplied text, or an empty string.
`MAX_TEXT(array items)`	Returns the ordinally highest source text, or an empty string.
`MAX_TEXT_OF(string... items)`	Returns the ordinally highest supplied text, or an empty string.
`AVERAGE(array items)`	Returns the mean of numeric source items, or `0` when none exists.
`AVERAGE_OF(number... numbers)`	Returns the mean of supplied numbers, or `0` when empty.
`COUNT(array items)`	Counts non-null items in a source collection.
`FORMATNUMBER(number value, string numberFormat)`	Formats a number with an invariant standard or custom .NET format string.

Functions whose names end in _OF consume their remaining scalar arguments. Their matching source functions consume a collection reference or compatible collection function:

SUM({listOrders.Orders.total})
SUM_OF({getOrder.Order.subtotal}, {getOrder.Order.tax})
COUNT(FILTER({listOrders.Orders}, {listOrders.Orders.status} == "Open"))

Collection consumption can affect lazy execution and replay. See Lazy Evaluation and Streaming.

Lookup and selection functions

Function	Purpose
`MAKEARRAY(object value)`	Wraps one scalar or object value in a one-item array.
`LOOKUP(string value, array lookIn, string keyProperty, string valueProperty, string defaultValue = null)`	Finds a row by an ordinal text key and returns the selected field as text.
`FIRST(array items)`	Returns the first source item, or `null`.
`LAST(array items)`	Returns the last source item, or `null`.
`INDEX(array items, number position)`	Returns the item at a zero-based position, or `null` when out of range.

LOOKUP takes dotted property paths as strings. An index such as [0] can appear in a path segment. When no row matches, it returns defaultValue if one was supplied; otherwise it fails.

LOOKUP({.countryCode}, {getCountries.Countries}, "code", "name", "Unknown")

Collection and container functions

These functions can be the top-level Expression on an object or array mapping that also declares child Properties:

Function	Purpose and required shape
`MAKEARRAY(object value)`	Promotes one value to a one-item array.
`FIRST(array items)`	Selects the first object for child mapping.
`LAST(array items)`	Selects the last object for child mapping.
`PLUCK(array source)`	Selects a referenced field from a collection and recursively flattens array levels in source order. The authored argument is the field reference to project.
`FILTER(array source, boolean predicate)`	Keeps rows whose predicate is true. The source must be a direct Step Return reference and exactly one predicate is required. Predicate references must remain in that source scope.
`GROUPBY(array source, object... keys)`	Groups rows by one or more direct child fields of the source. The source must be a direct Step Return reference. Each key must append exactly one unique field segment to the source path.

Examples:

PLUCK({listOrders.Orders.lines.sku})
FILTER({listOrders.Orders}, {listOrders.Orders.status} == "Open")
GROUPBY({listOrders.Orders}, {listOrders.Orders.country}, {listOrders.Orders.region})

GROUPBY emits each grouping key as a scalar column and non-key fields as arrays for that group. Nested mappings can then project or aggregate those grouped values.

A pure collection reference is also valid as the parent Expression. A different top-level function on a container with child mappings is rejected even if that function can return an object or array.

Validate a function call

Use the designer's autocomplete and function hover information to confirm the current signature. Check Problems for an unknown function, wrong argument count, incompatible argument type, invalid collection source, or invalid container function.

After compilation succeeds, use Preview to inspect design-time results. Use Debug when the expression depends on representative data or an external call.