Vault Formula Reference Guide

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__v field

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.

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__c field

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__c field
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”

Cycle Time Functions

You can use cycle time functions in object report formula fields to perform cycle time calculations. Only the state__v field is supported for cycle time functions. For Union-All reports, cycle time calculations are supported in object-specific lifecycle state fields but are not supported in union fields.

previousState(state__v): Returns the value of the object’s previous state, for example, In Review.

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.

PicklistValues

Returns single and multiple values from a picklist field. Values with a hyphen are supported. You must use a picklist field as the first parameter.

Use

PicklistValues(picklist_field__c, 'picklist_value1__c', 'picklist_value2__c')

Examples

PicklistValues(impact_field__c, 'high__c', 'low__c'): Returns the High and Low value in the Impact picklist.

Picklist

Returns a single value from a picklist field.

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

You can only use format mask functions on Text and Number object fields. Email, Phone, Link, Time, and Percent fields default to their related format mask function and cannot be edited.

Format Mask Functions

PercentFormat

Description

Formats user’s input as a percentage when used with a Number field. Only available for use with Number fields. Vault uses this format mask for all Percent fields ((PercentFormat() without parameters) and it cannot be changed.

Use

PercentFormat(percent_format_mask)

Example

For these examples, assume that decimal places are set to five (5) for this Number field.

PercentFormat(): With input “51.950”, the value is treated as a percentage instead of a decimal. Upon saving, Vault stores a value of “0.51950” and displays “51.95%” in the UI. Any trailing non-zero decimal places are displayed, while trailing zeros are dropped.
PercentFormat("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.
PercentFormat(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__v is true or false. Upon saving, Vault stores a value of 0.67803, and displays “67.8%” or “67.803%”, depending on the value of closed__v.

EmailFormat

Description

Formats the user’s email address input as a hyperlink when used with a Text field. Only available on format expression for Text fields. Vault uses this format mask for all Email fields and it cannot be changed. This function does not allow any parameters.

Use

EmailFormat()

Example

EmailFormat(): With input “john.smith@company.com”, the email address is formatted as a hyperlink that opens a draft message in the user’s default email client. 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. However, you can use the Regex() logical function to validate the email address.

Note: 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.

TimeFormat

Description

Formats user’s input into a time. Only available for use in Text fields. This is the standard format mask for all Time fields and it cannot be changed. This function does not allow any parameters.

Use

TimeFormat()

Example

TimeFormat(): With input “13:30:30” in a Time field, Vault returns a time based on the locale settings setting. For example, if a US user enters “1:30 PM”, German users will see “13:30” instead.

The user’s input data must follow the “HH:MM:SS” syntax. Vault ignores any other syntax and displays the user’s input as-is. For example, if the user’s input is “1:30 PM”, Vault displays it as “1:30 PM” and does not convert the time based on the user’s locale.

PhoneFormat

Description

Formats the user’s input as a phone number. If a user specifies a country code, it is formatted as an international number. If no country code is specified, Vault formats the number using the North American Numbering Plan.

Only available for use in Text fields. Vault uses this format mask for all Phone fields and it cannot be changed. This function does not allow any parameters.

Use

PhoneFormat()

Example

PhoneFormat(): With input 9254526500, this expression returns “(###)-###-#####” as the format mask, which formats the input to “(925) 452-6500”. If a country code is provided, the format mask includes it. For example, with input +34932719725 or 19254526500, this expression returns “+## ### #######” and “# (###) ###-####” respectively. Vault formats the inputs to “+34 932 71 97 25” and “1 (925) 452-6500”.

Note: Users can enter non-numeric characters, such as ‘1-800-CABLETV’, or use their own formatting, such as ‘(456) 789-1234’. Vault will not attempt to format these inputs further.

LinkFormat

Formats the user’s input as a hyperlink. Only available for use in Text fields. Vault uses this format mask for all Link fields and it cannot be changed. Only the following parameters are accepted: "new_window", "same_window".

Use

LinkFormat(target)

Example

LinkFormat("new_window"): With input “www.veeva.com”, this expression returns a hyperlink as the format mask which directs the users to the Veeva home page. Clicking the hyperlink opens the destination in a new browser tab.

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`