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

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

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.

Math Functions

Abs

Description

Calculates the absolute value of a number

Use

Abs(number)

Example

Abs(height_1__c - height_2__c)
Returns the difference between Height 1 and Height 2 as a positive value regardless of which measurement is greater

Average

Description

Calculates the average of a set of numbers

Use

Average(number1, number2, ...)

Example

Average(1.5, 2, 3, 4, 5.0)
Returns 3.1

Ceiling

Description

Returns the next integer greater than the value

Use

Ceiling(number)

Example

Ceiling(14.2)
Returns 15 by rounding up to the nearest integer
Ceiling(-14.2)
Returns -14 by rounding up to the nearest integer

CountA

Description

Returns the number of populated values

Use

CountA(value1, value2, …)

Example

CountA(description1__c, description2__c, description3__c)
Returns the number of text fields that are not blank
CountA(completion_date__c, created_date__c, closed_date__c)
Returns the number of Date fields that are not blank

CurrencyRate

Description

Returns the conversion rate to the corporate currency for the given currency. If the currency is invalid, the function returns 1.0.

Use

CurrencyRate(currency_ISO_code)

Example

CurrencyRate("EUR")
Returns the conversion rate to your Vault’s corporate currency for EUR.

Floor

Description

Returns the next integer less than the value

Use

Floor(number)

Example

Floor(14.2)
Returns 14 by rounding down to the nearest integer
Floor(-14.2)
Returns -15 by rounding down to the nearest integer

FromUnixTime

Description

Returns the DateTime representing the given number as seconds elapsed since 1 January 1970

Use

FromUnixTime(number)

Example

FromUnixTime(time_in_seconds__v)
Returns the DateTime in your local timezone resulting from the value of Time in Seconds elapsed since 1 January 1970

Max

Description

Returns the largest value from the set

Use

Max(value1, value2, …)

Data Types

  • Number
  • Date
  • DateTime

You cannot mix data types in a single Max() request. If you need to mix Date and DateTime, we suggest converting DateTimes to Dates using DateValue.

Example

Max(measurement1__c, measurement2__c, measurement3__c)
Returns the greatest measurement number
Max((units__c * price__c), min_value__c)
Returns the greater of Units multiplied by Price or Minimum Value
Max(approval_date__c, today(), completed_date__v
Returns the greater of the given dates, which is the most recent (youngest) date

Median

Description

Returns the median of the given numbers

Use

Median(number1, number2, …)

Example

Median(23, 5, 75, 17, 9)
Returns 17
Median(3, 12, 6, 43)
Returns 9

Min

Description

Returns the lowest value from the set

Use

Min(value1, value2, …)

Data Types

  • Number
  • Date
  • DateTime

You cannot mix data types in a single Min() request. If you need to mix Date and DateTime, we suggest converting DateTimes to Dates using DateValue.

Example

Min((units__c * price__c), max_value__c)
Returns the lesser of Units multiplied by Price and Maximum Value
Min(approval_date__c, today(), completed_date__v)
Returns the lesser of the given dates, which is the least recent (oldest) date

Mode

Description

Returns the mode of the given numbers; returns the smallest number if two modes are found

Use

Mode(number1, number2, …)

Example

Mode(15, 16, 36, 16, 16)
Returns 16
Mode(4, 5, 7, 8)
Returns NULL

Pi

Description

Returns the number pi

Use

Pi()

Example

Pi()
Returns the number pi to 255 places

PicklistCount

Description

Returns the number of selected values in a multi-value picklist

Use

PicklistCount(picklist_field)

Example

Picklist(authentication_type__v)
Returns the number of values in the Authentication Type picklist

Power

Description

Given two numbers, multiplies the first number by itself as many times as specified in the second number

Use

Power(number, number)

Example

Power(5, 3)
Returns 125 by multiplying 5 by itself 3 times: 5 * 5 * 5
Power(8, 1/3)
Returns 2 by multiplying 8 by itself 1/3 times: 8 * ⅓
Power(9, 0)
Returns 1; if the second argument is zero, then the result is always 1

Rand

Description

Returns an evenly distributed random real number greater than or equal to 0 and less than 1

Use

Rand()

Example

Rand()
Returns a random number greater than or equal to 0 and less than 1
Rand() * 10
Returns a random number greater than or equal to 0 and less than 10

Round

Description

Rounds the number to the defined number of decimal places

Use

Round(number, number)

Example

Round(5.5, 0)
Returns “6”, 5.5 rounded up with no decimal value
Round(5.54, 1)
Returns “5.5”, 5.54 rounded down with one decimal place
Round(-5.5, 0)
Returns “-6”, -5.5 rounded to the nearest number with no decimal value

Round (Significant)

Description

Rounds to the defined number of significant figures

Use

Round(number, number_of_digits, "significant") OR Round(number, number_of_digits, "significant-astm")

Example

Round(0.445, 2, "significant")
Returns “0.45”
Round(0.445, 2, "significant-astm")
Returns “0.44”

Sqrt

Description

Returns the square root of a number

Use

Sqrt(number)

Example

Sqrt(25)
Returns “5”, the square root of 25

StDevP

Description

Returns the population standard deviation for the given numbers

Use

StDevP(number1, number2, …)

Example

StDevP(45, 31, 36, 88)
Returns 22.50

StDevS

Description

Returns the sample standard deviation for the given numbers

Use

StDevS(number1, number2, …)

Example

StDevS(10, 12, 18, 13)
Returns 3.40

Sum

Description

Adds the given numbers together

Use

Sum(number1, number2, ...)

Example

Sum(1.5, 2, 3, 4, 5.0)
Returns 15.5

Trunc

Description

Truncates a number to an integer or the specified number of decimal places

Use

Trunc(number, digits)

The digits parameter is optional and specifies the number of decimal places to include. If you pass Trunc functions without this parameter, Vault truncates the number with zero decimal places.

Example

Trunc(7.9)
Returns 7
Trunc(5.62, 1)
Returns 5.6

Value

Description

Returns a text string as a number; if the text does not resolve to a number, the function will result in an error

Use

Value(text)

Example

Value(Right("S1234",4)
Returns the number “1234”

Deprecated Functions

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, 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

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)

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

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)

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.

Use

Regex(text, regex_text)

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)

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

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”

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)

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”

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

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

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

Upper

Description

Converts all letters in the specified text string to uppercase

Use

Upper(text)

Example

Upper("Company A")
Returns the text string “COMPANY A”

Other Functions

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

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.

Picklist Value Expressions

You can use an expression to reference a picklist value in a formula.

Use

Picklist.<picklist name>.<picklist value name>

Examples

Picklist.impact__c.high__c
Returns the “High” value in the “Impact” picklist.
if(object_type__v = "Action Plan", Picklist.ap_securityc.analysis_and_action_planc, Picklist.ap_securityc.action_plan__c)
Returns the “Analysis & Action Plan” value from the “Action Plan Security” picklist if the object type is “Action Plan”; otherwise, returns the “Action Plan” value from the “Action Plan Security” picklist.

Format Masks

You can use all Vault operators and functions to create a Format Mask expression as long as the expression resolves to a text string. In other words, if you use conditional functions such as If or Case, all outputs must be a format mask wrapped in quotation marks.

Format mask expressions also support the token this, which returns the value of the current field.

Example

If(Length(Text(this))<=7,"###-####","###-###-####")
With input “1234567”, this expression returns “###-####” as the format mask, which formats the input to “123-4567”. With input “5551234567”, this expression returns “###-###-####” as the format mask, which formats the input to “555-123-4567”.

Format Mask Functions

Percent

Description

Formats user’s input as a percentage when used with a percent format mask

Use

Percent(percent_format_mask)

Example

Percent("0.00%")
With input “38.5”, the value is treated as a percentage instead of a decimal. Upon saving, Vault stores a value of 0.385, while displaying “38.50%” in the UI.
Percent(If(closed__v=true,"0.0%","0.000%"))
With input “67.803”, the value is treated as a percentage instead of a decimal regardless of whether closed__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 input as a hyperlink when used with an Email format mask.

Use

EmailFormat(text)

Example

EmailFormat(john.smith@company.com)
The john.smith@company.com email address is formatted as a link after the user inputs it in a Text field. When a user clicks the link, Vault opens a draft message in the user’s default email client with john.smith@company.com populated in the To field. Vault does not check if the input value is a valid email address. However, you can use the Regex() logical function to validate the email address.

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.