Formula Reference V2

Current

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.

Expression Engine Version: The content below refers to the updated formula grammar (Expression Engine V2), which was released in April 2019 with 19R1. To write rule expressions for Studies created prior to 19R1, see the Formula Reference (V1).

Concepts

Review these concepts to familiarize yourself with how formulas work in Vault.

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 mathematical functions. 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 (interprets number as days)

  • Date with Number (interprets number as days)

  • 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

  • Date

  • DateTime

  • DateTime with Number (interprets number as days)

  • Date with Number (interprets number as days)

  • Date with DateTime (converts DateTime to Date)

  • Date with Interval

  • DateTime with Interval

  • Time with Time (returns time difference in minutes)

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

  • DateTime with Date (converts DateTime to Date)

  • Yes/No

  • Picklist (both picklists must be single-value

  • 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, using the vault’s timezone.

  • 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 (converts DateTime to Date)

  • Yes/No

  • Picklist (both picklists must be single-value

  • 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, using the vault’s timezone.

  • 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

  • Date with DateTime (converts DateTime to Date)

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

  • Date with DateTime (converts DateTime to Date)

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

  • Date with DateTime (converts DateTime to Date)

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

  • Date with DateTime (converts DateTime to Date)

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 mathematical functions within your expression.

Function Description
Abs

Calculates the absolute value of a number

Average

Calculates the average of the provided numbers

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

Calculates the median value of its arguments. This function accepts arguments with the number data type.

Min

Returns the lowest number from the set

Power

Calculates the value of a number raised to the power of another number (exponent)

Round

Rounds the value to the defined number of decimal places

Sqrt

Returns the square root of a number

Sum

Calculates the sum of the provided numbers

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


Average

Calculates the average of the provided numbers

Syntax

Average()

Use

Average(number, number, number...)

Examples
Expression Explanation
Average(lesion_measurement_1, lesion_measurement_2, legion_measurement_3)

Returns the average for the three lesion measurements as a number


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

Notes
  • You can use this function with the Number, Date, and DateTime data types. You can’t mix data types in a single Max() request. If you need to mix Date and DateTime, convert DateTimes to Dates using DateValue.

Median

Calculates the median value of its arguments. This function accepts arguments with the number data type.

Syntax

Median()

Use

Median(number, number...)

Examples
Expression Explanation
Median(@EventGroup[*].e_visit.measurements.ig_measurements.lesion_size)

Returns the median value of all instances of the Lesion Size item in the Treatment Visit repeating event group.


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

Notes
  • You can use this function with the Number, Date, and DateTime data types. You can’t mix data types in a single Min() request. If you need to mix Date and DateTime, convert DateTimes to Dates using DateValue.

Power

Calculates the value of a number raised to the power of another number (exponent)

Syntax

Power()

Use

Power(number, number)

Examples
Expression Explanation
(8.22 * Power(pulse_period, 0.3558)) > 440

Returns true when the subject’s QT interval (calculated from the pulse_period item) is greater than 440

QT / (Power(RR, 1/3))

A common use for the Power function in CDMS is to calculate QTcF interval values using the Fridericia (QTcF) formula, QTcF = QT / (RR^1/3)


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.

Sum

Calculates the sum of the provided numbers

Syntax

Sum()

Use

Sum(number, number, number...)

Examples
Expression Explanation
Sum(morning_dose_amount, evening_dose_amount)

Returns the sum of the monring and evening dose amounts


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 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 picklist contains names that match the defined string or single-value picklist

Syntax

Includes()

Use

Includes(string/picklist value)

Examples
Expression Explanation
Includes(cl_colors, 'RED')

Returns true if Red is one of the values selected for the Colors field in a defined string or single-value picklist

Notes
  • Multi-value picklists are available in Vault Platform. For CDMS, use the FindValue() function to locate values selected from a picklist.

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)

Examples
Expression Explanation
Find(" ", "4280 Hacienda Dr, Pleasanton, CA")

Returns 5, the position of the first space within 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, count)

Examples
Expression Explanation
Middle("4280 Hacienda Dr, Pleasanton, CA", 6, 13)

Returns the text string “Hacienda”, which is from the 6th through the 13th characters of the provided text


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”


Aggregate Functions

Aggregate functions perform functions on values from aggregate identifiers within your expression. Some of these aggregate functions are also available for use with any identifier type, but they are also listed here to provide a complete list of functions available for aggregate identifiers.

Function Description
AllEqual

Checks if all values of the aggregate identifiers are equal. Returns true if the values are equal, and returns false if the values are not equal.

Average

Returns the average of all values from the aggregate identifiers.

Count

Returns the number of instances of the aggregate identifier (for example, the number of instances for the identified repeating form).

CountIf

Returns the number of times the specified value can be found across all of the specified identifiers.

FindValue

Checks if a given value is present in at least one of the values for the identifiers in the expression.

First

Returns the first (by lowest Sequence Number) value of the aggregate identifier.

GetAllMatches

Returns an array of values from the third parameter that are found in the same instances where the value (first parameter) can be found in the second parameter. Both identifiers passed must have the same number of values. So, both identifiers must use the same aggregation path.

HasDuplicates

Returns true if any 2+ arrays have equal values for all the identifiers inside. Does not return true if the duplicates are null or blank values. Null or blank values are ignored by default.

IsBlank

Returns true if the array is blank. Returns false if the array has at least one value, even if that value is null.

IsAnyBlank

Checks if any values from the identifier is blank. Returns true if there are any blanks.

Last

Returns the last (by highest Sequence Number) value of the aggregate identifier.

Max

Returns the maximum (highest, largest) value of the values from the aggregate identifiers.

Median

Calculates the median value of its arguments. This function accepts arguments with the number data type.

Min

Returns the minimum (lowest, smallest) value of the values from the aggregate identifiers.

NoBlanks

Returns an array of values with any null values removed. This function preserves the original order of the values passed.

Sum

Returns the sum of the values from the aggregate identifier.

AllEqual

Checks if all values of the aggregate identifiers are equal. Returns true if the values are equal, and returns false if the values are not equal.

Syntax

AllEqual()

Use

AllEqual(Identifier, Identifier, ...)

Examples
Expression Explanation
AllEqual(@EventGroup[*].e_visit.measurements.ig_measurements.lesion_size)

Returns true if all values for Lesion Size are equal across all instances of the Treatment Visit repeating event group.


Average

Returns the average of all values from the aggregate identifiers.

Syntax

Average()

Use

Average(Identifier), Average(Identifier, Identifier)

Examples
Expression Explanation
Average($Visit1.Visit1.Measurements[*].Lesion.LesionSize)

Returns the average of all values for the Lesion Size item across all instances of the repeating Measurements form.


Count

Returns the number of instances of the aggregate identifier (for example, the number of instances for the identified repeating form).

Syntax

Count()

Use

Count(Identifier, Identifier, ...)

Examples
Expression Explanation
Count($eg_common.e_common.adverse_event[*].AE.AETERM)

Returns the number of instances of the AETERM item on the repeating Adverse Event form in the casebook.

Notes
  • This function includes blank (or null) values. If an Item was left blank or marked as intentionally left blank, it is included in the count. Use the NoBlanks function to remove any null values.

CountIf

Returns the number of times the specified value can be found across all of the specified identifiers.

Syntax

CountIf()

Use

CountIf(Value, Identifier, Identifier)

Examples
Expression Explanation
CountIf("Y", $eg_common.e_common.adverse_event[*].ig_ae.AESER)

Returns the number of times the Is the Adverse Event serious item is set to Yes (“Y”) across all instances of the Adverse Event form in the casebook.


FindValue

Checks if a given value is present in at least one of the values for the identifiers in the expression.

Syntax

FindValue()

Use

FindValue(value, identifier)

Examples
Expression Explanation
FindValue("SERIOUS", $Logs.Logs.Adverse_event[*].AE_Details.AESER)

Returns true if the value “SERIOUS” can be found within any value for the AE Seriousness item in any of the instances of the Adverse Event form.


First

Returns the first (by lowest Sequence Number) value of the aggregate identifier.

Syntax

First()

Use

First(Identifier)

Examples
Expression Explanation
First($Visit1.Visit1.Measurements.Lesion[*].LesionSize) > Last($Visit1.Visit1.Measurements.Lesion[*].LesionSize)

Compares the first Lesion Size entry in the repeating item group, Lesion, at Visit 1 to the last Lesion Size from Visit 1.


GetAllMatches

Returns an array of values from the third parameter that are found in the same instances where the value (first parameter) can be found in the second parameter. Both identifiers passed must have the same number of values. So, both identifiers must use the same aggregation path.

Syntax

GetAllMatches()

Use

GetAllMatches(Value, Identifier, Identifier)

Examples
Expression Explanation
GetAllMatches(10, @Form.measurement[*].lesion_size, @Form.measurement[*].date

Returns the Date item value for all instances of the Measurement item group where the value of the Lesion Size item is 10.

Notes
  • Aggregation paths:
    • If the first identifier is $EG[*].EV.F.IG.IT, the second identifier should start with $EG[*].
    • If the first identifier is $EG.EV.F[*].IG.IT, the second identifier should start with $EG.EV.F[*].
    • If the first identifier is $EG.EV.F.IG[*].IT, the second identifier should start with $EG.EV.F.IG[*].
    • If the first identifier is $EG[*].EV.F[*].IG[*].IT, the second identifier should start with $EG[*].EV.F[*].IG[*].
    • If the first identifier is $EG[*].EV.F[*].IG.IT, the second identifier should start with $EG[*].EV.F[*].
    • If the first identifier is $EG[*].EV.F.IG[*].IT, the second identifier should start with $EG[*].EV.F.IG[*].
    • If the first identifier is $EG.EV.F[*].IG[*].IT, the second identifier should start with $EG.EV.F[*].IG[*].

HasDuplicates

Returns true if any 2+ arrays have equal values for all the identifiers inside. Does not return true if the duplicates are null or blank values. Null or blank values are ignored by default.

Syntax

HasDuplicates()

Use

HasDuplicates(Identifier1, Identifier2, Identifier3)

Examples
Expression Explanation
HasDuplicates($EG.EV.AE[*].AEIG.AEITEM)

Returns true if the Adverse Event Item values are equal across Adverse Event forms.

HasDuplicates($EG.EV.AE[*].AEIG.AEITEM, $EG.EV.AE[*].AEIG.AESTARTDATE)

Returns true if the Adverse Event Item and Adverse Event Start Date values are equal across Adverse Event forms.

HasDuplicates($EG.EV.AE[*].AEIG.AEITEM, $EG.EV.AE[*].AEIG.AESTARTDATE, $EG.EV.AE[*].AEIG.AEENDDATE, $EG.EV.AE[*].AEIG.SERIOUS)

Returns true if the Adverse Event Item, Adverse Event Start Date, and Adverse Event End Date values are equal and the event is Serious across Adverse Event forms.


IsBlank

Returns true if the array is blank. Returns false if the array has at least one value, even if that value is null.

Syntax

IsAnyBlank()

Use

IsBlank(expression)

Examples
Expression Explanation
IsBlank(@Form.ig_physexam[*].specify_abnormalities)

Returns true if the Specify Abnormalities item is blank across all instances of the Physical Exam repeating item group.


IsAnyBlank

Checks if any values from the identifier is blank. Returns true if there are any blanks.

Syntax

IsAnyBlank()

Use

IsAnyBlank(Identifier, Identifier, ...)

Examples
Expression Explanation
IsAnyBlank(@Form.ig_phsexam[*].specify_abnormalities)

Returns true if any instance of the Specify Abnormalities item is blank across all instances of the Physical Exam repeating Item Group.


Last

Returns the last (by highest Sequence Number) value of the aggregate identifier.

Syntax

Last()

Use

Last(Identifier)

Examples
Expression Explanation
Last($Visit1.Visit1.Measurements.Lesion[*].DATE) > First($Visit1.Visit1.Measurements.Lesion[*].DATE)

Compares the last Lesion Size entry in the repeating item group, Lesion, at Visit 1 to first Lesion Size from Visit 1.


Max

Returns the maximum (highest, largest) value of the values from the aggregate identifiers.

Syntax

Max()

Use

Max(Identifier), Max(Identifier, Identifier)

Examples
Expression Explanation
Max($Visit1.Visit1.Measurements[*].Lesion.LesionSize) $Screening.Screening.InitialMeasurement.Lesion.InitialLesionSize)

Returns the highest value from the values for the Lesion Size item on both the Initial Measurement form and all instances of the Measurements form.


Median

Calculates the median value of its arguments. This function accepts arguments with the number data type.

Syntax

Median()

Use

Median(number, number...)

Examples
Expression Explanation
Median(@EventGroup[*].e_visit.measurements.ig_measurements.lesion_size)

Returns the median value of all instances of the Lesion Size item in the Treatment Visit repeating event group.


Min

Returns the minimum (lowest, smallest) value of the values from the aggregate identifiers.

Syntax

Min()

Use

Min(Identifier), Min(Identifier, Identifier)

Examples
Expression Explanation
Min($Visit1.Visit1.Measurements[*].Lesion.LesionSize, $Screening.Screening.InitialMeasurement.Lesion.InitialLesionSize)

Returns the lowest value from the values for the Lesion Size item on both the Initial Measurement form and all instances of the Measurements form.


NoBlanks

Returns an array of values with any null values removed. This function preserves the original order of the values passed.

Syntax

NoBlanks()

Use

NoBlanks(Identifier, Identifier, ....)

Examples
Expression Explanation
NoBlanks(@Form.ig_physexam[*].specify_abnormalities)

Returns the values for the Specify Abnormalities item, with any null values removed, across all instances of the Physical Exam repeating Item Group.


Sum

Returns the sum of the values from the aggregate identifier.

Syntax

Sum()

Use

Sum(Identifier)

Examples
Expression Explanation
Sum($Screening.Screening.Measurements[*].Lesion.LesionSize)

Returns the sum of all values for the lesion Size item across all instances of the repeating Measurements form.


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 current hour in terms of 0 to 23

Hours

Returns the specified number of hours as an interval

Minute

Returns 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

NetWorkdays

Returns the number of workdays between two (2) dates/datetimes. This function supports mixing dates and datetimes. Be mindful of timezones when using datetimes. This function also accepts optional parameters for weekends and holiday schedules. Admins can configure holidays from Business Admin > Holiday Schedules.

Now

Returns the current date and time

Second

Returns the current second in terms of 0 to 59

StartOfDay

Returns a DateTime in UTC that is the equivalent of the bginning of the day in the specified timezone

Time

Returns the time based on the specified hours, minutes, and seconds

Today

Returns the current date for the user. Include the optional “timezone” parameter to return the current date in a specific timezone.

Weekday

Returns the day of the week from a date or datetime as a number from 1-7

Workday

Returns a date a given number of days in the future factoring out weekends and, optionally, holidays. This function also accepts optional parameters for weekends and holiday schedules. Admins can configure holidays from Business Admin > Holiday Schedules.

Year

Returns the year from a given date or datetime. If no date or datetime is provided, it returns the current year.

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

SiteDateTime

Automatically creates datetime from Date item and Time item in Site timezone

SiteDateValue

Returns the date from a datetime item and automatically puts it in Site timezone

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 the Vault’s timezone

If(Right(VSDTC , 4) = "UNKZ", DateValue(MinDateTime(VSDTC), UTC), DateValue(MinDateTime(VSDTC), @Site.timezone__v))

This example shows an expression using ‘SiteDateValue()’ which should be used for datetime items that don’t allow unknowns


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 current hour in terms of 0 to 23

Syntax

Hour()

Use

Hour()

Examples
Expression Explanation
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.

  • This function doesn’t return the hour from a DateTime or Time item.

  • Don’t use this function with decimal places. Instead, use the Minutes() function with the equivalent number of minutes, for example, Minutes(30) instead of Hours(0.5).


Minute

Returns the current minute in terms of 0 to 59

Syntax

Minute()

Use

Minute()

Examples
Expression Explanation
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


NetWorkdays

Returns the number of workdays between two (2) dates/datetimes. This function supports mixing dates and datetimes. Be mindful of timezones when using datetimes. This function also accepts optional parameters for weekends and holiday schedules. Admins can configure holidays from Business Admin > Holiday Schedules.

Syntax

NetWorkdays()

Use

NetWorkdays(start_date/datetime, number_of_days, weekend_number, holiday_schedule)

Examples
Expression Explanation
NetWorkdays($screening.screening_visit.event_date__v, $baseline.baseline_visit.event_date__v,1, "United States")

Returns the number of workdays, based on holidays in the United States, between the date for the Screening Visit ($screening.screening_visit.event_date__v) and the date for the Baseline Visit ($baseline.baseline_visit.event_date__v)


Now

Returns the current date and time

Syntax

Now()

Use

Now(), Now(Timezone)

Examples
Expression Explanation
Round((Now() - Birth_Date)/365, 0)

Returns the subject’s Age on the current date

Notes
  • Now() without the timezone parameter returns the current datetime in UTC.

  • Use the timezone parameter to specify a timezone to return the current datetime. See this list of entry formats.

  • To return the current date in the site’s timezone, you can use DateValue(Now(), @Site.timezone__v).


Second

Returns the current second in terms of 0 to 59

Syntax

Second()

Use

Second()

Examples
Expression Explanation
Second()

Returns the current second for the user


StartOfDay

Returns a DateTime in UTC that is the equivalent of the bginning of the day in the specified timezone

Syntax

StartOfDay()

Use

StartOfDay(day, "Timezone")

Examples
Expression Explanation
StartOfDay(event_date, "Europe/Oslo")

Returns the DateTime in UTC that is the equivalent of the start of the day for the Event Date in the Europe/Oslo timezone


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. Include the optional “timezone” parameter to return the current date in a specific timezone.

Syntax

Today()

Use

Today(), Today("timezone")

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”)

Workday

Returns a date a given number of days in the future factoring out weekends and, optionally, holidays. This function also accepts optional parameters for weekends and holiday schedules. Admins can configure holidays from Business Admin > Holiday Schedules.

Syntax

Workday()

Use

Workday(start_date/datetime, number_of_days, weekend_number, holiday_schedule)

Examples
Expression Explanation
Workday($Screening, Screening_visit.event_date__v,1,"United States")

Returns a date 10 workdays (no weekends or US holidays) in the future from the date of the Screening Visit.


Year

Returns the year from a given date or datetime. If no date or datetime is provided, it returns the current year.

Syntax

Year()

Use

Year(date)

Examples
Expression Explanation
Year($Screening.Screening_Visit.Demographics.Creation_Criteria.DOB)

Returns the year from the birth date item.


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), MaxDate(unknown date, timezone)

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.

  • Use the timezone parameter to specify a timezone to return the current date. See this list of entry formats.


MinDate

Replaces the unknown part of a date with the minimum possible value

Syntax

MinDate()

Use

MinDate(unknown date), , MinDate(unknown date, timezone)

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.

  • Use the timezone parameter to specify a timezone to return the current date. See this list of entry formats.


MaxDateTime

Replaces the unknown part of a datetime with the maximum possible value

Syntax

MaxDateTime()

Use

MaxDateTime(unknown date), MaxDateTime(unknown date, timezone)

Examples
Expression Explanation
MaxDateTime(2018-07-UNT14:00)

Returns 2018-07-31T14:00

MaxDateTime(2018-12-UNTUN:UN)

Returns 2018-12-31T23:59

Notes
  • Using MaxDateTime() on a datetime without Unknowns returns only the original datetime.

  • For unknown times, Vault returns 23 for hours and 59 for minutes (23:59).

  • 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.

  • Use the timezone parameter to specify a timezone to return the current datetime. See this list of entry formats.


MinDateTime

Replaces the unknown part of a datetime with the minimum possible value

Syntax

MinDateTime()

Use

MinDateTime(unknown date), MinDateTime(unknown date, timezone)

Examples
Expression Explanation
MinDateTime(2018-07-UNT14:00)

Returns 2018-07-01T14:00

MinDateTime(2018-12-UNTUN:UN)

Returns 2018-12-01T00:00

Notes
  • Using MinDateTime() on a datetime without Unknowns returns only the original datetime.

  • For unknown times, Vault returns 00 for both hours and minutes (00:00).

  • 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.

  • Use the timezone parameter to specify a timezone to return the current datetime. See this list of entry formats.


InWindow

Returns true if a date is in the specified window, based on the reference date

Syntax

InWindow()

Use

InWindow(date, date, 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.


SiteDateTime

Automatically creates datetime from Date item and Time item in Site timezone

Syntax

SiteDateTime()

Use

SiteDateTime(date, time)

Data Types

The SiteDateTime function accepts the following data types:

  • Date

  • Time

Examples
Expression Explanation
SiteDateTime(VSDate, VSTime)

Returns the Vital Signs date and Vital Signs time.


SiteDateValue

Returns the date from a datetime item and automatically puts it in Site timezone

Syntax

SiteDateValue()

Use

SiteDateValue(datetime)

Data Types

The SiteDateValue function accepts the following data types:

  • Datetime
Examples
Expression Explanation
SiteDateValue(2021, 6, 15, Injection_time)

Returns the Injection Time on the date 06-15-2021 in the Site timezone

Notes
  • MinDateTime and MaxDateTime are not supported for this function when date portions are unknown.

Deprecated Functions

The following functions are no longer available in the new expression grammar.

Concatenate

Connects two or more text strings. This function is only available for studies using V1 of the Expression Engine. See Use Instead for a function to use in V2.

Syntax

Concatenate(text, text)

Use Instead

Concat()&


DateAdd

Returns a date based on the offset (interval) from the starting date (date). This function is only available for studies using V1 of the Expression Engine. See Use Instead for a function to use in V2.

Syntax

DateAdd(date, interval)

Use Instead

date + number and date + interval


DateDiff

Returns the difference between two dates in number of days. This function is only available for studies using V1 of the Expression Engine. See Use Instead for a function to use in V2.

Syntax

DateDiff(end, start)

Use Instead

date - date


DateTimeAdd

Returns a DateTime value based on the offset (interval) from the starting DateTime (datetime). This function is only available for studies using V1 of the Expression Engine. See Use Instead for a function to use in V2.

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. This function is only available for studies using V1 of the Expression Engine. See Use Instead for a function to use in V2.

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. This function is only available for studies using V1 of the Expression Engine. See Use Instead for a function to use in V2.

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. This function is only available for studies using V1 of the Expression Engine. See Use Instead for a function to use in V2.

Syntax

IfNull(expression, expression)

Use Instead

If(IsBlank())


IsNull

Returns true when the value is null. This function is only available for studies using V1 of the Expression Engine. See Use Instead for a function to use in V2.

Syntax

IsNull(expression, expression)

Use Instead

If(IsBlank())


NumberEquals

Returns true if both numeric arguments are equal. This function is only available for studies using V1 of the Expression Engine. See Use Instead for a function to use in V2.

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. This function is only available for studies using V1 of the Expression Engine. See Use Instead for a function to use in V2.

Syntax

PicklistEquals(<picklist_field>, picklist_value__v)

Use Instead

=


TextEquals

Returns true if both text strings are equal. This function is only available for studies using V1 of the Expression Engine. See Use Instead for a function to use in V2.

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). This function is only available for studies using V1 of the Expression Engine. See Use Instead for a function to use in V2.

Syntax

TimeDiff(end, start)

Use Instead

Time - Time


Date Formats

The table below lists available date formats:

Format Example Explanation
HH:ii 11:30 Hour and minute
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:

Study

The following fields on the Study (study__v) object are available for use in system references and variables:

Field Name Field Label Description Rule Expression Example
locked__v Locked Returns the Locked status of the Study @Study.locked__v
name__v Name Returns the Name of the Study @Study.name__v
oid__v External ID Returns the External ID of the Study @Study.oid__v

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 Rule Expression Example
name__v Name Returns the Study Country of the associated Site @StudyCountry.name__v
country_abbreviation__v Country Abbreviation Returns the Country Abbreviation of the Study Country @StudyCountry.country_abbreviation__v

Site

The following fields on the Site (site__v) object are available for use in system references and variables:

Field Name Field Label Description Rule Expression Example
locked__v Locked Returns the Locked status of the Site @Site.locked__v
name__v Name Returns the Study Site Number of the Site @Site.name__v
oid__v External ID Returns the External ID of the Site @Site.oid__v
principal_investigator__v Principal Investigator Returns the Principal Investigator for the Site @Site.principal_investigator__v
status__v Status Returns the Status of the Site @Site.status__v
timezone__v Timezone Returns the Timezone of the Site @Site.timezone__v
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 Rule Expression Example
version__v Version Returns the Version of the Casebook @Casebook.version__v
subject_status__v Subject Status Returns the Subject Status of the Casebook (based on the Casebook Definition) @Casebook.subject_status__v
current_sdv_plan__v Current SDV Plan Returns the SDV Review Plan currently assigned to the Subject (Casebook) @Casebook.current_sdv_plan__v
current_dmr_plan__v Current DMR Plan Returns the DMR Review Plan currently assigned to the Subject (Casebook) @Casebook.current_dmr_plan__v
subject_name__v Subject ID Returns the Subject ID for the casebook's Subject @Casebook.subject_name__v
var Variable Can be used as an identifier to represent Casebook Variables that have been defined for the study @Var.enrolled_date

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 Rule Expression Example
name__v Name Returns the Name of the Event Group @EventGroup.name__v
sequence__v Sequence Returns the Sequence number of the Event Group (for repeating Event Groups) @EventGroup.sequence__v

Event

The following fields on the Event (event__v) object are available for use in system references and variables:

Field Name Field Label Description Rule Expression Example
change_reason__v Change Reason Returns the Change Reason for the Event @Event.change_reason__v
did_not_occur__v Did Not Occur Returns true if the Event did not occur @Event.did_not_occur__v
event_date__v Event Date Returns the Event Date of the Event See notes
name__v Name Returns the Name of the Event @Event.name__v
sequence__v Sequence Returns the Sequence number of the Event (for Events within repeating Event Groups) @Event.sequence__v
Notes
  • Always define an Event when referencing Event Date. Do not use @Event.event_date__v. For fully-qualified identifiers use $EG.EVENT.event_date__v.

Form

The following fields on the Form (form__v) object are available for use in system references and variables:

Field Name Field Label Description Rule Expression Example
change_reason__v Change Reason Returns the Change Reason for the Form @Form.change_reason__v
form_status__v Form Status Returns the status of the Form. Options include: submitted__v, planned__v, blank__v, in_progress__v, in_progress_post_submit__v @Form.form_status__v
intentionally_left_blank__v Intentionally Left Blank Returns true if the Form was marked as Intentionally Left Blank @Form.intentionally_left_blank__v
name__v Name Returns the Name of the Form @Form.name__v
sequence__v Sequence Returns the Sequence number of the Form (for repeating Forms) @Form.sequence__v
submit_counter__v Submit Counter Returns the number of times that the Form has been submitted @Form.submit_counter__v
Notes
  • Use $EG.EVENT.FORM.intentionally_left_blank as an example format for fully-qualified identifiers.

  • In the 24R2 release, the label for the in_progress_post_submit__v form status changed from In Progress Post Submit to In Edit. The variable has not changed, and remains in_progress_post_submit__v.

Item

The following fields on the Item (item__v) object are available for use in system references and variables:

Field Name Field Label Description Rule Expression Example
change_reason__v Change Reason Returns the Change Reason for the Item @Item.change_reason__v
has_value_changed__v Has Value Changed Returns true if the value has changed since the last time the rule ran. This is only available when used in an @Form identifier for a Send Email rule @Item.has_value_changed__v
intentionally_left_blank__v Intentionally Left Blank Returns true if the Item was marked as Intentionally Left Blank @Item.intentionally_left_blank__v
lab_modifier__v Lab Modifier Returns the Lab Modifier for the Item @Item.lab_modifier__v
previous_submit_value__v Previous Submit Value Returns the value for the Item from the previous form submission @Item.previous_submit_value__v
translated_unit__v Translated Unit Returns the Name of the Standard Unit assigned to the Unit for a unit-type Item @Item.translated_unit__v
unit__v Unit Returns the Name of the Unit assigned to a unit-type Item @Item.unit__v
value__v Value Returns the Value for the Item @Item.value__v
value_normalized__v Value Normalized Returns the Value Normalized for the Item Item.value_normalized__v
value_translated__v Value Translated Translates the value to the Standard Unit Item.value_translated__v
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.

  • Use $EG.EVENT.FORM.IG.ITEM.change_reason__v as an example format for fully-qualified identifiers.

  • Mathematical functions can be used on Unit Item Types.

  • Vault automatically translates the value for identifiers that are unit items to the Standard Unit. For example, @Form.IG.UNITITEM.value_translated__v. You can use {{@Form.IG.UNITITEM.value__v }} to return the numerical value as entered with no unit.

Note that partially-defined paths are also supported in the rule expression. For example, @EventGroup.EVENT.field__v, @EventGroup.EVENT.FORM.field__v, @EventGroup.EVENT.FORM.IG.ITEM.field__v, @Event.FORM.field__v, @Event.FORM.IG.ITEM.field__v.

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:

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 or @ symbol 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
locked__v Locked Returns the Locked status of the Study
name__v Name Returns the Name of the Study
oid__v External ID Returns the External ID 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 Study Country of the associated Site
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
locked__v Locked Returns the Locked status of the Site
name__v Name Returns the Study Site Number of the Site
oid__v External ID Returns the External ID of the Site
principal_investigator__v Principal Investigator Returns the Principal Investigator for the Site
status__v Status Returns the 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)
current_sdv_plan__v Current SDV Plan Returns the SDV Review Plan currently assigned to the Subject (Casebook)
current_dmr_plan__v Current DMR Plan Returns the DMR Review Plan currently assigned to the Subject (Casebook)
subject_name__v Subject ID Returns the Subject ID for the casebook's Subject
var Variable Can be used as an identifier to represent Casebook Variables that have been defined for the study

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 Group
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
change_reason__v Change Reason Returns the Change Reason for the Event
did_not_occur__v Did Not Occur Returns true if the Event did not occur
event_date__v Event Date Returns the Event Date of the Event
name__v Name Returns the Name of the Event
sequence__v Sequence Returns the Sequence number of the Event (for Events within repeating Event Groups)
Notes
  • Always define an Event when referencing Event Date. Do not use @Event.event_date__v. For fully-qualified identifiers use $EG.EVENT.event_date__v.

Form

The following fields on the Form (form__v) object are available for use in system references and variables:

Field Name Field Label Description
change_reason__v Change Reason Returns the Change Reason for the Form
form_status__v Form Status Returns the status of the Form. Options include: submitted__v, planned__v, blank__v, in_progress__v, in_progress_post_submit__v
intentionally_left_blank__v Intentionally Left Blank Returns true if the Form was marked as Intentionally Left Blank
name__v Name Returns the Name of the Form
sequence__v Sequence Returns the Sequence number of the Form (for repeating Forms)
submit_counter__v Submit Counter Returns the number of times that the Form has been submitted
Notes
  • Use $EG.EVENT.FORM.intentionally_left_blank as an example format for fully-qualified identifiers.

  • In the 24R2 release, the label for the in_progress_post_submit__v form status changed from In Progress Post Submit to In Edit. The variable has not changed, and remains in_progress_post_submit__v.

Item

The following fields on the Item (item__v) object are available for use in system references and variables:

Field Name Field Label Description
change_reason__v Change Reason Returns the Change Reason for the Item
has_value_changed__v Has Value Changed Returns true if the value has changed since the last time the rule ran. This is only available when used in an @Form identifier for a Send Email rule
intentionally_left_blank__v Intentionally Left Blank Returns true if the Item was marked as Intentionally Left Blank
lab_modifier__v Lab Modifier Returns the Lab Modifier for the Item
previous_submit_value__v Previous Submit Value Returns the value for the Item from the previous form submission
translated_unit__v Translated Unit Returns the Name of the Standard Unit assigned to the Unit for a unit-type Item
unit__v Unit Returns the Name of the Unit assigned to a unit-type Item
value__v Value Returns the Value for the Item
value_normalized__v Value Normalized Returns the Value Normalized for the Item
value_translated__v Value Translated Translates the value to the Standard Unit
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.

  • Use $EG.EVENT.FORM.IG.ITEM.change_reason__v as an example format for fully-qualified identifiers.

  • Mathematical functions can be used on Unit Item Types.

  • Vault automatically translates the value for identifiers that are unit items to the Standard Unit. For example, @Form.IG.UNITITEM.value_translated__v. You can use {{@Form.IG.UNITITEM.value__v }} to return the numerical value as entered with no unit.

Examples

Note: The examples below use sample names of objects and records. To use these references, you must replace the names with values from your own study.

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).

This way of referencing objects also works with system variables.

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:

NUM1 + NUM2
Blanks “As zero” result “As null” result
Both 0 + 0 = 0 Null + Null = Null
NUM1 NUM1 + 0 = NUM1 NUM1 + Null = Null
NUM2 0 + NUM2 = NUM2 Null + NUM2 = Null

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:

  1. Verify that your Study does not contain any formulas using V1 of the formula language.
  2. Navigate to the Study Configuration object in Admin > Business Admin.
  3. Locate your Study and click to open it.
  4. Click Edit.
  5. In the Expression Engine Version field, enter 2.
  6. 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.

This method removes any formulas, including custom rules and derived column formulas, from your design.

  1. Export your study design. See details here.
  2. Create a new Study.
  3. Import your study design into the new Study. See details here.
  4. 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: