Vault CDMS Formula Reference
You can create formulas to accomplish a variety of tasks in Vault. Vault uses an Excel™-like formula language, including functions and operators that allow you to calculate dates and text strings, as well as numbers.
The content below refers to the updated formula grammar, released in April 2019 with 19R1. For details about the pre-19R1 formulas, see the Formula Reference Guide (V1). See Migration (V1 to V2) below for details about enabling the updated formula grammar in your study.
Concepts
Review these concepts to familiarize yourself with how formulas work in Vault.
- What is a formula?
- Where are formulas used?
- Elements of a Formula
- Operators & Functions
- Variables & References
What is a Formula
A formula is a set of instructions for creating a desired result. For example, chemical formulas (two H and one O make H2O). This is not an equation, in the sense that hydrogen and oxygen are not “equal” to water, yet you can use them to make water. In some cases, a formula is a special type of equation that shows the relationship between different variables. In this case, a formula is meant to be evaluated (not solved) by substituting values for the variables.
After evaluating a formula, Vault returns a value or True/False.
Where are Formulas Used
Formulas are used in several places within Vault CDMS:
- Data validation rules
- Derived items
- Unit conversion
- Derived columns in views
- Formula fields
- Field defaults
Elements of a Formula
A formula is made up of one or more expressions and arguments. Each expression contains values, functions, operators, references, and variables to calculate a result. An argument is when an operator or function acts upon the result of an expression.
Term | Definition |
---|---|
Expression | An expression is a single piece of an equation or a formula, made up of values, variables, operators, and/or functions. A formula may contain a single expression or multiple expressions. |
Function | Functions are actors within your formula. They can perform tasks against expressions and values within your formula to output a result. |
Operator | Operators are actors within your formula. They can perform tasks against expressions and values within your formula to output a result. |
Argument | An argument is when an expression is used in a function. The function acts upon the result of the expression. |
Variable | A variable is a placeholder value within a formula that Vault replaces with an actual value during evaluation. For example, if you reference a data collection Item value in a rule formula, the data collection Item value is the variable. When Vault evaluates the rule on that Item, Vault replaces the Item variable with collected data. |
Reference | A reference is a placeholder value within a formula that points to an object field related to the object that Vault evaluates the formula from. |
Evaluate | When Vault evaluates a formula, it means that Vault has replaced all variables with their execution values and calculated the result of the expressions and arguments within your formula. After evaluating the formula, Vault returns a value, True, or False. |
True | When a formula returns true, it means that once Vault put in the variable values in the formula, the resulting calculations successfully passed the actions within the formula. For example, if you check that Field 1 is less than 10 ($field1.value__v > 10), the formula returns true when Field 1’s value is 6. |
False | When a formula returns false, it means that once Vault put in the variable values in the formula, the resulting calculations didn’t pass the actions within the formula. For example, if you check that Field 1 is less than 10 ($field1.value__v > 10), the formula returns false when Field 1’s value is 15. |
Operators & Functions
Operators and functions are actors within your formula. They can perform tasks against expressions and values within your formula to output a result.
What is the difference between an “operator” and a “function”?
All operators are functions, but not all functions are operators. Operators exclusively return the same data type that you put in, whereas functions can return a different data type. For example, consider the operator Add()
and the function Value()
. These are both considered “mathematic”. When you add two numbers together, the formula returns a number. Add(2, 2)
returns 4
. When you use the Value()
function, you input text and the formula outputs a number. Value(Right(“Veeofen 20”, 2)
returns the number 20
, instead of a text string.
This reference contains a list of all operators and functions currently supported in Vault.
Variables & References
You can use variables and references within your formula to act as placeholders for execution data. Vault replaces your variables and references with their actual execution values when the system evaluates your formula. Whenever you write data validation rules in Studio, each time you use an Identifier to call out a data collection Item, you are using a variable. A reference, in the context of Vault formulas, is a variable that is pointing to a value on a related object, instead of a value on an object. For example, when you use $site__vr.status__v
, Vault replaces that reference with the Status value from the Site object.
Operators
Use the operators below when building your formula. All operators are available everywhere that you can include a formula unless otherwise specified.
Math Operators
Math operators perform mathematic operations within your expression.
Operator | Description |
---|---|
Add | Calculates the sum of two values |
Subtract | Calculates the difference between two values |
Multiply | Multiplies two values |
Divide | Multiples two values |
Remainder | Remainder from one value divided by another |
Parenthesis | Evaluates expressions within the parenthesis first |
Add
Calculates the sum of two values
Syntax
+
Use
value1 + value2
Data Types
The Add operator accepts the following data types:
-
Number
-
DateTime with Number
-
Date with Number
-
DateTime with Time
-
Date with Interval
-
DateTime with Interval
Examples
Expression | Explanation |
---|---|
Lesion_measurement_1 + Lesion_measurement_2 |
Adds the two measurements entered as numbers |
First_injection + 15 |
Add 15 days to the First Injection Date to determine the Second Injection Date |
Subtract
Calculates the difference between two values
Syntax
-
Use
value1 - value2
Data Types
The Subtract operator accepts the following data types:
-
Number
-
DateTime with Number
-
Date with Number
-
DateTime with Time
-
Date with Interval
-
DateTime with Interval
Examples
Expression | Explanation |
---|---|
Second_injection_date - First_injection_date |
Calculates the number of days between two injection dates |
Multiply
Multiplies two values
Syntax
*
Use
value1 * value2
Data Types
The Multiply operator accepts the following data types:
- Number
Examples
Expression | Explanation |
---|---|
$value__v * 2.205 |
Converts a weight in kilograms to pounds |
Divide
Multiples two values
Syntax
/
Use
value1 / value2
Data Types
The Divide operator accepts the following data types:
- Number
Examples
Expression | Explanation |
---|---|
(Weight / (Height * Height)) * 703 |
Calculates the BMI of a Subject in imperial units |
Remainder
Remainder from one value divided by another
Syntax
%
Use
value1 % value2
Data Types
The Remainder operator accepts the following data types:
- Number
Examples
Expression | Explanation |
---|---|
@EventGroup.sequence__v % 2 |
Returns 0 if the Sequence Number of the current Event Group is even and 1 if it is odd |
Parenthesis
Evaluates expressions within the parenthesis first
Syntax
()
Use
(expression1)expression2
Data Types
The Parenthesis operator accepts the following data types:
Examples
Expression | Explanation |
---|---|
(Lesion_measurement_1 + Lesion_measurement_2)/2 |
Returns the average of 2 measurements |
Logical Operators
Logical operators perform logical operations within your expression.
Operator | Description |
---|---|
Equal | Evaluates if two values are equivalent |
Not Equal | Evaluates if two values are not equivalent |
Less Than | Evaluates if a value is less than another value |
Less Than or Equal To | Evaluates if a value is less than or equal to another value |
Greater Than | Evaluates if a value is greater than another value |
Greater Than or Equal To | Evaluates if a value is greater than or equal to another value |
And | Evaluates if two values or expressions are both true |
Or | Evaluates if at least one of two values or expressions is true |
Equal
Evaluates if two values are equivalent
Syntax
=
Use
value1 = value2
Data Types
The Equal operator accepts the following data types:
-
Number
-
Text
-
Date
-
DateTime
-
Time
-
DateTime with Date
-
Yes/No
-
Picklist
-
Picklist with Text
Examples
Expression | Explanation |
---|---|
Assessment_taken = "No" |
Returns true if the selected value for "Assessment Taken" is "No" |
Notes
When Dates are compared with DateTimes, Vault converts the DateTime to a Date.Picklist formulas and operators evaluate value names instead of value labels. For example, a picklist, Level (level__v), has three values named study_level__v, country_level__v and site_level__v with respective labels of Study, Country, and Site. To evaluate the value of a picklist, define the formula using the name, e.g., level__v = “country_level__v”, instead of using the label.
Not Equal
Evaluates if two values are not equivalent
Syntax
!=
Use
value1 != value2
Data Types
The Not Equal operator accepts the following data types:
-
Number
-
Text
-
Date
-
DateTime
-
DateTime with Date
-
Yes/No
-
Picklist
-
Picklist with Text
Examples
Expression | Explanation |
---|---|
Assessment_date != Visit_date |
Returns true if the Assessment Date is not the same as the Visit Date |
Severity != "Severe" |
Returns true if the value chosen for Severity is different from "Severe" |
Notes
When Dates are compared with DateTimes, Vault converts the DateTime to a Date.Picklist formulas and operators evaluate value names instead of value labels. For example, a picklist, Level (level__v), has three values named study_level__v, country_level__v and site_level__v with respective labels of Study, Country, and Site. To evaluate the value of a picklist, define the formula using the name, e.g., level__v = “country_level__v”, instead of using the label.
Less Than
Evaluates if a value is less than another value
Syntax
<
Use
value1 < value2
Data Types
The Less Than operator accepts the following data types:
-
Number
-
Date
-
DateTime
-
DateTime with Date
-
Time
Examples
Expression | Explanation |
---|---|
Informed_consent_date < Birth_date |
Returns true if the subject's Birth Date is after the Informed Consent Date |
Tumor_size_1 < Tumor_size_2 |
Returns true if the size of the first tumor is less than the size of the second |
Notes
When Dates are compared with DateTimes, Vault converts the DateTime to a Date.
Less Than or Equal To
Evaluates if a value is less than or equal to another value
Syntax
<=
Use
value1 <= value2
Data Types
The Less Than or Equal To operator accepts the following data types:
-
Number
-
Date
-
DateTime
-
DateTime with Date
-
Time
Examples
Expression | Explanation |
---|---|
Informed_consent_date <= Birth_date |
Returns true if the subject's Birth Date is after or equal to the Informed Consent Date |
Tumor_size_1 <= Tumor_size_2 |
Returns true if the size of the first tumor is less than or equal to the size of the second |
Notes
When Dates are compared with DateTimes, Vault converts the DateTime to a Date.
Greater Than
Evaluates if a value is greater than another value
Syntax
>
Use
value1 > value2
Data Types
The Greater Than operator accepts the following data types:
-
Number
-
Date
-
DateTime
-
DateTime with Date
-
Time
Examples
Expression | Explanation |
---|---|
Tumor_size_1 > Tumor_size_2 |
Returns true if the size of the first tumor is greater than the size of the second |
Informed_consent_date > Birth_date |
Returns true if the subject's Birth Date is before the Informed Consent date |
Notes
When Dates are compared with DateTimes, Vault converts the DateTime to a Date.
Greater Than or Equal To
Evaluates if a value is greater than or equal to another value
Syntax
>=
Use
value1 >= value2
Data Types
The Greater Than or Equal To operator accepts the following data types:
-
Number
-
Date
-
DateTime
-
DateTime with Date
-
Time
Examples
Expression | Explanation |
---|---|
Tumor_size_1 > Tumor_size_2 |
Returns true if the size of the first tumor is greater than or equal to the size of the second |
Informed_consent_date > Birth_date |
Returns true if the subject's Birth Date is before or equal to the Informed Consent date |
Notes
When Dates are compared with DateTimes, Vault converts the DateTime to a Date.
And
Evaluates if two values or expressions are both true
Syntax
&&
You can also use And()
in place of &&
.
Use
(expression) && (expression)
Data Types
The And operator accepts the following data types:
Examples
Expression | Explanation |
---|---|
Adverse_Event = "Other" && IsBlank(Other_Specify) |
Returns true if the Adverse Event selected is "Other" and the "Other specify" is blank |
Or
Evaluates if at least one of two values or expressions is true
Syntax
||
You can also use Or()
in place of ||
.
Use
(expression) || (expression)
Data Types
The Or operator accepts the following data types:
Examples
Expression | Explanation |
---|---|
100 > Weight || Weight > 200 |
Returns true if the subject's Weight is less than 100 or greater than 200 |
Text Operators
Text operators perform text operations within your expression.
Operator | Description |
---|---|
Concatenate | Connects two or more text strings |
Concatenate
Connects two or more text strings
Syntax
&
You can also use Concat()
in place of &
.
Use
text & text
Data Types
The Concatenate operator accepts the following data types:
Examples
Expression | Explanation |
---|---|
"Study: " & @Study.name__v |
Returns the text string, "Study: ", combined with the name of the current Study |
Functions
Use the functions below when building your formula. All functions are available everywhere that you can include a formula unless otherwise specified.
Math Functions
Math functions perform mathematic functions within your expression.
Function | Description |
---|---|
Abs | Calculates the absolute value of a number |
Ceiling | Returns the next integer greater than the value |
Floor | Returns the next integer less than the value |
Max | Returns the highest number from the set |
Median | Returns the medium number from the set |
Min | Returns the lowest number from the set |
Round | Rounds the value to the defined number of decimal places |
Sqrt | Returns the square root of a number |
Value | Returns a text string as a number |
Abs
Calculates the absolute value of a number
Syntax
Abs()
Use
Abs(number)
Examples
Expression | Explanation |
---|---|
Abs(Lesion_measurement_1 - Lesion_measurement_2) |
Returns the difference between the measurements of the first lesion and the second lesion as a positive value, regardless of which measurement is greater |
Ceiling
Returns the next integer greater than the value
Syntax
Ceiling()
Use
Ceiling(number)
Examples
Expression | Explanation |
---|---|
Ceiling(14.2) |
Returns 15 (14.2, rounded up to the nearest integer) |
Ceiling(-14.2) |
Returns -14 (-14.2 rounded up to the nearest integer) |
Floor
Returns the next integer less than the value
Syntax
Floor()
Use
Floor(number)
Examples
Expression | Explanation |
---|---|
Floor(14.2) |
Returns 14 (14.2, rounded down to the nearest integer) |
Ceiling(-14.2) |
Returns -15 (-14.2 rounded down to the nearest integer) |
Max
Returns the highest number from the set
Syntax
Max()
Use
Max(number, number...)
Examples
Expression | Explanation |
---|---|
Max(Lesion_measurement_1, Lesion_measurement_2) > 5 |
Returns true if the highest value between the two lesion measurements is greater than 5 |
Median
Returns the medium number from the set
Syntax
Median()
Use
Median(number, number...)
Examples
Expression | Explanation |
---|---|
Median(1, 3, 5, 6, 9) |
Returns 5, the middle (median) number |
Median(1, 3, 5, 6, 9, 13) |
Returns 5.5, the average of the two middle numbers |
Min
Returns the lowest number from the set
Syntax
Min()
Use
Min(number, number...)
Examples
Expression | Explanation |
---|---|
Min(Lesion_measurement_1, Lesion_measurement_2) < 5 |
Returns true if the lowest value between the two lesion measurements is greater than 5 |
Round
Rounds the value to the defined number of decimal places
Syntax
Round()
Use
Round(number, number of decimal places)
Examples
Expression | Explanation |
---|---|
Round(5.5, 0) |
Returns 6 (5.5 round up to 0 decimal places) |
Round(5.54, 1) |
Returns 5.5 (5.54 rounded up to 1 decimal place) |
Round(-5.5, 0) |
Returns -6 (-5.5 rounded down to 0 decimal places) |
Notes
- Vault rounds the number based on the number to the right of the decimal point. If that number is greater than 5, Vault rounds up, or away from zero. If that number is less than 5, Vault rounds down, or toward zero.
Sqrt
Returns the square root of a number
Syntax
Sqrt()
Use
Sqrt(number)
Examples
Expression | Explanation |
---|---|
Sqrt(25) |
Returns the number 5 |
Notes
- Numbers must be positive.
Value
Returns a text string as a number
Syntax
Value()
Use
Value(text)
Examples
Expression | Explanation |
---|---|
Value(Right("S1234", 4)) |
Returns the number 1234 |
Notes
- You will receive an error if you enter Text into a Value function that does not resolve to a number.
Logical Functions
Logical functions perform logical functions within your expression.
Function | Description |
---|---|
And | Returns true when all expressions are true |
Case | Compares the value of the expression with each case value and returns the paired result. If no values match, this function returns the last argument. |
If | Determines if expressions are true or false. Returns a given value if true and another value if false. |
Includes | Returns true when the multi-value picklist contains names that match the defined string or single-value picklist |
IsBlank | Returns true if the value is blank |
IsNumber | Returns true when the value is a number |
Not | Returns true when the expression is false and returns false when the expression is true |
Or | Returns true if any of the conditions is true |
And
Returns true when all expressions are true
Syntax
And()
You can also use &&
in place of And()
.
Use
And(expression, expression)
Examples
Expression | Explanation |
---|---|
And(Adverse_Event = "Other", IsBlank(Other_Specify)) |
Returns true if the Adverse Event selected is “Other” and the “Other specify” field is blank |
Case
Compares the value of the expression with each case value and returns the paired result. If no values match, this function returns the last argument.
Syntax
Case()
Use
Case(expression1, value1, result1, value2, result2, else_result)
Data Types
The Case function accepts the following data types:
-
Number
-
Text
-
Yes/No
-
Picklist
Examples
Expression | Explanation |
---|---|
Case(Severity, "MILD", "No need to check", "MODERATE", "Random checks needed", "SEVERE", "Check mandatory", "No answer") |
Returns if a Check is Mandatory or not depending on the Severity entered |
Notes
- When working with a picklist field, eg, picklist__c, the field returns the value names (study__v) instead of the value labels (Study).
If
Determines if expressions are true or false. Returns a given value if true and another value if false.
Syntax
If()
Use
If(expression1, value1, value2)
Examples
Expression | Explanation |
---|---|
If(Diabetes = "Type 2", Measurement_1 * 2, Measurement_2 *2) |
Returns Measurement 1 multiplied by 2 if "Type 2" is selected for Diabetes Type, otherwise, returns Measurement 2 multiplied by 2 |
Includes
Returns true when the multi-value picklist contains names that match the defined string or single-value picklist
Syntax
Includes()
Use
Includes(multi-value picklist, string/picklist value)
Examples
Expression | Explanation |
---|---|
Includes(colors__c, 'red__c') |
Returns true if Red is one of the values selected for the Colors field, a multi-value picklist |
Includes(colors__c, Picklist.field__v.value__v) |
Returns true if the value defined in the single-value picklist is currently selected in the multi-value picklist, Colors |
IsBlank
Returns true if the value is blank
Syntax
IsBlank()
Use
IsBlank(expression)
Examples
Expression | Explanation |
---|---|
IsBlank(Assessment_taken) && IsBlank(Assessment_date) |
Returns true if both the Assessment Taken and the Assessment Date have no value entered |
IsNumber
Returns true when the value is a number
Syntax
IsNumber()
Use
IsNumber(text)
Examples
Expression | Explanation |
---|---|
If(IsNumber(Measurement_1), Measurement_1/100, 0) |
Returns Measurement 1 divided by 100, if Measurement 1 is a number |
Not
Returns true when the expression is false and returns false when the expression is true
Syntax
Not()
Use
Not(expression)
Examples
Expression | Explanation |
---|---|
Not(IsBlank(Assessment_date)) |
Returns true if the Assessment Date is not blank |
Or
Returns true if any of the conditions is true
Syntax
Or()
Use
Or(expression, expression)
Examples
Expression | Explanation |
---|---|
Or(100 > Weight, Weight > 200) |
Returns true if the Subject's Weight is less than 100 or greater than 200 |
Text Functions
Logical functions perform logical functions within your expression.
Function | Description |
---|---|
Concat | Connects two or more text strings |
Find | Returns the position of a string within a string of text |
Left | Returns the specified number of characters from the beginning of a text string |
Length | Returns the number of characters in a specified text string |
Lower | Converts all letters in the specified string to lowercase |
Middle | Returns the number of text characters between two specified positions |
Right | Returns the specified number of characters from the end of a text string |
Substitute | Substitutes new text for old text in a string |
Text | Converts a value to text based on a specified format |
Trim | Removes any spaces and tabs from the beginning and end of a text string |
Upper | Converts all letters in the specified text string to uppercase |
Concat
Connects two or more text strings
Syntax
Concat()
You can also use &
in place of Concat()
.
Use
Concat(text, text...)
Examples
Expression | Explanation |
---|---|
Concat("Study: ", @Study.name__v) |
Returns the text string "Study: " combined with the Name of the current Study |
Find
Returns the position of a string within a string of text
Syntax
Find()
Use
Find(find_text, within_text), Find(find_text, within_text, number)
Examples
Expression | Explanation |
---|---|
Find(" ", "4280 Hacienda Dr, Pleasanton, CA") |
Returns 5, the position of the first space within the address |
Find(" ", "4280 Hacienda Dr, Pleasanton, CA", 2) |
Returns 14, the position of the second space in the address |
Left
Returns the specified number of characters from the beginning of a text string
Syntax
Left()
Use
Left(text, number)
Examples
Expression | Explanation |
---|---|
Left(name__v, 5) & "-" & abbvreviation__v |
Returns the first 5 characters of a product's Name and its abbreviation, concatenated together |
Length
Returns the number of characters in a specified text string
Syntax
Length()
Use
Length(text)
Lower
Converts all letters in the specified string to lowercase
Syntax
Lower()
Use
Lower(text)
Examples
Expression | Explanation |
---|---|
Lower("Company A") |
Returns the text string, "company a" |
Middle
Returns the number of text characters between two specified positions
Syntax
Middle()
Use
Middle(text, start_position, end_position)
Examples
Expression | Explanation |
---|---|
Middle("4280 Hacienda Dr, Pleasanton, CA", 6, 13) |
Returns the text string "Hacienda" |
Right
Returns the specified number of characters from the end of a text string
Syntax
Right()
Use
Right(text, number)
Examples
Expression | Explanation |
---|---|
Value(Right("S1234", 4)) |
Returns the number 1234 |
Substitute
Substitutes new text for old text in a string
Syntax
Substitute()
Use
Substitute(text, old_text, new_text)
Examples
Expression | Explanation |
---|---|
Substitute(ConMed_date, "UN", "15") |
Replaces the "UN" part of an unknown date with "15" |
Text
Converts a value to text based on a specified format
Syntax
Text()
Use
Text(value, "format")
Examples
Expression | Explanation |
---|---|
"The minimum Screening Date is " & Text(MinDate(Screening_Date), "yyyy-mm-dd") |
Returns the text "The minimum Screening Date is ", followed by the Screening Date in yyyy-mm-dd format |
Notes
-
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.Mar.2017
-
yyyy-mm-dd 2017-03-30
-
mmmm yyyy March 2017
-
dddd dd/mm/yy Thursday 31/03/17
Trim
Removes any spaces and tabs from the beginning and end of a text string
Syntax
Trim()
Use
Trim(text)
Examples
Expression | Explanation |
---|---|
Trim(" Phase III ") |
Returns the text string "Phase III" with the spaces before and after the string removed |
Upper
Converts all letters in the specified text string to uppercase
Syntax
Upper()
Use
Upper(text)
Examples
Expression | Explanation |
---|---|
Upper("Company A") |
Returns the text string "COMPANY A" |
Date & DateTime Functions
Date and DateTime functions perform Date and DateTime functions within your expression.
Function | Description |
---|---|
Date | Returns a date value from year, month, and day values |
DateValue | Returns the date part of a datetime |
Day | Returns the day of the month |
Days | Returns the specified number of days as an interval |
Hour | Returns the hour value from a date time or the current hour in terms of 0 to 23 |
Hours | Returns the specified number of hours as an interval |
Minute | Returns the minute value from a datetime or the current minute in terms of 0 to 59 |
Minutes | Returns the specified number of minutes as an interval |
Month | Returns the month from a date |
Months | Returns the specified number of months as an interval |
Now | Returns the current date and time |
Second | Returns the second value from a datetime or the current second in terms of 0 to 59 |
Time | Returns the time based on the specified hours, minutes, and seconds |
Today | Returns the current date for the user |
Weekday | Returns the day of the week from a date or datetime as a number from 1-7 |
Year | Returns the year from a date |
Years | Returns the specified number of years as an interval |
MaxDate | Replaces the unknown part of a date with the maximum possible value |
MinDate | Replaces the unknown part of a date with the minimum possible value |
MaxDateTime | Replaces the unknown part of a datetime with the maximum possible value |
MinDateTime | Replaces the unknown part of a datetime with the minimum possible value |
InWindow | Returns true if a date is in the specified window, based on the reference date |
Date
Returns a date value from year, month, and day values
Syntax
Date()
Use
Date(year, month, day)
Examples
Expression | Explanation |
---|---|
Date(2018, 3, 14) |
Returns the date 03-14-2018 |
Date(year(today), month(today())+1, day(today())) |
Returns the date one month from the current date |
Date(year(today()), month(today()), day(today())) + Time(12,0,0) |
Returns the date and time that represents today at noon |
DateValue
Returns the date part of a datetime
Syntax
DateValue()
Use
DateValue(datetime)
Examples
Expression | Explanation |
---|---|
DateValue(Vitals_exam_date) |
Returns the date of the Vitals Exam without the time, while still accommodating the conversion to UTC time |
DateValue(Vitals_exam_date, @Site.timezone__v) |
Returns the date of the Vitals Exam without the time in the current site's time zone |
Day
Returns the day of the month
Syntax
Day()
Use
Day(date)
Examples
Expression | Explanation |
---|---|
Day(Injection_date) |
Returns the day of the month that the injection was performed |
Days
Returns the specified number of days as an interval
Syntax
Days()
Use
Days(number)
Examples
Expression | Explanation |
---|---|
Injection_date + Days(10) |
Returns the date 10 days after the Injection Date |
Hour
Returns the hour value from a date time or the current hour in terms of 0 to 23
Syntax
Hour()
Use
Hour()
Examples
Expression | Explanation |
---|---|
Hour(created_date__v) |
Returns the hour when the object was created |
Hour() |
Returns the current hour for the user |
Hours
Returns the specified number of hours as an interval
Syntax
Hours()
Use
Hours(number)
Examples
Expression | Explanation |
---|---|
InWindow(Vitals_time, Injection_time, Hours(2), Hours(5), false, false) |
Returns true if the Vitals Collection time is between 2 and 5 hours after the Injection Time |
Notes
- In the current release, this function is only available to define time intervals inside the InInWindow() function.
Minute
Returns the minute value from a datetime or the current minute in terms of 0 to 59
Syntax
Minute()
Use
Minute(datetime), Minute()
Examples
Expression | Explanation |
---|---|
Minute(created_date__v) |
Returns the minute when the object was created |
Minute() |
Returns the current minute for the user |
Minutes
Returns the specified number of minutes as an interval
Syntax
Minutes()
Use
Minutes(number)
Examples
Expression | Explanation |
---|---|
InWindow(Vitals_time, Injection_time, Minutes(30), Minutes(45), false, false) |
Returns true if the Vitals Collection time is between 30 and 45 minutes after the Injection Time |
Notes
- In the current release, this function is only available to define time intervals inside the InInWindow() function.
Month
Returns the month from a date
Syntax
Month()
Use
Month(date), Month(datetime)
Examples
Expression | Explanation |
---|---|
Month(Injection_date) |
Returns the month that the injection was performed as a number |
Months
Returns the specified number of months as an interval
Syntax
Months()
Use
Months(number)
Examples
Expression | Explanation |
---|---|
Injection_date + Months(2) |
Returns the date 6 months after the Audit Start Date |
Now
Returns the current date and time
Syntax
Now()
Use
Now()
Examples
Expression | Explanation |
---|---|
Round((Now() - Birth_Date)/365, 0) |
Returns the subject's Age on the current date |
Notes
- Now() returns the current datetime for the user.
Second
Returns the second value from a datetime or the current second in terms of 0 to 59
Syntax
Second()
Use
Second(date), Second()
Examples
Expression | Explanation |
---|---|
Second(created_date__v) |
Returns the second when the object was created |
Second() |
Returns the current second for the user |
Time
Returns the time based on the specified hours, minutes, and seconds
Syntax
Time()
Use
Time(hour, minute, second)
Examples
Expression | Explanation |
---|---|
Time(hour(now()), minute(now()), second(now()) - Time(12,30,0) |
Returns the difference between the two times in terms of minutes' |
Date(year(today()), month(today()), day(today())) + Time(12,0,0) |
Returns the datetime that represents today at noon |
Today
Returns the current date for the user
Syntax
Today()
Use
Today()
Examples
Expression | Explanation |
---|---|
Today() = Sample_collection_date |
Returns true if the Sample Collection Date is equal to the current date in the user's timezone |
Weekday
Returns the day of the week from a date or datetime as a number from 1-7
Syntax
Weekday()
Use
Weekday(date), Weekday(datetime)
Examples
Expression | Explanation |
---|---|
If((Weekday(@Event.event_date__v) = 1) || (Weekday(@Event.event_date__v) = 7)), “Weekend Event Date”, “Not a weekend event date”) |
Year
Returns the year from a date
Syntax
Year()
Use
Year(date), Year(datetime)
Examples
Expression | Explanation |
---|---|
Date(Year(Today())+1, Month(Today()), Day(Today())) |
Returns the same day as the current date for next year |
Years
Returns the specified number of years as an interval
Syntax
Years()
Use
Years(number)
Examples
Expression | Explanation |
---|---|
Today() + Years(1) |
Returns the same day as the current date for the next year |
MaxDate
Replaces the unknown part of a date with the maximum possible value
Syntax
MaxDate()
Use
MaxDate(unknown date)
Examples
Expression | Explanation |
---|---|
MaxDate(2018-07-UN) |
Returns 2018-07-31 |
MaxDate(2018-UN-UN) |
Returns 2018-12-31 |
Notes
-
Use MaxDate() to wrap a date with unknowns for use in an expression.
-
Using MaxDate() on a date without Unknowns returns only the original date.
MinDate
Replaces the unknown part of a date with the minimum possible value
Syntax
MinDate()
Use
MinDate(unknown date)
Examples
Expression | Explanation |
---|---|
MinDate(2018-07-UN) |
Returns 2018-07-01 |
MinDate(2018-UN-UN) |
Returns 2018-01-01 |
Notes
-
Use MinDate() to wrap a date with unknowns for use in an expression.
-
Using MinDate() on a date without Unknowns returns only the original date.
MaxDateTime
Replaces the unknown part of a datetime with the maximum possible value
Syntax
MaxDateTime()
Use
MaxDateTime(unknown date)
Examples
Expression | Explanation |
---|---|
MaxDateTime(2018-07-UNT14:00) |
Returns 2018-07-31T14:00 |
MaxDate(2018-UN-UNT14:00) |
Returns 2018-12-31T14:00 |
Notes
-
Using MaxDateTime() on a datetime without Unknowns returns only the original datetime.
-
MaxDateTime() should not be used as a stand-alone function to set the value of an item, as this function is impacted by the conversion to UTC time applied to get the normalized value. Instead, use this function to perform calculations and comparisons that need a known DateTime to be evaluated.
MinDateTime
Replaces the unknown part of a datetime with the minimum possible value
Syntax
MinDateTime()
Use
MinDateTime(unknown date)
Examples
Expression | Explanation |
---|---|
MinDateTime(2018-07-UNT14:00) |
Returns 2018-07-01T14:00 |
MinDateTime(2018-UN-UNT14:00) |
Returns 2018-01-01T14:00 |
Notes
-
Using MinDateTime() on a datetime without Unknowns returns only the original datetime.
-
MinDateTime() should not be used as a stand-alone function to set the value of an item, as this function is impacted by the conversion to UTC time applied to get the normalized value. Instead, use this function to perform calculations and comparisons that need a known DateTime to be evaluated.
InWindow
Returns true if a date is in the specified window, based on the reference date
Syntax
InWindow()
Use
InWindow(date, date, interval, interval, interval, boolean, boolean)
Data Types
The InWindow function accepts the following data types:
-
Date
-
DateTime
-
Time
Examples
Expression | Explanation |
---|---|
InWindow(TestTime, ProcedureTime, Hours(1), Hours(3), false, false) |
Returns true if the Test Time is between 1 hour and 3 hours after the Procedure Time |
InWindow(ControlDate, TestDate, Days(3), Days(7), true, false) |
Returns true if the Control Date is after the Test Date by between 4 and 7 days |
Notes
-
Only objects of the same data type can be compared (Date with Date, DateTime with DateTime, and Time with Time).
-
The time interval can be defined using the following functions: Minutes(), Hours(), Days(), Months(), Years().
-
The two boolean arguments indicate if the lower boundary (1st argument) and upper boundary (2nd argument) are included in the overall window. true means that the boundary is excluded, and false means that the boundary is included.
Deprecated Functions
The following functions are no longer available in the new expression grammar.
Concatenate
Connects two or more text strings
Syntax
Concatenate(text, text)
Use Instead
Concat()&
DateAdd
Returns a date based on the offset (interval) from the starting date (date)
Syntax
DateAdd(date, interval)
Use Instead
date + number and date + interval
DateDiff
Returns the difference between two dates in number of days
Syntax
DateDiff(end, start)
Use Instead
date - date
DateTimeAdd
Returns a DateTime value based on the offset (interval) from the starting DateTime (datetime)
Syntax
DateTimeAdd(datetime, interval)
Use Instead
dateTime + number and dateTime + interval
DateTimeDiff
Returns the difference between two DateTime values as a number of days, hours, and minutes
Syntax
DateTimeDiff(end, start)
Use Instead
dateTime - dateTime
IfBlank
If the first argument is blank, this function returns the second argument. If not blank, this function returns the first argument.
Syntax
IfBlank(expression, expression)
Use Instead
If(IsBlank())
IfNull
If the first argument is null, this function returns the second argument. If not null, this function returns the first argument.
Syntax
IfNull(expression, expression)
Use Instead
If(IsBlank())
IsNull
Returns true when the value is null
Syntax
IsNull(expression, expression)
Use Instead
If(IsBlank())
NumberEquals
Returns true if both numeric arguments are equal
Syntax
NumberEquals(number, number)
Use Instead
=
PicklistEquals
Compares a picklist value’s base label with an existing global picklist value’s base label and returns true or false
Syntax
PicklistEquals(<picklist_field>, picklist_value__v)
Use Instead
=
TextEquals
Returns true if both text strings are equal
Syntax
TextEquals(text, text)
Use Instead
=
TimeDiff
Returns the difference between two times in number of minutes. This function subtracts the start time (start) from the end time (end).
Syntax
TimeDiff(end, start)
Use Instead
Time - Time
Date Formats
The table below lists available date formats:
Format | Example | Explanation |
---|---|---|
d | 1 | 1-digit day of the month |
dd | 1 | 2-digit day of the month |
ddd | Thu | 3-letter day of the week |
dddd | Thursday | Full day of the week |
mm | 3 | 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 | 2-digit day of month, 2-digit month, Full year |
yyyymmdd | 20170331 | Full year, 2-digit month, 2-digit day, with no spaces |
dd.mm.yyyy | 30.03.2017 | 2-digit day, 2-digit month, Full year, separated with periods (.) |
yyyy-mm-dd | 2017-03-30 | Full year, 2-digit month, 2-digit day |
mmmm yy | March 2017 | Full month, Full year |
dddd dd/mm/yy | Thursday 31/03/2017 | Full day of the week with the 2-digit day, 2-digit month, and Full year separated with forward slashes |
Number Formats
The table below lists available number formats:
Format | Example Input | Example Output | Explanation |
---|---|---|---|
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 (You can use multiple commas) |
- | 10 | -10 | Adds the minus sign to the expression |
% | 9 | %90 | Multiples 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 using scientific notation |
System Variables
A system variable is a system-defined dynamic object that stores a value, which can be referenced by one or more Vault applications. They are dynamic in the sense that their value changes depending on the context they are used in. For example, @User
is a system variable that points to the current logged in user’s profile and allows access to several properties, such as the user’s Status or Email.
Vault Platform System Variables
In the current release, one Vault Platform system variable is supported in Vault EDC, @User. You can reference this variable when writing rules.
Variable | Description |
---|---|
@User | References the user currently logged in |
@User
References the user currently logged in
Example Properties
-
@User.name__v
: Returns the current user’s Name -
@User.status__v
: Returns the current user’s Status -
@User.email__v
: Returns the current user’s Email -
@User.securityProfile__v
: Returns the current user’s Security Profile -
@User.language__v
: Returns the current user’s Language -
@User.timezone__v
: Returns the current user’s Timezone
Example Usage
Expression | Explanation |
---|---|
Case(@User.department__c, "Human Resources", "hr@sponsor.com", "Clinical Programming", "clinical_programming@company.com", "other@company.com") |
Returns an email address based on the current user's Department |
Vault EDC System Variables
EDC has several, specific system variables that you can use when writing rules. These variables allow quick access to objects in the Study or Casebook hierarchy.
The following objects and fields are available in Vault EDC for use in system variables:
System References
A reference, in the context of Vault formulas, is a variable that is pointing to a value on a related object, instead of a value on an object. For example, when you use $study__vr.phase__v, Vault replaces that reference with the Phase value from the Study object associated with the current site.
Vault Platform System References
The following system references are available as part of the Vault Platform, and so you can use them in any area of the application where formulas are available:
Object to a Related Object
Allows formulas to access properties on objects that are related to the current object in context.
Use
relationship__vr.name__vr
Example
principal_investigator__vr.research_and_focus_areas__v = "Macular Degeneration"
Returns True if the Principal Investigator for the current Site (object) has a research focus in Macular Degeneration.
Vault EDC System References
The following system references are available as part of Vault EDC, and so you can only use them in areas of the application where formulas are accepted as part of EDC (data validation rules, unit conversions, etc.).
Objects in an EDC study hierarchy follow the following order:
Study → Site → Casebook → Event Group → Event → Form → Item Group → Item
An EDC expression can reference several different objects and their properties inside a study hierarchy. To reference these objects, their access path in the study hierarchy must be written using a specific syntax.
Every reference must start with a $
sign and objects and properties must be separated by a period (.
).
The following objects and fields are available in Vault EDC for use in system references & variables:
Study
The following fields on the Study (study__v
) object are available for use in system references and variables:
Field Name | Field Label | Description |
---|---|---|
name__v |
Name | Returns the Name of the Study |
status__v |
Status | Returns the Status of the Study |
locked__v |
Locked | Returns the Locked status of the Study |
Study Country
The following fields on the Study Country (study_country__v
) object are available for use in system references and variables:
Field Name | Field Label | Description |
---|---|---|
name__v |
Name | Returns the Name of the Study Country |
country_abbreviation__v |
Country Abbreviation | Returns the Country Abbreviation of the Study Country |
Site
The following fields on the Site (site__v
) object are available for use in system references and variables:
Field Name | Field Label | Description |
---|---|---|
name__v |
Name | Returns the Study Site Number of the Site |
status__v |
Status | Returns the Status of the Site |
OID__v |
External ID | Returns the External ID of the Site |
locked__v |
Locked | Returns the Locked status of the Site |
timezone__v |
Timezone | Returns the Timezone of the Site |
Notes
- For sites, name__v returns the Study Site Number field value for that Site record. Study Site Number is the label for the name__v field.
Casebook
The following fields on the Casebook (casebook__v
) object are available for use in system references and variables:
Field Name | Field Label | Description |
---|---|---|
version__v |
Version | Returns the Version of the Casebook |
subject_status__v |
Subject Status | Returns the Subject Status of the Casebook (based on the Casebook Definition) |
Event Group
The following fields on the Event Group (event_group__v
) object are available for use in system references and variables:
Field Name | Field Label | Description |
---|---|---|
name__v |
Name | Returns the Name of the Event |
sequence__v |
Sequence | Returns the Sequence number of the Event Group (for repeating Event Groups) |
Event
The following fields on the Event (event__v
) object are available for use in system references and variables:
Field Name | Field Label | Description |
---|---|---|
name__v |
Name | Returns the Name of the Study |
event_date__v |
Event Date | Returns the Event Date of the Event |
sequence__v |
Sequence | Returns the Sequence number of the Event (for Events within repeating Event Groups) |
Form
The following fields on the Form (form__v
) object are available for use in system references and variables:
Field Name | Field Label | Description |
---|---|---|
name__v |
Name | Returns the Name of the Form |
sequence__v |
Sequence | Returns the Sequence number of the Form (for repeating Forms) |
intentionally_left_blank__v |
Intentionally Left Blank | Returns the Intentionally Left Blank status of the Form |
Item
The following fields on the Item (item__v
) object are available for use in system references and variables:
Field Name | Field Label | Description |
---|---|---|
intentionally_left_blank__v |
Intentionally Left Blank | Returns the Intentionally Left Blank status of the Item |
value__v |
Value | Returns the Value for the Item |
value_translated__v |
Value Translated | Returns the Value Translated for the Item |
value_normalized__v |
Value Normalized | Returns the Value Normalized for the Item |
Notes
- Do not use value___v for Items with the Date data type. By default, Date-type Items use the value_normalized__v for any calculations and forcing the use of value__v will cause an execution error.
Examples
For example, to access the date of the collection of vital signs at the end of a study, use the following (depending on the way the study is built in EDC Studio and the names of objects):
$End_of_Study.End_of_Study_Visit.Vital_Signs.General_Information.Date.value__v
This reference accesses the date for the collection of vital signs at the end of a study (the Date item, in the General Information item group, on the Vital Signs form, in the End of Study Visit event, in the End of Study event).
For example, if the expression is evaluated on the Vital Signs form (which is then considered the current Form), you could use:
@Form.General_Information.Date.value__v
You can also access properties at other levels, like Event or Form.
$Cohort_A.Visit_1.event_date__v
This reference accesses the Event Date for the first visit (the Visit 1 event) for the Cohort A event group.
Defining a Short Path
Specifying the full path to access all objects in an expression can become long and cumbersome, so you may find it useful to define short paths to reference a frequently used Form or Item Group in your expression (or any other object) using a #define statement.
You can use both system references and system variables as part of a #define statement.
The syntax to define a short path is:
#define vitals_info “$End_of_Study.Vital_Signs.General_Information”
The object vitals_info
can then be used to access related objects and properties.
The example from the previous section:
$End_of_Study.End_of_Study_Visit.Vital_Signs.General_Information.Date.value__v
becomes:
vitals_info.Date.value__v
Building Formulas
The formula editor functionality available to you depends on where in vault you’re using a formula. For specific details about formulas by functional area, see:
Valid Fields & Data Types
When defining the formula, be sure that the operators, functions, fields, and values in the formula match the field’s data type. For example, you cannot use a date field in the formula to update a text field without using the text function to convert it.
Vault automatically converts the following data types when used in a formula:
- Object Reference → Text
- Picklist → Text (except in formula fields on objects or field defaults)
- DateTime → Date (except in formula fields on objects or field defaults)
Vault provides functions that you can use to convert data types:
- Date → Text:
Text(date, format)
- Number → Interval:
Days(number)
,Months(number)
,Years(number)
,Hours(number)
,Minutes(number)
- Text → Number:
Value(text)
Identifiers
When building formulas to use in rules or in views, you can use identifiers to reference study data, for example, a certain Item on a Form.
You define identifiers hierarchically, until it is unique, and separate identifiers with a period (.
):
$Event_group.Event.Form.Item_group.Item
You can then use fields on the Item object to reference the collected value:
$Event_group.Event.Form.Item_group.Item.value__v
In the Studio Rules Editor, Vault can attempt to autocomplete identifiers, variables, operators, and functions as you enter them. Press Ctrl + Space to see a drop-down list of autocomplete options. If you press Ctrl + Space before beginning to type an identifier, Vault lists all valid identifiers in the drop-down.
If you use a design definition in more than one place, for example, if you use an Item across multiple Forms, you can use an @
to specify that you want to cover all instances of that definition.
For example, to evaluate an Item on multiple Forms, @Form.Item_group.Item
. You can use this at the Event Group, Event, and Form levels.
For Disable and Set Item Value rules, you can only use @Form
identifiers (not @EventGroup
, @Event
, or @ItemGroup
).
#Define Statements
You can use #define statements to define variables for identifiers, so that you don’t have to rewrite them each time you reference the identifier in your formula.
For example, to define a shortened name for the Diastolic Blood Pressure item:
#define diastolic @Form.Vitals.Diastolic_blood_pressure.value__v
Now, each time I use diastolic
in my formula, Vault automatically reads it as the identifier in my #define statement.
- Put your #define statements at the top of your formula, one per line.
- You must include #define statements in each individual formula. These variables are defined in the context of the rule, and so you cannot use a single #define statement across multiple rules.
Comments
You can use comments to make notes within your formula. For example, you may want to add a brief explanation of a certain expression to remind yourself of how it works later.
To add a comment to your formula, enter your comment text at the top of your formula (the first line) and surround it with /*
and */
.
/* This is a comment. */
Guidelines for Formulas
Use these guidelines when writing your formula:
- Decimal points are periods, regardless of your Vault’s locale.
- The maximum expression length is 1,500 characters.
- When a formula contains more than one expression, surround the expression in parentheses ().
- Note that Vault CDMS treats numbers as floats, resolving them at the end of a formula evaluation.
Functions
Use these guidelines for adding functions to a formula expression:
- Function names are case sensitive.
- Functions may have one or multiple arguments. Different functions allow different numbers of arguments.
- Vault handles formulas from left to right. For nested functions, Vault handles the sub-functions from left to right before handling the parent functions.
Operators
Use these guidelines for adding operators to a formula expression:
- Standard order of operations applies: multiplication and division before addition and subtraction unless you override by using parentheses to group.
- You can use math operations to add both numbers and number-type fields.
- You cannot use Date type fields in math operations. Use the various date functions outlined above.
Boolean Fields
When you write a formula referencing a Yes/No-type object field or a boolean-type Item (checkboxes), use true
for Yes and false
for No (unselected checkbox).
Blank Value Handling
This dictates how Vault handles blank field values within the formula:
- As zero: Vault substitutes a zero for the blank value, allowing you to complete the formula calculation.
- As null: Vault treats the blank value as null, causing the entire expression to return a null/blank value.
Example
This example shows how different blank handling options affect the outcome of this formula:
Formula: @Form.adverse_event_details.event_length_in_days.value__v - @Form.adverse_event_treatment.day_of_treatment.value__v
Adverse Event Length in Days | Number of Days After Event Beginning that Treatment Occurred | “As zero” result | “As null” result |
---|---|---|---|
5 | 0 | 5 | 5 |
7 | blank | 9 | blank |
Error Handling
Any syntax errors must be resolved before you can create or update your rule. When you click out of the Criteria field, Vault displays Expression is invalid unless your formula syntax is correct. The following are common syntax errors:
- Return data types are mismatched. For example, if the return type is Date, but your expression returns a number, you will receive an error.
- Your formula is missing a closing parenthesis, or you have mismatched parentheses.
- You have an incorrect number of arguments in your function. For example, you have three arguments in an
if(expression, value 1, value 2)
function. - Your function has an incorrect parameter value.
Migration (V1 to V2)
Enhanced Vault Formulas are automatically available in the platform application areas using formulas, and you can begin to use them immediately.
For EDC-specific formula usage (rules and views), you must enable Enhanced Vault Formulas on a study-by-study basis.
Studies created before 19R1 (released in April 2019) cannot upgrade to the enhanced formula language. Such studies must continue to use the V1 formula language (see the reference here) for their Rules and derived Column mappings.
How to Enable Enhanced Vault Formulas for a Study
To enable the Enhanced Vault Formulas on your Study:
- Verify that your Study does not contain any formulas using V1 of the formula language.
- Navigate to the Study Configuration object in Admin > Business Admin.
- Locate your Study and click to open it.
- Click Edit.
- In the Expression Engine Version field, enter
2
. - Click Save.
Your Study is now using the enhanced formula language.
Move an Existing Design to a New Study with Enhanced Vault Formulas
You can also choose to create a new Study (new Studies have Enhanced Vault Formulas enabled by default) and import your existing design into that new Study. Vault imports system-managed rules (required, range, and future date validation checks) and unit conversions (automatically replacing value__v
with $value__v
) but not custom rules or derived column formulas.
- Export your study design. See details here.
- Create a new Study.
- Import your study design into the new Study. See details here.
- Vault removes any custom rules and derived column formulas and then imports your Study.
You can now create new formula expressions using Enhanced Vault Formulas for your Rules, Derived-type Columns, and unit conversions.
Additional Resources
There are additional help topics with use-case specific information on how to use formulas: