...
| function name | meaning | parameter1 | parameter2 | parameter3 | parameter4 |
|---|---|---|---|---|---|
| IIF | Conditional If, if parameter1 is true then returns parameter2 otherwise returns parameter3 | condition | expression to return if condition is true | expression to return if condition is not true | |
| Coalesce | returns the first non-null parameter | first expression to check if not-null | second expression to check if not-null | (optional) 3rd expression to check | (optional) 4th expression to check (you can have an unlimited number of parameters) |
| NullIf | returns NULL if parameter1 equals parameter 2, otherwise it returns parameter1 | value to return if not equal to parameter2 | value to test parameter1 against | ||
| NullIfError | returns NULL if the evaluation of parameter1 throws an exception, otherwise it returns parameter 1 | expression to evaluate | |||
| Date | returns a new date value from the supplied Gregorian calendar components | year | month | day | |
| Now | returns the date and time right at the time the formula evaluation began (this value does not change when called multiple times with a given formula evaluation context, such that if used on an export all rows would have the same value) | ||||
| AddDays | returns the result of adding a number of days to a date | starting date | number of days to add | ||
| AddMonths | returns the result of adding a number of months to a date | starting date | number of months to add | ||
| AddYears | returns the result of adding a number of years to a date | starting date | number of years to add | ||
| Day | returns the day of the month of a given date | date | |||
| Month | returns the calendar month of a given date | date | |||
| Year | returns the calendar year of a given date | date | |||
| DayOfWeek | returns the day of the week (sunday = 0, monday = 1, ... saturday = 6 ) of a given date | date | |||
| ParseDate | converts a string to a date | string value to parse | (optional) the expected format of the string. For example MM/dd/yyyy | ||
| Len | returns the length of a string; or, the number of elements in a list/array | string value or array value | |||
| Left | returns a portion of a string starting from the left | string value | maximum number of characters to return | ||
| Right | returns a portion of a string starting from the right | string value | maximum number of characters to return | ||
| Substring | returns a portion of a string starting at a given position (this function will error if you specify a start and/or length beyond the range of the string) | string value | the starting index (beginning with 0) | (optional) the number of characters to return | |
| Replace | returns a the result of a string search-and-replace operation (case-sensitive) | string value to search | substring to search for within parameter1 | string value with which to replace any occurrences of parameter2 | |
| IndexOf | returns the first occurrence of a search within a string (-1 means not found, >= 0 means the index within the string that the match was found) | string value to search | substring to search for | (optional) the starting position (beginning with 0) to begin searching. | (optional)true for case-sensitive, false for case-insensitive |
| StartsWith | returns true if a string starts with a given substring, false otherwise | string value to check | substring to search for | (optional) true for case-sensitive, false for case-insensitive | |
| EndsWith | returns true if a string ends with a given substring, false otherwise | string value to check | substring to search for | (optional) true for case-sensitive, false for case-insensitive | |
| StringCompare | returns -1 if the parameter1 is alphabetically less than parameter2, 0 if they are equal, and 1 if parameter2 is greater than parameter1 | first string to compare | second string to compare | (optional) true for case-sensitive, false for case-insensitive | |
| ToUpper | returns the result of converting any lower case letters in parameter1 to upper-case letters | string value | |||
| ToLower | returns the result of converting any upper case letters in parameter1 to lower-case letters | string value | |||
| Trim | returns the result of removing any leading or trailing white-space characters from parameter1 | string value | |||
| PadLeft | returns the specified string padded to the left to the specified length if the string is less than that length | string value | minimum length to pad to | (optional)character to use for padding | |
| PadRight | returns the specified string padded to the right to the specified length if the string is less than that length | string value | minimum length to pad to | (optional)character to use for padding | |
| MatchRegex | returns whether or not the specified string matches the supplied regular expression | string test value | regular expression (ECMA script) | (optional) true for case-insensitive, false for case-sensitive | |
| ReplaceRegex | returns the result of a regular expression replacement | string value | regular expression match string (ECMA script) | regular expression replacement string | (optional) true for case-insensitive, false for case-sensitive |
| ParseFirstName | returns the first name component of the supplied full name | full name to parse | |||
| ParseLastName | returns the last name component of the supplied full name | full name to parse | |||
| ParseMiddleName | returns the middle name component of the supplied full name | full name to parse | |||
| ParseSuffix | returns the suffix component of the supplied full name | full name to parse | |||
| Format | returns the result of formatting zero or more supplied values into a string using a standard .net formatting string. Parameter1 is returns with { n } replaced by value-n, and { n : format } replaced by value-n formatted by the specified format. For example Format("{0} + {1:0.00} = {2:0.0}", 1, 2, 3) would return "1 + 2.00 = 3.0" | .net style format string | (optional) value 0 to apply to the format string | (optional) value 1 to apply to the format string | (optional) value 2 to apply to the format string. You can supply an unlimited number of value |
| Round | returns a numeric value rounded to a given number of decimal places. Midpoints are rounded away from 0 (e.g. 2.5 rounds to 3) | numeric value to round | (optional) number of decimal places to round to (0 is the default) | ||
| Truncate | returns the integer portion of a number, discarding any fractional amounts (can be thought of as rounding towards 0) | numeric value | |||
| Ceiling | returns a numeric value rounded up to the first integer greater than or equal to it (can be thought of as rounding towards positive infinite) | numeric value | |||
| Floor | returns a numeric value rounded down to the first integer less than or equal to it (can be thought of as rounding towards negative infinite) | numeric value | |||
| Frac | returns the fractional portion of a number | numeric value | |||
| Abs | returns the absolute value of a number | numeric value | |||
| GetProperty | gets the specified property of an object | object value | string property name | ||
| GetPropertyNames | gets an array of all of the properties in an object | object value | |||
| HasProperty | gets whether or not the specified property exists in an object | object value | string property name | ||
| GetListItem | gets the specified array/list item | array value | |||
| CurrentRecord | returns the current field values wrapped up as an object |
...