Skip to main content

Workflow Functions Reference

Note

For a list of supported functions for Standard mode tools, go to the Standard mode Formula Tool documentation.

These function types are available for transforming your data in Designer Cloud. The type of data determines the functions you can use.

Visit Supported Data Types for more information.

Use this guide to quickly reference functions that you can use in the Expression Editor in Designer Cloud. If you need more information and examples, visit the category-specific pages linked in each section.

Conditional

Conditional functions let you perform an action or calculation using an IF statement. For parameters and examples, visit the Workflow Conditional Functions article.

IF condition THEN t ELSE f ENDIF

IF c THEN t ELSE f ENDIF: Returns t if the condition c is true, else returns f.

IIF

IIF(bool, x, y): Returns x if bool is true, else returns y.

Switch

Switch(Value,Default,Case1,Result1,...,CaseN,ResultN): Compares a value against a list of cases and returns the corresponding result.

IF c THEN t1 ELSEIF c2 THEN t2 ELSE f ENDIF

IF c THEN t1 ELSEIF c2 THEN t2 ELSE f ENDIFIF c THEN t ELSE f ENDIF: Returns t1 if c is true, else returns t2 if condition c2 is true, else returns f.

Conversion

Conversion functions convert numbers to strings or strings to numbers. For parameters and examples, visit the Workflow Conversion Functions article.

ToNumber

ToNumber(x, [bIgnoreErrors], [keepNulls], [decimalSeparator]): Converts a string (x), to a number.

ToString

ToString(x, numDec, [addThousandsSeparator], [decimalSeparator]): Converts a numeric parameter (x) to a string using numDec decimal places. Default selection uses a period as the decimal separator.

DateTime

DateTime functions let you perform an action or calculation on a date and time value. For parameters and examples, visit the DateTime Functions article.

DateTimeAdd

DateTimeAdd(dt,i,u): Adds a specific interval to a date-time value.

DateTimeDay

DateTimeDay(dt): Returns the numeric value for the day of the month in a date-time value.

DateTimeDiff

DateTimeDiff(dt1,dt2,u): Subtracts the second argument from the first and returns it as an integer difference. The duration is returned as a number, not a string, in the specified time units.

DateTimeFirstOfMonth

DateTimeFirstOfMonth(): Returns the first day of the month, at midnight.

DateTimeHour

DateTimeHour(dt): Returns the hour portion of the time in a date-time value.

DateTimeLastOfMonth

DateTimeLastOfMonth(): Returns the last day of the current month, with the clock set to one second before the end of the day (23:59:59).

DateTimeMinutes

DateTimeMinutes(dt): Returns the minutes portion of the time in a date-time value.

DateTimeMonth

DateTimeMonth(dt): Returns the numeric value for the month in a date-time value.

DateTimeNow

DateTimeNow(): Returns the current system date and time.

DateTimeSeconds

DateTimeSeconds(dt): Returns the seconds portion of the time in a date-time value.

DateTimeToday

DateTimeToday(): Returns today’s date.

DateTimeYear

DateTimeYear(dt): Returns the numeric value for the year in a date-time value.

ToDate

ToDate(x): Converts a string, number, or date-time to a date.

ToDateTime

DateTimeTrim(ToDateTime(x), t): Converts a string, number, or date value to a date-time.

DateTimeParse

DateTimeParse(dt, f): Converts a date string with the specified format and language to the standard ISO format (yyyy-mm-dd HH:MM:SS).

DateTimeFormat

DateTimeFormat(dt, f): Converts date-time data df to another specified format f.

DateTimeTrim

DateTimeTrim(dt, f): Removes unwanted portions of a date-time value and returns the modified date-time value.

Finance

A finance function applies financial algorithms or mathematical calculations. Learn more in the Workflow Finance Functions article.

IRR

IRR(Value1, Value2): Calculates Internal Rate of Return, which is the interest rate at which the costs of the investment lead to the benefits of the investment. This means that all gains from the investment are inherent to the time value of money and that the investment has a zero net present value at this interest rate.

XIRR

XIRR(Value1, Date1, Value2, Date2): Calculates the Internal Rate of Return of an investment with dates.

Math

A math function performs mathematical calculations. For parameters and examples, visit the Workflow Math Functions article.

Abs

Abs(x): Returns the absolute value of x. It is the distance between 0 and a number. The value is always positive.

Exp

Exp(x): Returns e raised to the x power.

Round

Round(x, mult): Returns x rounded to the nearest multiple of the number specified in mult.

Rand

Rand(): Returns a random number greater than or equal to 0 and less than 1.

Floor

Floor(x): Returns the largest integer less than or equal to x.

Ceil

Ceil(x): Returns the smallest integer greater than or equal to x.

Min/Max

A minimum or maximum function finds the smallest and largest value of a set of values. Learn more in the Min/Max Functions article.

Min

Min(v0, v1,..., vn): Returns the minimum value from the list.

Max

Max(v0, v1,..., vn): Returns the maximum value from the list.

Operators

An operator is a character that represents an action. Use an arithmetic operator to perform mathematical calculations or a Boolean operator to work with true/false values. Learn more in the Workflow Operators article.

Addition

+: Addition.

Subtraction

-: Subtraction.

Division

/: Division.

Multiplication

*: Multiplication.

Boolean AND

&&: Combines two boolean values. The result is True if both of the combined values are True, False if either of them is False but not NULL, and NULL if either of them is NULL.

Boolean AND - Keyword

AND: Combines two boolean values. The result is True if both of the combined values are True, False if either of them is False but not NULL, and NULL if either of them is NULL.

Boolean NOT

!: Accepts one input. If that input is True, it returns False. If that input is False, it returns True. If that input is NULL, it returns NULL.

Boolean NOT - Keyword

NOT: Accepts one input. If that input is True, it returns False. If that input is False, it returns True. If that input is NULL, it returns NULL.

Boolean OR

||: Combines two boolean values. The result is True if either (or both) of the two values is true. Returns NULL if any of the values is NULL.

Boolean OR - Keyword

OR: Combines two boolean values. The result is True if either (or both) of the two values is true. Returns NULL if any of the values is NULL.

Equal To

=: Equal to.

Greater Than

>: Greater than.

Greater Than or Equal

>=: Greater than or equal.

Less Than

<: Less than.

Less Than or Equal

<=: Less than or equal.

Value IN (...)

IN: Test value in list.

Value NOT IN (...)

NOT IN: Test value not in list.

Specialized

These functions perform a variety of specialized actions and can be used with all data types. For parameters and examples, visit the Workflow Specialized Functions article.

NULL

NULL(): Returns a Null value.

String

A string function performs operations on text data. Use a string function to cleanse data, convert data to a different format or case, compute metrics about the data, or perform other manipulations. For parameters and examples, visit the Workflow String Functions article.

Contains

Contains(String, Target, CaseInsensitive=1): Searches for the occurrence of a particular string within a string. Returns True if (String) contains (Target), else returns False.

CountWords

CountWords(string): Returns the count of words in the specified string. Words are defined by characters separated by a space.

EndsWith

EndsWith(String, Target, CaseInsensitive=1): Checks if a string ends with a particular string. Returns True if (String) ends with (Target), else returns False. It defaults to case insensitive.

FindString

FindString(String,Target): Searches for the occurrence of a particular string (Target) within another string (String) and returns the numeric position of its occurrence in the string. Returns the 0-based index of the first occurrence of (Target) in (String). Returns -1 if no occurrence.

GetWord

GetWord(string, n): Returns the Nth (0-based) word in the string. Words are defined as a collection of characters separated by a space. 0-based index, means the first word is at the 0 position.

Left

Left(String, len): Returns the first (len) characters of the string (String). If len is less than 0 or greater than the length of String, String remains unchanged.

Length

Length(String): Returns the length of the string (String).

LowerCase

LowerCase(String): Converts a string to lower case.

PadLeft

PadLeft (String, len, char): Pads the string to the left with the specified character to the specified length. If the padding (char) is more than one character long, only the first character is used.

PadRight

PadRight (String, len, char): Pads the string to the right with the specified character to the specified length. If the padding (char) is more than one character long, only the first character is used.

REGEX_Replace

REGEX_Replace(String, pattern, replace, icase): Returns the string resulting from the RegEx find pattern and replace string.

REGEX_Match

REGEX_Match(String,pattern,icase): Searches a string for an occurrence of a regular expression. Tells if the string matches the pattern from the first character to the end.

ReverseString

ReverseString(String): Reverses all the characters in the string.

Replace

Replace(String, Target, Replacement): Returns the string (String) after replacing each occurrence of the String (Target) with the String (Replacement).

ReplaceChar

ReplaceChar(String, y, z): Returns the string (String) after replacing each occurrence of the character (y) with the character (z). If the replacement character (z) is a string with more than one character, only the first character is used. If (z) is empty, each character (String) that matches any character in (y) is simply removed.

ReplaceFirst

ReplaceFirst(String, Target, Replacement): Returns the string (String) after replacing the first occurrence of the string (Target) with the string (Replacement).

Right

Right(String, len): Returns the last (len) characters of the string. If len is less than 0 or greater than the length of String, the string remains unchanged.

StartsWith

StartsWith(String, Target, CaseInsensitive=1): Checks if a string starts with a particular string. Returns True if String starts with a particular string Target, else returns False.

Substring

Substring(String, start, length): Returns the substring of (String) starting at (start), stopping after (length), if provided.

TitleCase

TitleCase(String): Converts a string to title case.

Trim

Trim(String, y): Removes the character(s) in the string (y) from the ends of the string (String). Y is optional and defaults to trimming white space. Notice in the TRIM function examples the specified characters are trimmed. It doesn't matter what order the characters are in.

TrimLeft

TrimLeft(String, y): Removes character in the string (y) from the beginning of the string (String). Y is optional and defaults to trimming white space.

TrimRight

TrimRight(String, y): Removes character in the string (y) from the end of the string (String). Y is optional and defaults to trimming white space.

Uppercase

Uppercase(String): Converts a string to upper case.

Test

A test function performs data comparisons. Use a test function to identify the data type of a value, or determine if a value exists. For parameters and examples, visit the Workflow Test Functions article.

IsEmpty

IsEmpty(v): Tests if v is NULL or equal to "".

IsInteger

IsInteger(v): Tests if (v) contains a value that can be converted to an integer. If so, it returns True.

IsNull

IsNull(v): Tests if (v) is NULL. Returns True if v is NULL, otherwise returns False.

IsNumber

IsNumber(v): Tests if the field type for (v) is a number or not.

IsString

IsString(v): Tests if the field type for (v) is a string or not.