Workflow Functions Reference
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.
Note
Not all functions are supported between Standard mode and Cloud Native mode tools. For a list of supported functions, go to the respective Standard mode and Could Native mode function lists found on the Formula tool page.
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.
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.
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.
Conversion
Conversion functions convert numbers to strings or strings to numbers. For parameters and examples, visit the Workflow Conversion Functions article.
BinToInt
BinToInt(s): Converts the binary string s to an integer (Limited to 53 bits).
CharFromInt
CharFromInt(x): Returns the Unicode® character that matches the input number x.
CharToInt
CharToInt(s): Returns the number that matches the input Unicode® character s.
ConvertFromCodePage
ConvertFromCodePage(s, codePage): Translates text from a code page to Unicode. To learn more about code pages, go to the Code Pages documentation.
ConvertToCodePage
ConvertToCodePage(s, codePage): Translates text from Unicode encoding to a specific code page. To learn more about code pages, visit the Code Pages documentation.
HexToNumber
HexToNumber(x): Converts a HEX string to a number (Limited to 53 bits).
IntToBin
IntToBin(x): Converts x to a binary string.
IntToHex
IntToHex(x): Converts x to a hexadecimal string.
ToDegrees
ToDegrees(x): Converts a numeric radian value (x) to degrees via the (x)rad × 180/π calculation. Please note that x must be a numeric value and can’t include any radian symbol (rad).
ToNumber
ToNumber(x, [bIgnoreErrors], [keepNulls], [decimalSeparator]): Converts a string (x), to a number.
ToRadians
ToRadians(x): Converts a numeric degree value (x) to radians via the (x)° × π/180 calculation. Please note that x must be a numeric value and can’t include the degree symbol (°).
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.
UnicodeNormalize
UnicodeNormalize(String, Form): Converts text data in the provided string into a standardized Unicode form. Use the required Form parameter (case-insensitive) to define the normalization form. You must manually enter one of the below normalization form options (this parameter can't be populated via an upstream field.
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.
DateTimeFormat
DateTimeFormat(dt, f): Converts date-time data df to another specified format f.
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.
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).
DateTimeQuarter
DateTimeQuarter(dt,[Q1Start]): Returns the numeric value for the quarter of the year in which a date-time (YYYY-MM-DD) value falls. Use the optional numeric parameter to indicate the start month for the first quarter (Q1).
DateTimeSeconds
DateTimeSeconds(dt): Returns the seconds portion of the time in a date-time value.
DateTimeToday
DateTimeToday(): Returns today’s date.
DateTimeTrim
DateTimeTrim(dt, f): Removes unwanted portions of a date-time value and returns the modified date-time value.
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.
Finance
A finance function applies financial algorithms or mathematical calculations. Learn more in the Workflow Finance Functions article.
FinanceCAGR
FinanceCAGR(BeginningValue, EndingValue, NumYears): Calculates Compound Annual Growth Rate: The geometric mean growth rate on an annualized basis.
FinanceEffectiveRate
FinanceEffectiveRate(NominalRate, PaymentsPerYear): Calculates Effective Annual Interest Rate: The interest rate on a loan or financial product restated from the nominal interest rate as an interest rate with an annual compound interest payable in arrears.
FinanceFV
FinanceFV(Rate, NumPayments, PaymentAmount, PresentValue, PayAtPeriodBegin): Calculates Future Value of an Investment: The value of an asset at a specified time in the future assuming a certain interest rate or rate of return.
FinanceFVSchedule
FinanceFVSchedule(Principle, Year1Rate, Year2Rate): Calculates Future Value Schedule: The future value of an initial principal after applying a series of interest rates to an investment.
FinanceIRR
FinanceIRR(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.
FinanceMIRR
FinanceMIRR(FinanceRate, ReinvestRate, Value1, Value2): Calculates Modified Internal Rate of Return: A modification of the internal rate of return that aims to resolve some problems with the IRR. The MIRR is a financial measure of an investment's attractiveness.
FinanceMXIRR
FinanceMXIRR(FinanceRate, ReinvestRate, Value1, Date1, Value2, Date2): Calculates Modified Internal Rate of Return of an investment with dates.
FinanceNominalRate
FinanceNominalRate(EffectiveRate, PaymentsPerYear): Calculates Nominal Annual Interest Rate: An interest rate is called nominal if the frequency of compounding (such as a month) is not identical to the basic time unit (normally a year).
FinanceNPER
FinanceNPER(Rate, PaymentAmount, PresentValue, FutureValue, PayAtPeriodBegin): Calculates the Number of periods for an investment or loan.
FinanceNPV
FinanceNPV(Rate, Value1, Value2): Calculates Net Present Value of an investment: Measures the excess or shortfall of cash flows, in present value terms, once financing charges are met.
FInancePMT
FinancePMT(Rate, NumPayments, PresentValue, FutureValue, PayAtPeriodBegin): Calculates payments on a loan.
FinancePV
FinancePV(Rate, NumPayments, PaymentAmount, FutureValue, PayAtPeriodBegin): Calculates Present Value of an investment: The value on a given date of a future payment, or series of future payments, discounted to reflect the time value of money and other factors such as investment risk.
FinanceRate
FinanceRate(NumPayments, PaymentAmount, PresentValue, FutureValue, PayAtPeriodBegin): Calculates the interest rate (per period).
FinanceXIRR
FinanceXIRR(Value1, Date1, Value2, Date2): Calculates the Internal Rate of Return of an investment with dates.
FinanceXNPV
FinanceXNPV(Rate, Value1, Date1, Value2, Date2): Calculates the Net Present Value 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.
ACOS
ACOS(x): Returns the arccosine, or inverse cosine, of a number (x). The arccosine is the angle whose cosine is (x).
ASIN
ASIN(x): Returns the arcsine, or inverse sine, of a number (x). The arcsine is the angle whose sine is (x).
ATAN
ATAN(x): Returns the arctangent, or inverse tangent, of a number (x). The arctangent is the angle whose tangent is (x). The returned angle is given in radians in the range -pi/2 to pi/2.
ATAN2
ATAN2(y, x): Returns the arctangent, or inverse tangent, of y and x (Arc - Tangent of (y/x)).
Average
Average(n1, ...): Returns the average of a list of numbers. NULL values are treated as 0 and taken into consideration in the calculation of the average value.
AverageNonNull
AverageNonNull(n1, ...): Returns the average of a list of numbers. Null values are excluded from the calculation. The calculation only works with Numeric data types.
CEIL
.CEIL(x, [mult]): Returns a numeric value (x) raised to the lowest multiple of (mult) that is greater than or equal to x. The (mult) parameter must be a number greater than 0. If (mult) is not specified, the function returns the smallest integer greater than or equal to (x).
COS
COS(x): Returns the cosine of a given angle (x). x must be in radians.
COSH
COSH(x): Returns the hyperbolic cosine of a number (x).
EXP
EXP(x): Returns e raised to the x power.
Factorial
Factorial(x): Returns the factorial of a numeric value (x) via the (n! = n x (n–1) x … x 1) calculation. For example, the factorial of 4 is calculated as such: 4! = 4 x 3 x 2 x 1 = 24.
FLOOR
FLOOR(x, [mult]): Returns a numeric value (x) lowered to the highest multiple of (mult) that is less than or equal to x. The (mult) parameter must be a number greater than 0. If (mult) is not specified, the function returns the largest integer less than or equal to (x), as shown in the first example below.
LOG
LOG(x): Returns the natural logarithm of a number (x). x should be a positive number.
LOG10
LOG10(x): Returns the base-10 logarithm of a number (x). x should be a positive number.
Median
Median(...): Calculates the median of 1 or more (possibly unsorted) values.
Mod
Mod(n,d): Modulo of a number (n) divided by a number (d) (integer operation).
PI
PI(): Returns the value of the constant PI to 15 digits of accuracy.
POW
POW(x, e): Returns a number (x) raised to the (e) power.
RAND
RAND(): Returns a random number greater than or equal to 0 and less than 1.
RandInt
RandInt(n): Returns a random integer between 0 and the specified parameter (n).
Round
Round(x, mult): Returns x rounded to the nearest multiple of the number specified in mult.
SIN
SIN(x): Returns the sine of a given angle (x). x must be in radians.
SINH
SINH(x): Returns the hyperbolic sine of a number (x).
SmartRound
SmartRound(x): Returns a number (x) rounded to the nearest multiple of a value determined dynamically based on the size of (x).
SQRT
SQRT(x): Returns the square root of a number (x).
TAN
TAN(x): Returns the tangent of a given angle (x). x must be in radians.
TANH
TANH(x): Returns the hyperbolic tangent of a number (x).
Math: Integer
An integer function operates on 1 or more bit patterns or binary numerals at the level of their individual bits.
BinaryAnd
BinaryAnd(n,m): Returns a binary of (n) and (m). The result is 1 if both n and m are 1, and 0 otherwise. If 0 is equated with False, and 1 with True, the BinaryAnd operation works like a logical And. Note the absence of the decimal point on the result values. The result is an integer, not a float value.
BinaryNot
BinaryNot(n): Returns a Binary Not of (n). Numbers are treated as 64-bit, two's complement numbers.
BinaryOr
BinaryOr(n,m): Returns a Binary Or of (n) and (m).
BinaryXOr
BinaryXOr(n,m): Returns a Binary XOr of (n) and (m).
ShiftLeft
ShiftLeft(n,b): Left shifts (n) (as integer) by (b) bits.
ShiftRight
ShiftRight(n,b): Right shifts (n) (as integer) by (b) bits.
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.
Between
BETWEEN(x, min, max): Tests if a value (x) is between 2 other values (min and max), inclusive of min and max. Returns TRUE if x is between min and max. Returns FALSE if x is not between min and max.
Bound
Bound(x, min, max): If (x < min) returns min, else if (x > max) return max, else returns (x).
Max
Max(v0, v1,..., vn): Returns the maximum value from the list.
MaxIDX
MaxIDX(v0, v1,..., vn): Returns the 0-based index of the maximum value from the list.
Min
Min(v0, v1,..., vn): Returns the minimum value from the list.
MinIDX
MinIDX(v0, v1,..., vn): Returns the 0-based index of the minimum 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.
Boolean AND - Keyword
AND: Combines 2 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
&&: Combines 2 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 - Keyword
NOT: Accepts 1 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
!: Accepts 1 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 - Keyword
OR: Combines 2 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
||: Combines 2 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.
Division
/: Division.
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.
Multiplication
*: Multiplication.
Not Equal To
!=: Not Equal To
Subtraction
-: Subtraction.
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.
Coalesce
Coalesce(v1,v2,v3,…,vn): Returns the first non-null value.
EscapeXMLMetacharacters
EscapeXMLMetacharacters(String): Replaces XML metacharacters with their escaped versions.
GetVal
GetVal(index, v0,...vn): Returns the value (v0, ..., vn) specified by the 0-based index.
Message
Message(messageType, message, returnValue): Use with a conditional statement to output a message to the message log and update column data with a specified value when a condition is met.
NULL
NULL(): Returns a Null value.
RangeMedian
RangeMedian(...): Calculates the median from a series of aggregated ranges. Go to Range Median for more information.
Soundex
Soundex(String): Returns the Soundex of String.
Soundex_Digits
Soundex_Digits(String): Returns the first 4 digits or the Soundex if none.
TOPNIDX
TOPNIDX(N, v0, v1, ..., vn): Returns the 0-based original index position of the Nth from the maximum value upon sorting the indexed fields (v0 to vn) in descending order. Null values are ignored and N should be less than non-null parameters. With N==0, it is the same as MaxIdx(...).
UrlEncode
UrlEncode(String): Encodes a UTF-16 string using a non-standard, UTF-16-based percent-encoding.
UrLEncodeUTF8
UrlEncodeUTF8(String): Encodes a string using the RFC 3986-compliant* percent-encoding. The function converts characters into UTF8-based units and it skips characters defined in the unreserved character set. Unlike the above UrlEncode function, this function doesn't skip the common URL delimiters like "/", so it's not suitable for encoding full path URLs. You should use this function to encode specific URL components, like query component parameters.
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.
DecomposeUnicodeForMatch
DecomposeUnicodeForMatch(String): Removes accents and expands compound characters while converting to a narrow string. All accents and other decorations are removed.
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.
FindNth
FindNth(Initial String, Target, Instance): Finds the instance (nth occurrence) of a target string in the initial string and returns the 0-indexed position of the instance. The function is case-sensitive and doesn’t accept negative parameters.
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.
GetLeft
GetLeft(String, Delimiter): Returns the left part of the provided (String) until the first instance of the specified 1 or more (Delimiter).
GetPart
GetPart(String, Delimiter, Index): Uses the specified 1 or more (Delimiters) to divide (String) into substrings, and returns the substring in the position that is specified in (Index).
GetRight
GetRight(String, Delimiter): Returns the right part of the provided (String) after the first instance of the specified 1 or more (Delimiters).
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.
MD5_ASCII
MD5_ASCII(String): Calculates the MD5 hash of the string. The string is expected to be only narrow characters. Wide characters are converted to '?' before computing the hash. Use for String types. You should avoid using this function on strings that might contain wide characters.
MD5_UNICODE
MD5_UNICODE(String): Calculates the MD5 hash of the string stored as UTF-16.
MD5_UTF8
MD5_UTF8(String): Calculates the MD5 hash of the string stored as UTF-8.
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 1 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 1 character long, only the first character is used.
REGEX_CountMatches
REGEX_CountMatches(String,pattern,icase): Returns the count of matches within the string to the pattern.
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.
REGEX_Replace
REGEX_Replace(String, pattern, replace, icase): Returns the string resulting from the RegEx find pattern and replace 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 1 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).
ReverseString
ReverseString(String): Reverses all the characters in the string.
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.
STRCSPN
STRCSPN(String, y): Returns the length of the initial segment of the string (String) consisting of charactersnotin the string (y).
StripQuotes
StripQuotes(String): Removes a matched set of quotation marks or apostrophes from the ends of the string.
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.
UuidCreate
UuidCreate(): Creates a unique identifier.
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.
CompareDictionary
CompareDictionary(a,b): Compares 2 strings ignoring case differences, and compares text that appears to consist of numbers, in numerical order. The function returns -1 if a < b, 0 if a==b, and 1 if a > b.
CompareDigits
CompareDigits(a, b, nNumDigits): Compares 2 numbers and determines if they are the same to the given number of digits (nNumDigits).
CompareEpsilon
CompareEpsilon(a, b, epsilon): Compares 2 floating-point numbers and returns True if they are within epsilon.
EqualStrings
EqualStrings(a,b): Compares string (a) to string (b) to determine if they are an exact match. The function returns True if all characters in both a and b are identical and False if 1 or more characters are not identical.
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.
IsLowerCase
IsLowerCase(String): Returns True if all (1 or more) alphabetic characters in a string are lowercase and False if 1 or more alphabetic characters are uppercase. The function ignores non-alphabetic characters. Note that this function only applies to characters with a case distinction and might not apply to characters in all languages.
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.
IsUpperCase
IsUpperCase(String): Returns True if all (1 or more) alphabetic characters in a string are uppercase and False if 1 or more alphabetic characters are lowercase. The function ignores non-alphabetic characters. Note that this function only applies to characters with a case distinction and might not apply to characters in all languages.