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.