Formula Reference V1
Pre-19R1
See the tables below for details about available functions and operators, date formats, and number formats in the Vault CDMS formula and expression grammar.
Functions and Operators
| Syntax | Description | ||||||
| Logical Operators | |||||||
| < | Less than | ||||||
| <= | Less than or equal to | ||||||
| > | Greater than | ||||||
| >= | Greater than or equal to | ||||||
| numberEquals(number1, number2) | Returns true if both numeric arguments (can include expressions) are equal. | ||||||
| textEquals(text1, text2) | Returns true if both text (string) arguments are equal. | ||||||
| && | AND | ||||||
| || | OR | ||||||
| Arithmetic Operators | |||||||
| * | Multiplies two values. | ||||||
| / | Divides one value by another. | ||||||
| % | Remainder from one value divided by another. | ||||||
| + | Calculates the sum of two values. | ||||||
| - | Calculates the difference of two values. | ||||||
| Math Functions | |||||||
| min(number1, number2 ...) | Returns the smallest number from the set. | ||||||
| max(number1, number2, ...) | Returns the largest number from the set. | ||||||
| sqrt(number) | Returns the square root. | ||||||
| floor(number) | Returns the next integer greater than the value (number). | ||||||
| ceiling(number) | Returns the next integer less than the value (number). | ||||||
| round(number_of_digits) | Rounds the value to the defined number of digits. | ||||||
| value(text) | Returns the string (text) as a number. | ||||||
| Logical Functions | |||||||
| and(expression1, expression2, ...) | Returns true when both conditions are true. | ||||||
| or(expression1, expression2, ....) | Returns true when only one of the conditions is true. | ||||||
| not(expression) | Returns true when the condition is not true. | ||||||
| if(expression1, value1, value2) | Returns value 1 if the expression is true or value 2 if the expression is false. | ||||||
| isNumber(text) | Returns true when the value is a number. | ||||||
| isNull(expression) | Returns true when the value is null. | ||||||
| picklistEquals(<Picklist Field>, Picklist.<picklist name>.<value>) | Compares a picklist value with an existing global picklist label value and returns true or false. | ||||||
| isBlank(expression) | Returns true when the value is blank. | ||||||
| Value Functions | |||||||
| case(expression1, match1, result1, match2, result2, else_result) | Compares the value of the expression with each matching value (match) and returns the paired result. If no values match, this function returns the last (else) argument. | ||||||
| ifBlank(expression1, expression2) | If the first argument (expression1) is blank, this function returns the second argument (expression2). If not blank, this function returns the first argument (expression1). | ||||||
| ifNull(expression1, expression2) | If the first argument (expression1) is null, this function returns the second argument (expression2). If not null, this function returns the first argument (expression1). | ||||||
| Text Functions | |||||||
| upper(value) | Changes the text (value) to UPPERCASE. | ||||||
| lower(value) | Changes the text (value) to lowercase. | ||||||
| left(value, pos) | Returns the number of text characters specified (value) starting from a specified position (pos) on the left. | ||||||
| right(value, pos) | Returns the number of text characters specified (value) starting from a specified position (pos) on the right. | ||||||
| middle(value, pos1, pos2) | Returns the number of text characters between two specified positions (pos1 and pos2). | ||||||
| length(value) | Returns the character length. | ||||||
| substitute(value, from, to) | Substitutes a defined value. | ||||||
| trim(value) | Removes leading and trailing spaces. | ||||||
| text(Date, format_mask) | Applies the format (format_mask) to the specified Date. | ||||||
| text(Number, format_mask) | Applies the format (format_mask) to the specified number (can include an expression). | ||||||
| Concatenate(text1, text2, ...) | Use this function to connect text strings. | ||||||
| Date & DateTime Functions | |||||||
| date(year, month, day) | Returns the date in the set date format. | ||||||
| day(date) | Returns the day from the date. | ||||||
| month(date) | Returns the month from the date. | ||||||
| year(date) | Returns the year from the date. | ||||||
| today() | Returns today's date in the vault’s time zone. | ||||||
| now() | Returns the current date and time (UTC). | ||||||
| hour() | Returns the current hour value in terms of 1 to 23 (UTC). | ||||||
| minute() | Returns the current minute value in the current hour in terms of 1 to 59 (UTC). | ||||||
| second() | Returns the current second value in the current minute in terms of 1 to 59 (UTC). | ||||||
| days(number) | Returns the specified number of days as an interval. | ||||||
| months(number) | Returns the specified number of months as an interval. | ||||||
| years(number) | Returns the specified number of years as an interval. | ||||||
| dateDiff(end, start) | Returns the difference between two dates in number of days. This function subtracts the start date (start) from the end date (end). (EDC) Note that dateDiff cannot process Unknown dates. If your study design allows Unknown values for a date-type Item, you must use substitute to replace the Unknown date with a date value. |
||||||
| timeDiff(end, start)) | Returns the difference between two times in number of minutes. This function subtracts the start time (start) from the end time (end). | ||||||
| dateAdd(date, interval) | Returns a date based on the offset (interval) from the starting date (date). You can use days, months, or years for the interval using the following syntax: days(#), months(#), or years(#). For example, you may use a formula field on an Audit object to calculate the Audit End Date. The end date is two weeks after the Audit Start Date. dateAdd(Object.audit_start_date v, days(14)) |
||||||
| dateTimeAdd (datetime, interval) | Returns a DateTime value based on the offset (interval) from the starting DateTime (datetime). You can use days, months, or years for the interval using the following syntax: days(#), months(#), or years(#). |
||||||
| dateTimeDiff (end, start) | Returns the difference between two DateTime values as a number of days, hours, and minutes. This function subtracts the start DateTime (start) from the end DateTime (end). | ||||||
| dateInISO(date) | Returns the Date (date) in ISO format. | ||||||
| dateInISO(datetime) | Returns the DateTime (datetime) as a date in ISO format. | ||||||
| Vault Functions | |||||||
| durationInValue | Returns the total duration that a field had the specified value; does not support Date or Date/Time field types. If the field currently has the value, Vault uses the current date as the end date. If the field has never had the value, the function returns null/blank. Note: This function returns fractional days. | ||||||
| previousValue | Returns the value that the field had prior to the current value; does not support Date or Date/Time field types. | ||||||
| firstTimeInValue | Returns the date and time when a field was first set to the specified value; does not support Date or Date/Time field types. If the field has never had the value, the function returns null/blank. | ||||||
| lastTimeInValue | Returns the date and time when a field was last set to the specified value; doesn’t support Date or Date/Time field types. Note that even if a field has had a specific value several times, this returns the most recent time. If the field currently has the value, the function returns the current date and time. If the field has never had the value, the function returns null/blank. | ||||||
| numTimesInValue | Returns the number of times the field was set to the specified value; does not support Date or Date/Time field types. | ||||||
Date Formats
| Date Format | Example Date Output | ||||
| d | 1 (1-digit day of the month) | ||||
| dd | 01 (2-digit day of the month) | ||||
| ddd | Thu (3-letter day of the week) | ||||
| dddd | Thursday (Full day of the week) | ||||
| mm | 03 (2-digit month) | ||||
| mmm | Mar (3-letter month) | ||||
| mmmm | March (Full month) | ||||
| yy | 17 (2-digit year) | ||||
| yyyy | 2017 (Full year) | ||||
| dd-mm-yyyy | 31-03-2017 | ||||
| yyyymmdd | 20170331 | ||||
| dd.mmm.yyyy | 30.03.2017 | ||||
| yyyy-mm-dd | 2017-03-30 | ||||
| mmmm yyyy | March 2017 | ||||
| dddd dd/mm/yy | Thursday 31/03/17 | ||||
Number Formats
| Number Format | Example Number Input | Example Number Output | |||||||||
| 0 | 10.1 | 10 (Number as is, no decimal) | |||||||||
| # | 10.10 | 10 (Number as is, no decimal, zero absent) | |||||||||
| 0.00 | 10.2531 | 10.25 (Number rounded to two decimal places) | |||||||||
| #.## | 10.2501 | 10.25 (Number rounded to two decimal places, zero absent) | |||||||||
| $# | 100 | $100 (Number in currency form) | |||||||||
| #,### | 1104 | 1,104 (Number with a comma 3 digits from the left. Multiple commas can be used) | |||||||||
| - | 10 | -10 (Adds the minus sign to the expression) | |||||||||
| % | 9 | %90 (Multiplies the number by 10 and displays it as a percentage) | |||||||||
| E | 12345 | 1.234E4 (Separates the mantissa, which is the number of significant digits, and the exponent in scientific notation) | |||||||||