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
Availability
| Object Configuration | ✓ |
| Object Lifecycle Actions | ✓ |
| Document Lifecycle Actions | ✓ |
See what is included in each category.
– (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
Availability
| Object Configuration | ✓ |
| Object Lifecycle Actions | ✓ |
| Document Lifecycle Actions | ✓ |
See what is included in each category.
* (Multiply)
Description
Multiplies two values
Use
value1 * value2
Data Types
- Number
Example
monthly_cost__c * 12- Returns the annual cost
Availability
| Object Configuration | ✓ |
| Object Lifecycle Actions | ✓ |
| Document Lifecycle Actions | ✓ |
See what is included in each category.
/ (Divide)
Description
Divides one value by another
Use
value1 / value2
Data Types
- Number
Example
(measurement1__c + measurement2__c) / 2- Returns average between two measurements
Availability
| Object Configuration | ✓ |
| Object Lifecycle Actions | ✓ |
| Document Lifecycle Actions | ✓ |
See what is included in each category.
% (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
Availability
| Object Configuration | ✓ |
| Object Lifecycle Actions | ✓ |
| Document Lifecycle Actions | ✓ |
See what is included in each category.
() (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
Availability
| Object Configuration | ✓ |
| Object Lifecycle Actions | ✓ |
| Document Lifecycle Actions | ✓ |
See what is included in each category.
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)
Availability
| Object Configuration | ✓ |
| Object Lifecycle Actions | ✓ |
| Document Lifecycle Actions |
See what is included in each category.
!= (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)
Availability
| Object Configuration | ✓ |
| Object Lifecycle Actions | ✓ |
| Document Lifecycle Actions |
See what is included in each category.
< (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
Availability
| Object Configuration | ✓ |
| Object Lifecycle Actions | ✓ |
| Document Lifecycle Actions |
See what is included in each category.
<= (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
Availability
| Object Configuration | ✓ |
| Object Lifecycle Actions | ✓ |
| Document Lifecycle Actions |
See what is included in each category.
> (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
Availability
| Object Configuration | ✓ |
| Object Lifecycle Actions | ✓ |
| Document Lifecycle Actions |
See what is included in each category.
>= (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”
Availability
| Object Configuration | ✓ |
| Object Lifecycle Actions | ✓ |
| Document Lifecycle Actions |
See what is included in each category.
&& (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
Availability
| Object Configuration | ✓ |
| Object Lifecycle Actions | ✓ |
| Document Lifecycle Actions |
See what is included in each category.
|| (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
Availability
| Object Configuration | ✓ |
| Object Lifecycle Actions | ✓ |
| Document Lifecycle Actions |
See what is included in each category.
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
Availability
| Object Configuration | ✓ |
| Object Lifecycle Actions | ✓ |
| Document Lifecycle Actions | ✓ |
See what is included in each category.
Date & DateTime Functions
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
Availability
| Object Configuration | ✓ |
| Object Lifecycle Actions | ✓ |
| Document Lifecycle Actions |
See what is included in each category.
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
Availability
| Object Configuration | ✓ |
| Object Lifecycle Actions | ✓ |
| Document Lifecycle Actions |
See what is included in each category.
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
Availability
| Object Configuration | ✓ |
| Object Lifecycle Actions | ✓ |
| Document Lifecycle Actions |
See what is included in each category.
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
Availability
| Object Configuration | ✓ |
| Object Lifecycle Actions | ✓ |
| Document Lifecycle Actions | ✓ |
See what is included in each category.
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
(created_date__v)- Returns the hour from the Created Date
Hour()- Returns the current hour in the Vault time zone
Availability
| Object Configuration | ✓ |
| Object Lifecycle Actions | ✓ |
| Document Lifecycle Actions |
See what is included in each category.
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
Availability
| Object Configuration | ✓ |
| Object Lifecycle Actions | ✓ |
| Document Lifecycle Actions |
See what is included in each category.
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
Availability
| Object Configuration | ✓ |
| Object Lifecycle Actions | ✓ |
| Document Lifecycle Actions |
See what is included in each category.
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
Availability
| Object Configuration | ✓ |
| Object Lifecycle Actions | ✓ |
| Document Lifecycle Actions | ✓ |
See what is included in each category.
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 under Business Admin > 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
Availability
| Object Configuration | |
| Object Lifecycle Actions | ✓ |
| Document Lifecycle Actions | ✓ |
See what is included in each category.
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
Availability
| Object Configuration | ✓ |
| Object Lifecycle Actions | ✓ |
| Document Lifecycle Actions | ✓ |
See what is included in each category.
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
Availability
| Object Configuration | ✓ |
| Object Lifecycle Actions | ✓ |
| Document Lifecycle Actions |
See what is included in each category.
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.
Availability
| Object Configuration | ✓ |
| Object Lifecycle Actions | ✓ |
| Document Lifecycle Actions | ✓ |
See what is included in each category.
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
Availability
| Object Configuration | ✓ |
| Object Lifecycle Actions | ✓ |
| Document Lifecycle Actions |
See what is included in each category.
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
Availability
| Object Configuration | ✓ |
| Object Lifecycle Actions | ✓ |
| Document Lifecycle Actions | ✓ |
See what is included in each category.
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
Availability
| Object Configuration | ✓ |
| Object Lifecycle Actions | ✓ |
| Document Lifecycle Actions | ✓ |
See what is included in each category.
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”
Availability
| Object Configuration | ✓ |
| Object Lifecycle Actions | ✓ |
| Document Lifecycle Actions |
See what is included in each category.
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 under Business Admin > 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
Availability
| Object Configuration | |
| Object Lifecycle Actions | ✓ |
| Document Lifecycle Actions | ✓ |
See what is included in each category.
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
Availability
| Object Configuration | ✓ |
| Object Lifecycle Actions | ✓ |
| Document Lifecycle Actions |
See what is included in each category.
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
Availability
| Object Configuration | ✓ |
| Object Lifecycle Actions | ✓ |
| Document Lifecycle Actions | ✓ |
See what is included in each category.
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
Availability
| Object Configuration | ✓ |
| Object Lifecycle Actions | ✓ |
| Document Lifecycle Actions |
See what is included in each category.
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
Availability
| Object Configuration | ✓ |
| Object Lifecycle Actions | ✓ |
| Document Lifecycle Actions |
See what is included in each category.
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
Availability
| Object Configuration | ✓ |
| Object Lifecycle Actions | ✓ |
| Document Lifecycle Actions |
See what is included in each category.
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
Availability
| Object Configuration | ✓ |
| Object Lifecycle Actions | ✓ |
| Document Lifecycle Actions |
See what is included in each category.
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
Availability
| Object Configuration | ✓ |
| Object Lifecycle Actions | ✓ |
| Document Lifecycle Actions |
See what is included in each category.
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
Availability
| Object Configuration | ✓ |
| Object Lifecycle Actions | ✓ |
| Document Lifecycle Actions |
See what is included in each category.
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
Availability
| Object Configuration | ✓ |
| Object Lifecycle Actions | ✓ |
| Document Lifecycle Actions |
See what is included in each category.
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
Availability
| Object Configuration | ✓ |
| Object Lifecycle Actions | ✓ |
| Document Lifecycle Actions |
See what is included in each category.
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”
Availability
| Object Configuration | ✓ |
| Object Lifecycle Actions | ✓ |
| Document Lifecycle Actions | ✓ |
See what is included in each category.
Sqrt
Description
Returns the square root of a number
Use
Sqrt(number)
Example
Sqrt(25)- Returns “5”, the square root of 25
Availability
| Object Configuration | ✓ |
| Object Lifecycle Actions | ✓ |
| Document Lifecycle Actions |
See what is included in each category.
Sum
Description
Adds the given numbers together
Use
Sum(number1, number2, ...)
Example
Sum(1.5, 2, 3, 4, 5.0)- Returns 15.5
Availability
| Object Configuration | ✓ |
| Object Lifecycle Actions | ✓ |
| Document Lifecycle Actions |
See what is included in each category.
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”
Availability
| Object Configuration | ✓ |
| Object Lifecycle Actions | ✓ |
| Document Lifecycle Actions | ✓ |
See what is included in each category.
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
Availability
| Object Configuration | ✓ |
| Object Lifecycle Actions | ✓ |
| Document Lifecycle Actions |
See what is included in each category.
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, 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)
Availability
| Object Configuration | ✓ |
| Object Lifecycle Actions | ✓ |
| Document Lifecycle Actions |
See what is included in each category.
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
Availability
| Object Configuration | ✓ |
| Object Lifecycle Actions | ✓ |
| Document Lifecycle Actions |
See what is included in each category.
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”
Availability
| Object Configuration | ✓ |
| Object Lifecycle Actions | ✓ |
| Document Lifecycle Actions |
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
Availability
| Object Configuration | ✓ |
| Object Lifecycle Actions | ✓ |
| Document Lifecycle Actions |
See what is included in each category.
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”
Availability
| Object Configuration | ✓ |
| Object Lifecycle Actions | ✓ |
| Document Lifecycle Actions |
See what is included in each category.
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
Availability
| Object Configuration | ✓ |
| Object Lifecycle Actions | ✓ |
| Document Lifecycle Actions |
See what is included in each category.
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
Availability
| Object Configuration | ✓ |
| Object Lifecycle Actions | ✓ |
| Document Lifecycle Actions |
See what is included in each category.
Text Functions
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”
Availability
| Object Configuration | ✓ |
| Object Lifecycle Actions | ✓ |
| Document Lifecycle Actions |
See what is included in each category.
Find
Description
Returns the position of a string within a string of text
Use
Find(subtext, text)
Example
Find(" ", "4280 Hacienda Dr, Pleasanton, CA")- Returns 5, the position of the first space in the address
Availability
| Object Configuration | ✓ |
| Object Lifecycle Actions | ✓ |
| Document Lifecycle Actions |
See what is included in each category.
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
Availability
| Object Configuration | ✓ |
| Object Lifecycle Actions | ✓ |
| Document Lifecycle Actions | ✓ |
See what is included in each category.
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
Availability
| Object Configuration | ✓ |
| Object Lifecycle Actions | |
| Document Lifecycle Actions |
See what is included in each category.
Left
Description
Returns the specified number of characters from the beginning of a text string
Use
Left(text, position)
Example
Left(name__v, 5) & " - " & abbreviation__c- Returns the first five characters of a name and it’s abbreviation concatenated, for example, “Chole – CC”
Availability
| Object Configuration | ✓ |
| Object Lifecycle Actions | ✓ |
| Document Lifecycle Actions | ✓ |
See what is included in each category.
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
Availability
| Object Configuration | ✓ |
| Object Lifecycle Actions | ✓ |
| Document Lifecycle Actions |
See what is included in each category.
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”
Availability
| Object Configuration | ✓ |
| Object Lifecycle Actions | ✓ |
| Document Lifecycle Actions | ✓ |
See what is included in each category.
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”
Availability
| Object Configuration | ✓ |
| Object Lifecycle Actions | ✓ |
| Document Lifecycle Actions |
See what is included in each category.
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”
Availability
| Object Configuration | ✓ |
| Object Lifecycle Actions | ✓ |
| Document Lifecycle Actions |
See what is included in each category.
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
Availability
| Object Configuration | ✓ |
| Object Lifecycle Actions | ✓ |
| Document Lifecycle Actions |
See what is included in each category.
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, 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.
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
Availability
| Object Configuration | ✓ |
| Object Lifecycle Actions | ✓ |
| Document Lifecycle Actions | ✓ |
See what is included in each category.
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
Availability
| Object Configuration | ✓ |
| Object Lifecycle Actions | ✓ |
| Document Lifecycle Actions |
See what is included in each category.
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”
Availability
| Object Configuration | ✓ |
| Object Lifecycle Actions | ✓ |
| Document Lifecycle Actions | ✓ |
See what is included in each category.
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.
Availability
This function is only available for Formula-type fields on objects where the Return Type is Link.
| Object Configuration | |
| Object Lifecycle Actions | |
| Document Lifecycle Actions |
See what is included in each category.
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
Availability
| Object Configuration | |
| Object Lifecycle Actions | ✓ |
| Document Lifecycle Actions | ✓ |
See what is included in each category.
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
Availability
| Object Configuration | ✓ |
| Object Lifecycle Actions | ✓ |
| Document Lifecycle Actions | ✓ |
See what is included in each category.
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 %20
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.
Availability
| Object Configuration | ✓ |
| Object Lifecycle Actions | ✓ |
| Document Lifecycle Actions |
See what is included in each category.
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 |
Function & Operator Availability
Various administration areas within Vault allow different functions and operators. You can see where a given function or operator is available in the Availability sections above.
- Object Configuration includes defaulting on object fields, Formula-type fields on objects, object page layout rules, workflow start step rules, and record validation rules.
- Object Lifecycle Actions include Update Record Field workflow actions and Update Record Field lifecycle state entry actions.
- Document Lifecycle Actions include Set field with formula lifecycle state entry actions and Update document field workflow actions.
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.
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.
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.
Any time that Vault encounters an error when evaluating a formula expression because of a NULL value, Vault returns a NULL value. For example, NULL + 1 would return NULL.