This reference guide includes a list of available operators and functions for use in Vault. Note that not all functions and operators are available for all areas of Vault using formulas.
Math Operators
+ (Add)
Description
Calculates the sum of two values
Use
value1 + value2
Data Types
- Number
- DateTime with Number (interprets number as days)
- Date with Number (interprets number as days)
- Date with Time
- Date with Interval
- DateTime with Interval
Example
times_in_review__c + 1- Increments a Times In Review field by one
created_date__v + 30- Adds 30 days to Created Date
Date(Year(Today()), Month(Today()), Day(Today())) + Time(12,0,0)- Returns the DateTime that represents today at noon
Today() + Months(1)- Returns the same day next month
– (Subtract)
Description
Calculates the difference between two values
Use
value1 - value2
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)
Example
suggested_retail_price__c - discount__c- Calculates the price after discount
created_date__v - 1- Calculates day before created date
completion_date__c - created_date__v- Calculates the difference between two DateTimes as a number of days, hours, and minutes
Today() - Years(1)- Returns today’s date from the previous year
* (Multiply)
Description
Multiplies two values
Use
value1 * value2
Data Types
- Number
Example
monthly_cost__c * 12- Returns the annual cost
/ (Divide)
Description
Divides one value by another
Use
value1 / value2
Data Types
- Number
Example
(measurement1__c + measurement2__c) / 2- Returns average between two measurements
^ (Exponent)
Description
Raises a number to the power of another number
Use
value1 ^ value2
Data Types
- Number
Example
100 ^ 2- Returns 1,000 by multiplying 100 by itself 2 times: 100 * 100
% (Remainder)
Description
Remainder from one value divided by another
Use
value1 % value2
Data Types
- Number
Example
"Weeks:" & Text(Floor(days_required__c / 5)) & "Days:" & Text(days_required__c % 5)- Returns text string with number of weeks and days required
() (Parenthesis)
Description
Specifies that the expressions within the open parenthesis and close parenthesis are evaluated first
Use
(expression1) expression2
Example
(measurement1__c + measurement2__c) / 2- Returns average between two measurements
Logical Operators
= (Equal)
Description
Evaluates if two values are equivalent
Use
value1 = value2
Data Types
- Number
- Text
- Date
- DateTime
- Date with DateTime (converts DateTime to Date)
- Yes/No
- Picklist (both picklists must be single-value)
- Picklist with Text
Example
Document.annotation_unresolved__v = 0- Returns true if there are no unresolved annotations
created_date__v = last_modified_date__v- Returns true if the object record has not been modified since creation (DateTimes are equivalent)
name__v = "text"- Returns true if the name value matches the “text” (text strings are equivalent)
picklist__c = "text__c"- Returns true if the picklist includes an item that matches “text__c” (picklist value matches text string)
!= (Does Not Equal)
Description
Evaluates if two values are not equivalent
Use
value1 != value2
Data Types
- Number
- Text
- Date
- DateTime
- Date with DateTime (converts DateTime to Date)
- Yes/No
- Picklist (both picklists must be single-value)
- Picklist with Text
Example
amount1__c != amount2__c- Returns true if numbers are not equivalent
created_date__v != last_modified_date__v- Returns true if the object record has been modified since creation (DateTimes are not equivalent)
name__v != "text"- Returns true if the name value does not match “text” (text strings are not equivalent)
picklist__c != "text__c"- Returns true if the picklist does not include an item that matches “text__c” (picklist value does not match text string)
< (Less Than)
Description
Evaluates if the first value is less than the second value
Use
value1 < value2
Data Types
- Number
- Date
- DateTime
- Date with DateTime (converts DateTime to Date)
Example
measurement1__c < measurement2__c- Returns true if Measurement 1 is smaller than Measurement 2
If(due_date__c < Today(), "Late", "On-Time")- Returns “Late” if the Due Date is before the current date
(submission_date__c + 15) < approval_date__c- Returns true if the Approval Date occurs at least fifteen days after the Submission Date
<= (Less Than or Equal To)
Description
Evaluates if a value is less than or equal to another value
Use
value1 <= value2
Data Types
- Number
- Date
- DateTime
- Date with DateTime (converts DateTime to Date)
Example
measurement1__c <= measurement2__c- Returns true if Measurement 1 is less than or equal to Measurement 2
If(due_date__c <= Today(), "Due", "On-Time")- Returns “Due” if Due Date is the current date or earlier
> (Greater Than)
Description
Evaluates if the first value is greater than the second value
Use
value1 > value2
Data Types
- Number
- Date
- DateTime
- Date with DateTime (converts DateTime to Date)
Example
measurement1__c > measurement2__c- Returns true if Measurement 1 is larger than Measurement 2
If(due_date__c > Today(), "On-Time", "Late")- Returns “On-Time” if the Due Date is after the current date, otherwise returns “Late”
approval_date__c > (submission_date__c + 15)- Returns true if the Approval Date occurs at least fifteen days after the Submission Date
>= (Greater Than or Equal To)
Description
Evaluates if a value is greater than or equal to another value
Use
value1 >= value2
Data Types
- Number
- Date
- DateTime
- Date with DateTime (converts DateTime to Date)
Example
measurement1__c >= measurement2__c- Returns true if Measurement 1 is greater than or equal to Measurement 2
If(due_date__c >= Today(), "On-Time", "Due")- Returns “On-Time” if the Due Date is the current date or later, otherwise returns “Due”
&& (And)
Description
Evaluates if two values or expressions are both true
Use
(expression1) && (expression2)
Users may use And() in place of &&.
Example
(count__c >= expected_count__c) && (Today() < expiration_date__c)- Returns true if Count is greater than or equal to the Expected Count and the current date is before the Expiration Date
|| (Or)
Description
Evaluates if at least one of two values or expressions is true
Use
(expression1) || (expression2)
| Users may use Or() in place of | . |
Example
(count__c >= expected_count__c) || (Today() < expiration_date__c)- Returns true if Count is greater than or equal to the Expected Count or the current date is before the Expiration Date; also returns true if both expressions are true
Text Operators
& (Concatenate)
Description
Connects two or more text strings
Use
text1 & text2
Users may use Concat() in place of &
Example
"Study" & name__v- Returns the text string “Study” combined with the name of the object record
Note: The concatenate function does not accept Long Text or Rich Text fields.
Date & DateTime Functions
AddMonths
Description
Returns a date that is the indicated number of months before or after a specified date. If the specified date is the last date of the month, Vault returns the last date of the resulting month if the resulting month has fewer days.
Use
AddMonths(date, number) OR AddMonths(datetime, number)
Example
AddMonths(created_date__v, 6)- Returns the date 6 months after the Created Date
AddMonths(approval_date__c, 4)- Returns the date and time 4 months after the Approval Date
Date
Description
Returns a date value when given year, month, and day values
Use
Date(year, month, day)
Example
Date(2018, 3, 14)- Returns the date 2018-03-14
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 DateTime that represents today at noon
DateValue
Description
Returns the date portion of a DateTime value. This function also accepts a second optional parameter for timezone. The date returned is based on the indicated timezone.
The timezone parameter can be a picklist, text field, or the text literal for the timezone, such as “America/Los_Angeles” or “Asia/Shanghai”.
Use
DateValue(DateTime) or DateValue(DateTime, Timezone)
Example
DateValue(audit_end_date_and_time__c)- Returns the date that the audit ended, without time
Example
DateValue(audit_end_date_and_time__c, @User.timezone__sys)- Returns the date that the audit ended without time in the user’s timezone
DateTimeValue
Description
Returns the date and time of a DateTime value. This function accepts a date, DateTimed, and string value in the format of a date or DateTime.
Use
DateTimeValue(date/datetime/string, timezone)
This function also accepts an optional timezone parameter. If a timezone is not specified, Vault uses GMT for the timezone. If only a date is provided, Vault returns 00:00:00 as the time.
The timezone parameter can be a picklist, text field, or the text literal for the timezone, such as “America/Los_Angeles” or “Asia/Shanghai”.
Example
DateTimeValue("2024-01-31 17:00:00")- Returns a DateTime with the input value
DateTimeValue(approval_date__c)- Returns the approval date at 12:00 AM in GMT
DateTimeValue(approval_date__c 10:00:00 @Vault.timezone__sys)- Returns the approval date at 10:00 AM in the user’s Vault’s timezone
Day
Description
Returns the day of the month
Use
Day(date)
Example
Day(audit_end_date__c)- Returns the day of the month from the Audit End Date
DayOfYear
Description
Returns the day of the calendar year, from 1 to 366, based on the Vault time zone
Use
DayOfYear(date)
Example
DayOfYear(audit_end_date__c)- Returns the day of the year for the Audit End Date
Days
Description
Returns the specified number of days as an interval
Use
Days(number)
Example
audit_start_date__c + Days(10)- Returns the date 10 days after the Audit Start Date
Hour
Description
Returns the hour value from a DateTime in terms of 0 to 23
Hour is returned in terms of UTC. However, if used to construct a DateTime, Vault converts the DateTime to the active user’s local time.
Use
Hour(dateTime) or Hour()
Example
Hour(created_date__v)- Returns the hour from the Created Date
Hour()- Returns the current hour in the Vault time zone
IsoWeek
Description
Returns the ISO 8601 week number (from 1 to 53) for the given date, where the first week starts on a Monday, based on the Vault time zone
Use
IsoWeek(date)
Example
IsoWeek(created_date__v)- Returns the ISO 8601 week number from the Created Date
IsoYear
Description
Returns the ISO 8601 week-numbering year for the given date, where the first day is a Monday, based on the Vault time zone
Use
IsoYear(date)
Example
IsoYear(created_date__v)- Returns the ISO 8601 week-numbering year for the Created Date
Minute
Description
Returns the minute value from a DateTime in terms of 0 to 59
Minute is returned in terms of UTC. However, if used to construct a DateTime, that DateTime is converted to the active user’s local time.
Use
Minute(DateTime) or Minute()
Example
Minute(created_date__v)- Returns the minute from the Created Date
Minute()- Returns the current minute in the Vault time zone
Month
Description
Returns the month from a date or DateTime as a number from 1-12
Use
Month(date), Month(DateTime)
Example
Month(created_date__v)- Returns the month from the Created Date
Months
Description
Returns the specified number of months as an interval
Use
Months(number)
Example
audit_start_date__c + Months(1)- Returns the date 1 month after the Audit Start Date
NetWorkdays
Description
Returns the number of workdays between two (2) dates/datetimes. This function also accepts optional parameters for weekends and holiday schedules. Admins can configure holidays from Business Admin > Objects > Holiday Schedules or download and deploy a VPK with pre-configured holidays.
Use
NetWorkdays(start_date/datetime, end_date/datetime, weekend_number, holiday_schedule)
Example
NetWorkdays(created_date__v, approval_date__c, 1, "United States")- Returns the number of workdays between the dates with United States holidays removed
Now
Description
Returns the current date and time
Returns the DateTime for the active user.
Use
Now()
Example
Now() - created_date__v- Returns the number of days since Created Date
Second
Description
Returns the second value from a DateTime in terms of 0-59
Second is returned in terms of UTC. However, if used to construct a DateTime, that DateTime is converted to the active user’s local time.
Use
Second(date), Second()
Example
Second(created_date__v)- Returns the second value from Created Date
Second()- Returns the current second in the Vault time zone
StartOfDay
Description
Returns the DateTime which is the beginning of the given day in the given timezone
The timezone parameter can be a picklist, text field, or the text literal for the timezone, such as “America/Los_Angeles” or “Asia/Shanghai”.
Use
StartOfDay(date, timezone)
Example
StartOfDay(assigned_date__v, "Europe/Oslo")- If
assigned_date__v= July 1, 2020, returns the UTC DateTime which corresponds to 2019/07/01 00:00 in Oslo time.
Time
Description
Returns the time based on the hour, minute, and second values entered
Use
Time(hour, minute, second)
Example
Time(12,0,0)- Returns the time representing noon
Time(Hour(Now()), Minute(Now()), Second(Now())) - Time (12,30,0)- Calculates the difference between 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
TimeNow
Description
Returns the current time in the Vault time zone. You can use this function instead of Now to track time without a date.
Use
TimeNow()
Example
TimeNow()- Returns the current time
TimeValue
Description
Returns the local time value without the date
Use
TimeValue(datetime)
Example
TimeValue("17:30:45.235")- Returns 5:30 PM
TimeValue(closed_date__v)- Returns a time value based on the DateTime value of the
closed_date__vfield
Today
Description
Returns the current date in your Vault’s base timezone; with a variable, can return the current date in the specified timezone
The timezone parameter can be a picklist, text field, or the text literal for the timezone, such as “America/Los_Angeles” or “Asia/Shanghai”.
Use
Today(), Today(timezone)
Example
Today() = due_date__c- Returns true if Due Date is equal to the current date in your Vault’s base timezone
Today("Europe/Oslo") = due_date__c- Returns true if Due Date is equal to the current date in the Oslo timezone
Today (User)
Description
Returns the current date in the active user’s specified timezone.
When used in a lifecycle or workflow, “user” refers to the user who initiates the action. For example, the workflow owner is the “user” for an action that completes immediately after a workflow’s Start step, while the “user” for an action that completes immediately after a Workflow Task step is the last user to complete the task.
Use
Today("user")
Example
Today("user") = due_date__c- Returns true if Due Date is equal to the current date in the active user’s timezone
UnixTimeStamp
Description
Returns the number of seconds since 1 January 1970 for the given date, or the number of seconds in a day for a time
Use
UnixTimeStamp(date) OR UnixTimeStamp(time)
Example
UnixTimeStamp(created_date__v)- Returns the Unix timestamp from the Created Date
Weekday
Description
Returns the day of the week from a Date or DateTime as a number from 1-7
Use
Weekday(date), Weekday(datetime)
Example
If((Weekday(due_date__v) = 1) || (Weekday(due_date__v) = 7)), "Weekend Due Date", "Not a weekend due date")- Returns “Weekend Due Date” if the day of the week of the due date is a Sunday or Saturday, otherwise returns “Not a weekend due date”
Workday
Description
Returns a date N 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 > Objects >Holiday Schedules or download and deploy a VPK with pre-configured holidays.
Use
Workday(start_date/datetime, number_of_days, weekend_number, holiday_schedule)
Example
Workday(created_date__v, N)- Returns a date that is N days ahead of the Created Date excluding Saturday and Sundays
Workday(created_date__v, N, 1, "United States")- Returns a date that is N days ahead of the Created Date with United States holidays removed
Year
Description
Returns the year from the date
Use
Year(date) or Year(DateTime)
Example
Date(Year(Today()) + 1, Month(Today()), Day(Today()))- Returns the same day as the current date for the following year
Years
Description
Returns the specified number of years as an interval
Use
Years(number)
Example
Today() + Years(1)- Returns the same day as the current date for the following year
Deprecated Functions
The following functions have been deprecated.
DateAdd: Use the + operator.DateTimeAdd: Use the + operator.DateDiff: Use the – operator.DateTimeDiff: Use the – operator.
Note: Deprecated functions continue to work and users can utilize them by typing the function in, but they do not appear in the formula creation UI.
Math Functions
Abs
Description
Calculates the absolute value of a number
Use
Abs(number)
Example
Abs(height_1__c - height_2__c)- Returns the difference between Height 1 and Height 2 as a positive value regardless of which measurement is greater
Average
Description
Calculates the average of a set of numbers
Use
Average(number1, number2, ...)
Example
Average(1.5, 2, 3, 4, 5.0)- Returns 3.1
Ceiling
Description
Returns the next integer greater than the value
Use
Ceiling(number)
Example
Ceiling(14.2)- Returns 15 by rounding up to the nearest integer
Ceiling(-14.2)- Returns -14 by rounding up to the nearest integer
CountA
Description
Returns the number of populated values
Use
CountA(value1, value2, …)
Example
CountA(description1__c, description2__c, description3__c)- Returns the number of text fields that are not blank
CountA(completion_date__c, created_date__c, closed_date__c)- Returns the number of Date fields that are not blank
CurrencyRate
Description
Returns the conversion rate to the corporate currency for the given currency. If the currency is invalid, the function returns 1.0.
Use
CurrencyRate(currency_ISO_code)
Example
CurrencyRate("EUR")- Returns the conversion rate to your Vault’s corporate currency for EUR.
Floor
Description
Returns the next integer less than the value
Use
Floor(number)
Example
Floor(14.2)- Returns 14 by rounding down to the nearest integer
Floor(-14.2)- Returns -15 by rounding down to the nearest integer
FromUnixTime
Description
Returns the DateTime representing the given number as seconds elapsed since 1 January 1970
Use
FromUnixTime(number)
Example
FromUnixTime(time_in_seconds__v)- Returns the DateTime in your local timezone resulting from the value of Time in Seconds elapsed since 1 January 1970
Max
Description
Returns the largest value from the set
Use
Max(value1, value2, …)
Data Types
- Number
- Date
- DateTime
You cannot mix data types in a single Max() request. If you need to mix Date and DateTime, we suggest converting DateTimes to Dates using DateValue.
Example
Max(measurement1__c, measurement2__c, measurement3__c)- Returns the greatest measurement number
Max((units__c * price__c), min_value__c)- Returns the greater of Units multiplied by Price or Minimum Value
Max(approval_date__c, today(), completed_date__v- Returns the greater of the given dates, which is the most recent (youngest) date
Median
Description
Returns the median of the given numbers
Use
Median(number1, number2, …)
Example
Median(23, 5, 75, 17, 9)- Returns 17
Median(3, 12, 6, 43)- Returns 9
Min
Description
Returns the lowest value from the set
Use
Min(value1, value2, …)
Data Types
- Number
- Date
- DateTime
You cannot mix data types in a single Min() request. If you need to mix Date and DateTime, we suggest converting DateTimes to Dates using DateValue.
Example
Min((units__c * price__c), max_value__c)- Returns the lesser of Units multiplied by Price and Maximum Value
Min(approval_date__c, today(), completed_date__v)- Returns the lesser of the given dates, which is the least recent (oldest) date
Mode
Description
Returns the mode of the given numbers; returns the smallest number if two modes are found
Use
Mode(number1, number2, …)
Example
Mode(15, 16, 36, 16, 16)- Returns 16
Mode(4, 5, 7, 8)- Returns
NULL
Pi
Description
Returns the number pi
Use
Pi()
Example
Pi()- Returns the number pi to 255 places
PicklistCount
Description
Returns the number of selected values in a multi-value picklist
Use
PicklistCount(picklist_field)
Example
Picklist(authentication_type__v)- Returns the number of values in the Authentication Type picklist
Power
Description
Given two numbers, multiplies the first number by itself as many times as specified in the second number
Use
Power(number, number)
Example
Power(5, 3)- Returns 125 by multiplying 5 by itself 3 times: 5 * 5 * 5
Power(8, 1/3)- Returns 2 by multiplying 8 by itself 1/3 times: 8 * ⅓
Power(9, 0)- Returns 1; if the second argument is zero, then the result is always 1
Rand
Description
Returns an evenly distributed random real number greater than or equal to 0 and less than 1
Use
Rand()
Example
Rand()- Returns a random number greater than or equal to 0 and less than 1
Rand() * 10- Returns a random number greater than or equal to 0 and less than 10
Round
Description
Rounds the number to the defined number of decimal places
Use
Round(number, number)
Example
Round(5.5, 0)- Returns “6”, 5.5 rounded up with no decimal value
Round(5.54, 1)- Returns “5.5”, 5.54 rounded down with one decimal place
Round(-5.5, 0)- Returns “-6”, -5.5 rounded to the nearest number with no decimal value
Round (Significant)
Description
Rounds to the defined number of significant figures
Use
Round(number, number_of_digits, "significant") OR Round(number, number_of_digits, "significant-astm")
Example
Round(0.445, 2, "significant")- Returns “0.45”
Round(0.445, 2, "significant-astm")- Returns “0.44”
Sqrt
Description
Returns the square root of a number
Use
Sqrt(number)
Example
Sqrt(25)- Returns “5”, the square root of 25
StDevP
Description
Returns the population standard deviation for the given numbers
Use
StDevP(number1, number2, …)
Example
StDevP(45, 31, 36, 88)- Returns 22.50
StDevS
Description
Returns the sample standard deviation for the given numbers
Use
StDevS(number1, number2, …)
Example
StDevS(10, 12, 18, 13)- Returns 3.40
Sum
Description
Adds the given numbers together
Use
Sum(number1, number2, ...)
Example
Sum(1.5, 2, 3, 4, 5.0)- Returns 15.5
Trunc
Description
Truncates a number to an integer or the specified number of decimal places
Use
Trunc(number, digits)
The digits parameter is optional and specifies the number of decimal places to include. If you pass Trunc functions without this parameter, Vault truncates the number with zero decimal places.
Example
Trunc(7.9)- Returns 7
Trunc(5.62, 1)- Returns 5.6
Value
Description
Returns a text string as a number; if the text does not resolve to a number, the function will result in an error
Use
Value(text)
Example
Value(Right("S1234",4)- Returns the number “1234”
Deprecated Functions
NumberEquals: Use the = operator.
Note: Deprecated functions continue to work and users can utilize them by typing the function in, but they do not appear in the formula creation UI.
Logical Functions
And
Description
Returns true when all expressions are true
Users may use && in place of And().
Use
And(expression1, expression2, …)
Example
And(due_date__v = today(), status__v != "Completed")- Returns true if the object record’s due date is today and its Status is not Completed
BlankValue
Description
Returns the value of an expression, or returns a substitute expression value if it is empty
Use
BlankValue(expression, substitute_expression)
Example
BlankValue(batch__v, "Undesignated")- Returns the Batch field value if present, returns the word Undesignated if empty
Case
Description
Compares the value of the expression with each case value and returns the paired result; if no values match, returns the last argument
The expression argument accepts the following data types: Number, Text, Long Text, Rich Text, Yes/No, Picklist. When working with a picklist field, the field returns the value names (study123__c) instead of the value labels (Study123).
Use
Case(expression1, value1, result1, value2, result2, else_result)
Example
Case(WeekDay(Today()), 1, "Sunday", 2, "Monday", 3, "Tuesday", 4, "Wednesday", 5, "Thursday", 6, "Friday", 7, "Saturday", "None")- Returns the name of the current day
Case(picklist__c, "sunday__c", 1, "monday__c", 2, "tuesday__c", 3, "wednesday__c", 4, "thursday__c", 5, "friday__c", 6, "saturday__c", 7, 0)- Returns the number of the day selected in the picklist using the picklist value name (sunday__c) as opposed to its label (Sunday)
If
Description
Determines if expressions are true or false; returns a given value if true and another value if false
Use
If(expression, value_if_true, value_if_false)
Example
If(Or(IsBlank(measurement1__v), IsBlank(completed_date__v)), "Incomplete", "Complete")- Returns “Complete” if both Measurement 1 and Completed Date are not blank
IsAppEnabled
Description
Returns true when the application is enabled in your Vault and returns false if the application is disabled or an empty value is provided. The application name must match the appropriate application string.
Use
IsAppEnabled(application name)
Example
IsAppEnabled("quality_qms__v")- Returns true if Quality: QMS is enabled in your Vault
IsAppEnabled("rim_reg__v")- Returns true if RIM: Registrations is enabled in your Vault
Includes
Description
Returns true when the multi-value picklist contains names that match the defined string or single-value picklist
Use
Includes(multi-value picklist, string) or Includes(multi-value picklist, single-value picklist)
Example
Includes(colors__c, 'red__c')- Returns true if “Red” is one of the values selected in the multi-value picklist “colors__c”
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__c”
IsBlank
Description
Returns true when the value is blank
Use
IsBlank(expression)
Example
If(Or(IsBlank(measurement1__v), IsBlank(completed_date__v)), "Incomplete", "Complete")- Returns “Complete” if both Measurement 1 and Completed Date are not blank
IsChanged
Description
Compares a field value to the previous value and returns true if the values are different
Use
IsChanged(field)
Note: The IsChanged() function is currently only available on object field validation rules.
Example
Not(And(IsChanged(comments__c), Text(state__v) = "Finalized"))- Returns false when a user attempts to edit the comment field and the record is currently in a state with the label “Finalized”
IsNew
Description
Returns true if the formula is executed during record creation. If an existing record is being updated, the function returns false.
Use
IsNew()
Note: The IsNew() function is currently only available on object field validation rules.
Example
AND(ISNEW(), completion_date__v < TODAY())- When included in a validation rule, this expression prevents users from creating a record with a Completion Date in the future.
IsNumber
Description
Returns true when the value is a number
Use
IsNumber(text)
Example
If(IsNumber(measurement1__c),measurement1__c/100,0)- Returns Measurement 1 divided by 100 if Measurement 1 is a number; otherwise, returns “0”
Not
Description
Returns true when the expression is false and false when the expression is true
Use
Not(expression)
Example
Not(isBlank(due_date__c))- Returns true if the Due Date field is populated
Or
Description
Evaluates if at least one of two values or expressions is true
Use
(expression1) || (expression2)
Users may use || operator in place of Or().
Example
Or((count__c >= expected_count__c),(Today() < expiration_date__c))- Returns true if Count is greater than or equal to the Expected Count or the current date is before the Expiration Date; also returns true if both expressions are true
Or(due_date__v = Today(), due_date__v = Today() + 1)- Returns true if the object record’s due date is today or tomorrow
PriorValue
Description
Returns the previous field value
Use
PriorValue(field)
Note: The PriorValue() function is currently only available on validation rules.
Example
If(PriorValue(completed_date__v) > completed_date__v, false, true)- Returns false if the previous value for a completed date is after the current value for completed date
Regex
Description
Compares a text string or field to a regular expression and returns true if there is a match. A regular expression is a string that specifies a search pattern.
Regular expression syntax is based on Java Platform SE 6.
Note: You must escape \ characters in regular expressions as \\.
Use
Regex(text, regex_text)
Note: The Regex() function is currently only available on validation rules.
Example
Regex(email__v, "^\\S+@\\S+\\.\\S+$")- Ensures that text entered into an email field follow a basic pattern expected for email addresses
Vlookup
Description
Searches an object for a record where the specified name field matches the specified lookup value and returns another field on that record. This is similar to the VLOOKUP() function in Microsoft Excel.
Use
Vlookup(field_to_return, field_on_lookup_object, lookup_value)
Note: The Vlookup function is currently only available on validation rules.
Example
IsBlank(VLookUp($ObjectField.question__v.answer__v, $ObjectField.question__v.name__v, question_number__v) = answer__v- Returns true if the user entered a value in the answer field for the current record that matches the answer field on a Question object
UserHasAssignedTask
Description
Returns true if the current user has any assigned workflow task on the current record if no parameter is provided. If a parameter is used, returns true for the task named in the parameter if the user is assigned to it. Returns false if the user has an available, completed, or canceled task.
Use
UserHasAssignedTask((optional) taskName)
Example
UserHasAssignedTask("mrw_multitask_wf_obj__c.task_2__c")
Note: The optional taskName parameter accepts only a literal string in the form of workflowName.taskName. Any other format or data type is considered invalid.
DisplayMode
Description
Returns the current display mode of a record detail page as a text value: VIEW, CREATE, EDIT, or COPY.
Use
DisplayMode()
Example
DisplayMode()='VIEW'
Text Functions
Ascii
Description
Returns the first character of a given string’s code point as a number
Use
Ascii(text)
Example
Ascii("hello")- Returns 104, the ASCII code for the letter “h”
Begins
Description
Returns true if text begins with the specified characters, otherwise returns false
Use
Begins(text, compare_text)
This function is case sensitive.
Example
Begins(product_type__c, "Per")- Returns true if the Product Type begins with the string “Per” and returns false if not
Concat
Description
Connects two or more text strings
Use
Concat(text1, text2, …)
Users may use & in place of Concat()
Example
Concat("Study", name__v)- Returns the text string “Study” combined with the name of the object record, for example, “Study123”
Note: The Concat() function does not accept Long Text or Rich Text fields.
Contains
Description
Compares two text arguments, returns true if the first argument contains the second argument and returns false if not
Use
Contains(text, compare_text)
This function is case sensitive.
Example
Contains(product_type__c, "part")- Returns true if the Product Type contains the string “part” and returns false if not
Find
Description
Returns the position of a string within a string of text
Use
Find(search_text, text, [,start_num])
The [,start_num] parameter is optional and specifies the character at which Vault starts the search. If you pass Find functions without this parameter, Vault searches from the beginning of the string.
Example
Find(" ", "4280 Hacienda Dr, Pleasanton, CA")- Returns 5, the position of the first space in the address
ID
Description
Returns the record ID as text of an Object Reference value
Use
ID(text/object_record)
Example
ID(country__c)- Returns the ID of the object record referenced in the
country__cfield
Icon
Description
Displays an icon based on the formula evaluation
Use
icon(name, color, text)
The Text argument is optional and provides alternate text for users. See Vault Supported Icons for more information.
Example
If(completeness_v = 'complete_v', Icon("circle", "#00C345", "Complete"), Icon("circle", "#ff0000", "Incomplete")):- Displays a status icon based on the Completeness picklist value
InitCap
Description
Returns text as lowercase, with the first character of each word made uppercase
Use
InitCap(text)
Example
InitCap("Cholecap works well"):- Returns the string “Cholecap Works Well”
Left
Description
Returns the specified number of characters from the beginning of a text string
Use
Left(text, position)
Note: The Left() function can accept Long Text and Rich Text fields, returning up to 1,500 characters. Rich Text fields will return the first 1,500 characters of their plain text value.
Example
Left(name__v, 5) & " - " & abbreviation__c- Returns the first five characters of a name and it’s abbreviation concatenated, for example, “Chole – CC”
Length
Description
Returns the number of characters in a specified text string
Use
Length(text)
Example
Length(name__v)- Returns the number of characters in the Name field
Lower
Description
Converts all letters in the specified text string to lowercase
Use
Lower(text)
Example
Lower("Company A")- Returns the text string “company a”
Middle
Description
Returns the character specified in the start position and the specified number of following characters from within a text string
Use
Middle(text, start, number)
Example
Middle("4280 Hacienda Dr, Pleasanton, CA", 6, 8)- Returns the text string “Hacienda”
Right
Description
Returns the specified number of characters from the end of a text string
Use
Right(text, number)
Example
Value(Right("S1234",4))- Returns the number “1234”
Substitute
Description
Substitutes new text for old text in a text string
Use
Substitute(text, old_text, new_text)
Example
Substitute("Total cost of ownership: $1,000", "$", "￡")- Turns the same text string with the dollar sign replaced by a pound sign
Text
Description
Converts a value to text based on a specified format
See Date Formats and Number Formats for more information.
Use
Text(date/datetime/number/text, format) or Text(lifecycle_state/picklist_value/object_record)
When converting a number to text, the format argument is optional. You must enclose the specified format in double quotes (“). If you pass Text() functions without a specified format, Vault preserves the current number of decimal places defined. For operands with different numbers of decimal places, Vault preserves the greatest number of decimal places. For example, Text(3.14+2.345) returns 5.485.
You can add spaces or characters to text strings using the optional format argument. You must enclose the specified format in double quotes (“). If you pass a text string through the function without a specified format, Vault returns the original text string. A capital “A” is used to represent each character passed to the function. Double backslashes (\\) can be used to escape the character “A” if the “A” is not meant to represent a character in the passed text string.
Note: We do not recommend using Text(picklist_value) when comparing two picklists. Doing so will compare the localized picklist option label, rather than the picklist option itself. Instead, we recommend using the picklist option name, for example, “red__c” where “red__c” is the public label.
Example
"Today is" & Text(Today(), "dddd")- Returns the text string “Today is” followed by the full name of the day of the week, for example, “Today is Monday”
Text(state__v)- Returns the text string “Planned”, which is the lifecycle state label
Text(Document.major_version_number__v, "###") & "." & Text(Document.minor_version_number__v, "###")- Returns the text string “#.#”, where # is the document’s major and minor version number, for example, “12.2”
Text(region__c)- Returns the name of the object record referenced in the
region__cfield Text("18006895000", "A-AAA-AAA-AAAA")- Returns “1-800-689-5000”
Text(name__v & "Study", "A AAAAA \\A")- Returns “Cholecap Study A” if the name of the object record is “Cholecap”
Trim
Description
Removes the spaces and tabs from the beginning and end of a text string
Use
Trim(text)
Example
Trim(" Phase III ")- Returns the text string “Phase III” by removing the space from the beginning of the string
Define
Description
Replaces long blocks of text with a shorter set of characters.
Use
#Define(text)
Example
- Expression before:
if(today() > join_ref_1_v.workflow.workflow_dueDate_v, "Late", "On-Time")- Returns the text string “Phase III” by removing the space from the beginning of the string
Expression after:
define wf_dd join_ref_1_v.workflow.workflow_dueDate_v if(today() > wf_dd, "Late", "On-Time")
This example defines which blocks of text should be replaced by longer blocks of text.
Upper
Description
Converts all letters in the specified text string to uppercase
Use
Upper(text)
Example
Upper("Company A")- Returns the text string “COMPANY A”
Other Functions
Hyperlink
Description
Displays text as a clickable link
Use
Hyperlink(href, label, target, connection)
You must use this function with a formula field that uses Link return type.
Arguments
- href: Clickable URL for the link; you can use text strings, other functions, field references, and expressions that return a string. When using literal text strings, you must enclose them in single quotes (“).
- label: Text that appears as a link in the UI; you can use text strings, other functions, field references, and expressions that return a string within this argument. When using literal text strings, you must enclose them in single quotes (”).
- target: Determines whether the link opens in the current tab or in a new browser tab/window; accepts ‘new_window’ and ‘same_window’. You must enclose attributes in single quotes (”).
- Connection: (Optional) Populates another Vault’s DNS within the URL utilizing a configured Connection object record; format as @Connection.{connection API name}, for example, @Connection.veepharm_v2v. See Creating & Managing Connections for more information.
Example
Hyperlink('https://veepharm.com', 'VeePharm Site', 'new_window')- This example is a simple navigation to a specific URL. The field would display “VeePharm Site” and would navigate to the provided URL. Rather than opening in the same tab or window, this site would open in a new tab or window.
Hyperlink(Concat("https://veepharm.theorgwiki.com/employees/", first_name__sys, "_", last_name__sys), Concat(first_name__sys, " ", last_name__sys), 'new_window')- This example could be used with the User object record to return to a user’s company profile on OrgWiki. The formula displays a clickable link labeled with the user’s first and last name and navigates to an OrgWiki profile page for the user.
Hyperlink(Concat("/ui/#t/0TB00000000N04/", related_record_id__c), related_record_label__c, 'same_window', @Connection.veepharm_v2v)- This example uses a Vault to Vault Connection record with the API name “veepharm_v2v”. The href argument, together with the connection argument, navigates the user to a specific record within a connected Vault. Because this example uses a Connection record, it only includes the portion of the URL after the DNS in the href argument.
RecordByLabel
Description
Returns object references (earliest if multiple) for the specified object label
Use
RecordByLabel()
Example
RecordByLabel ("Cholecap")- Returns the object reference for the Cholecap product
State
Description
Returns the name of the object or document lifecycle state
Use
state__v
Example
state__v = "planned_state__v"- Returns true if the lifecycle state name value is
planned_state__v
Urlencode
Description
Converts characters in a text string to a format that can be transmitted through a URL, for example, by changing a space to +
Use
Urlencode(text)
Example
Hyperlink(Concat("https://www.google.com/search?q=", Urlencode(claim_text__c)), 'Search', 'new_window')- This example performs a Google search for the text in the Claim Text field. This field may have spaces and other non-URL encoded characters. The formula uses Urlencode() to properly encode the string.
Date Formats
| Format | Example | Description |
| 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 | Day of month, month, and year, separated by hyphens |
| yyyymmdd | 20170331 | Day of month, month, and year, no separation |
| dd.mmm.yyyy | 31.Mar.2017 | Day of month, 3-letter month, and year, separated by periods |
| yyyy-mm-dd | 2017-03-31 | Year, month, and day of month, separated by hyphens |
| Mmmm yyyy | March 2017 | Full month and full year |
| dddd dd/mm/yy | Thursday 31/03/17 | Full day of week with day of month, month, and 2-digit year separated by forward slashes |
Number Formats
| Format | Example | Description |
| 0 | 1 | Digit without decimal |
| # | 1 | Digit without decimal (zero shows as absent) |
| 0.00 | 1.20 | Rounded up to two decimal places |
| #.## | 1.2 | Rounded up to two decimal places (zero shows as absent) |
| $# | $10 | Currency form (different currencies can be used) |
| #,### | 10,000 | Comma up to three digits from the left (multiple commas can be used) |
| #,###.## | 10,000.12 | Comma up to three digits from the left, rounded up to two decimal places |
| -# | -10 | Minus icon before the digits |
| % | %1000 | Multiply by 100 and show as percentage, with percentage first |
| #% | 1000% | Multiply by 100 and show as percentage, with digits first |
| #,###E0 | 1.234E3 | Separate mantissa and exponent in scientific notation |
System Variables
System variables are dynamic objects that store a value which Vault can then reference. These are dynamic in the sense that their value changes depending on the context in which you use them. For example, @User points to the profile of the active user and allows access to field values like Email Address.
Note: Lifecycle and workflow configurations cannot use the @User system variable effectively because actions in this context use the System User. However, you can use @TaskOwner and @WorkflowOwner in these configurations.
Picklist Value Expressions
You can use an expression to reference a picklist value in a formula.
Use
Picklist.<picklist name>.<picklist value name>
Examples
Picklist.impact__c.high__c- Returns the “High” value in the “Impact” picklist.
if(object_type__v = "Action Plan", Picklist.ap_securityc.analysis_and_action_planc, Picklist.ap_securityc.action_plan__c)- Returns the “Analysis & Action Plan” value from the “Action Plan Security” picklist if the object type is “Action Plan”; otherwise, returns the “Action Plan” value from the “Action Plan Security” picklist.
Format Masks
You can use all Vault operators and functions to create a Format Mask expression as long as the expression resolves to a text string. In other words, if you use conditional functions such as If or Case, all outputs must be a format mask wrapped in quotation marks.
Format mask expressions also support the token this, which returns the value of the current field.
Example
If(Length(Text(this))<=7,"###-####","###-###-####")- With input “1234567”, this expression returns “###-####” as the format mask, which formats the input to “123-4567”. With input “5551234567”, this expression returns “###-###-####” as the format mask, which formats the input to “555-123-4567”.
Format Mask Functions
Percent
Description
Formats user’s input as a percentage when used with a percent format mask
Use
Percent(percent_format_mask)
Note: The Percent() function is only available on format mask expressions for Number fields.
Example
Percent("0.00%")- With input “38.5”, the value is treated as a percentage instead of a decimal. Upon saving, Vault stores a value of 0.385, while displaying “38.50%” in the UI.
Percent(If(closed__v=true,"0.0%","0.000%"))- With input “67.803”, the value is treated as a percentage instead of a decimal regardless of whether
closed__vis true or false. Upon saving, Vault stores a value of 0.67803, and displays “67.8%” or “67.803%”, depending on the value ofclosed__v.
EmailFormat
Description
Formats the user’s input as a hyperlink when used with an Email format mask.
Use
EmailFormat(text)
Example
EmailFormat(john.smith@company.com)- The john.smith@company.com email address is formatted as a link after the user inputs it in a Text field. When a user clicks the link, Vault opens a draft message in the user’s default email client with john.smith@company.com populated in the To field. Vault does not check if the input value is a valid email address. However, you can use the
Regex()logical function to validate the email address.
Note: The EmailFormat() function is only available on format mask expressions for Text fields. In addition, you cannot enclose EmailFormat() within another function, such as a Text or Logical function. For example, If (Contains (this, "@"), EmailFormat(), "") would return an invalid expression error.
Comments & Formatting
Comments and formatting can help you and other Admins understand how a formula expression works.
You can add comments within the formula expression field following these rules:
- Comments must be the first text in the field
- Comments should follow this format: /* {COMMENT} */
Vault preserves certain formatting within a formula expression:
- New lines
- Spaces
Null Values
You can use a formula expression to return a NULL value, which would set a field value to blank.
In many cases, when Vault encounters a NULL value in a formula expression, Vault returns a NULL value. For example, NULL + 1 would return NULL. However, there are exceptions to this rule.
If the formula has the setting Treat blank fields as zeros and empty strings, NULL will be treated as 0 in NULL + 1 and 1 will be returned. Additionally, if a function accepts multiple values, in the case of Max() and Sum(), these functions will ignore NULL values as long as at least one non-NULL value is passed in.
Application Strings
The following table lists the application strings you can use in the IsAppEnabled function:
| Application | Application String |
|---|---|
| Clinical Data: EDC | clinical_data_edc__v |
| Clinical Data: Workbench | clinical_data_workbench__v |
| Clinical: CTMS | clinical_ctms__v |
| Clinical: eTMF | clinical_etmf__v |
| Clinical: Payments | clinical_payments__v |
| Clinical: SiteConnect | clinical_siteconnect__v |
| Clinical: SSU | clinical_ssu__v |
| Clinical: CTMS | clinical_ctms__v |
| Medical: MedComms | medical_medcomms__v |
| Medical: MedInquiry | medical_medinquiry__v |
| Medical: Multichannel | medical_multichannel__v |
| PM: Multichannel | pm_multichannel__v |
| PM: PromoMats | pm_promomats__v |
| Quality: QMS | quality_qms__v |
| Quality: QualityDocs | quality_qdocs__v |
| Quality: Station Manager | quality_station_manager__v |
| Quality: Training | quality_training__v |
| Quality: Validation | quality_validation__v |
| RIM: Registrations | rim_reg__v |
| RIM: Submissions | rim_subs__v |
| RIM: Submissions Publishing | rim_subs_publishing__v |
| RIM: Submissions Archive | rim_subsarch__v |
| Safety Docs | safety_docs__v |
| Safety Management | safety_management__v |
| Vault CRM: Approved Email | vaultcrm_approved_email__v |
| Vault CRM: Core CRM | vaultcrm_core__v |
| Vault CRM: Engage | vaultcrm_engage__v |
| Vault CRM: Events Management | vaultcrm_events_management__v |