# Vault Formula Reference Guide This reference guide includes a list of available operators and functions for use in Vault. Not all functions and operators are available for all areas of Vault using formulas. ## Math Operators ### + (Add) {#plus-operator} #### 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) {#minus-operator} #### 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) {#multiply-operator} #### Description Multiplies two values #### Use `value1 * value2` #### Data Types * Number #### Example **`monthly_cost__c * 12`** : Returns the annual cost ### / (Divide) {#divide-operator} #### Description Divides one value by another #### Use `value1 / value2` #### Data Types * Number #### Example **`(measurement1__c + measurement2__c) / 2`** : Returns average between two measurements ### ^ (Exponent) {#exponent-operator} #### 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) {#remainder-operator} #### 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) {#parenthesis-operator} #### 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 {#logical-operators} ### = (Equals) {#equals-operator} #### 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) {#does-not-equal-operator} #### 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) {#less-than-operator} #### 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) {#less-than-or-equal-to-operator} #### 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) {#greater-than-operator} #### 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) {#greater-than-or-equal-to-operator} #### 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) {#and-operator} #### Description Evaluates if two values or expressions are both true #### Use `(expression1) && (expression2)` Users may use [`And()`][16] 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) {#or-operator} #### Description Evaluates if at least one of two values or expressions is true #### Use `(expression1) || (expression2)` Users may use [`Or()`][17] 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) {#concatenate-operator} #### Description Connects two or more text strings #### Use `text1 & text2` Users may use [`Concat()`][2] in place of `&` #### Example **`"Product: " & name__v`** : Returns the text string "Product: " combined with the name of the object record, for example, "Product: Cholecap"
## Date & DateTime Functions {#date-datetime-functions} ### AddMonths {#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 {#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 {#datevalue} #### Description Returns the date portion of a DateTime value. This function also accepts an optional `Timezone` parameter. 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 {#datetimevalue} #### Description Returns the date and time of a DateTime value. This function accepts a Date, DateTime, 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, @Vault.timezone__sys)`** : Returns the approval date at 12:00 AM in the user's Vault's timezone ### Day {#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 {#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 {#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 {#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 {#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 {#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 {#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 {#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 {#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 {#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 {#now} #### Description Returns the current date and time for the active user in the user's time zone. #### Use `Now()` #### Example **`Now() - created_date__v`** : Returns the number of days since Created Date ### Second {#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 {#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 2020/07/01 00:00 in Oslo time. ### 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 {#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 {#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 {#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) {#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 {#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 {#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 {#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 {#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 {#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][3]. * `DateTimeAdd`: Use the [+ operator][3]. * `DateDiff`: Use the [- operator][4]. * `DateTimeDiff`: Use the [- operator][4].
## Math Functions ### Abs {#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 {#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 {#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 {#counta} #### Description When used with a list of fields (`value1, value2, …`), returns the number of fields that are not blank. Fields with blank values are not returned. When used with a [`collection`][22] parameter in a workflow, returns the total number of task verdicts, task statuses, or field values on a document or record in a workflow. You can only use the `collection` parameter with this function in _Decision_ step conditions. When used with a records parameter, returns the total number of `records` counted. #### Generic Use `CountA(value1, value2, …)` #### Generic 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 #### Workflow Collection Use `CountA(collection)` #### Workflow Collection Example **`CountA(Tasks.review_task__c.verdict)`** : Returns the number of task verdicts submitted for all task instances of the `review_task__c` in the workflow. If the task is assigned to ten (10) users, and each user submits a different task verdict, the number ten (10) is returned. If one (1) user cancels a task, the number nine (9) is returned. #### Records Use **`CountA(records)`** #### Records Example **`CountA(GetRelatedRecords($accounts__cr, "reviewed=true"))`** : Returns the number of _Accounts_ records in the _Reviewed_ state. ### CountIf {#countif} #### Description Returns the total number of task verdicts, task statuses, or field values on a document or object record that meet the defined criteria. Can only be used in workflow _Decision_ step conditions. The [`collection`, `collection_item`, and `formula`][22] parameters are required. #### Use `CountIf(collection, collection_item, formula)` #### Example `CountIf(Tasks.review__c.status, x, x = TaskStatus.CANCELED) = 0` : Returns true if none of the review__c task instances are Canceled `CountIf(Items.date__c, x, IsBlank(x)) > 0` : Returns true if at least one (1) Date field is blank `CountIf(Tasks.review__c.verdict, x, x = "Approved") = CountA(Tasks.review__c.verdict)` : Returns true if all verdicts for instances of the review__c task are submitted as Approved. `CountIf(Tasks.review__c.verdict, x, OR(x='Approved', x='Approved with Comments')) = CountA(Tasks.review__c.verdict)` : Returns true if all verdicts for instances of the review__c task are submitted as Approved or Approved with Comments. `CountIf(Tasks.review__c.verdict, x, x = "Rejected") > 0` : Returns true if at least one (1) verdict for the review__c task is submitted as Rejected. `CountIf(Items.boolean__c, x, x = true) = Workflow.items__count` : Returns true if all Boolean field values equal Yes `AND(CountIf(Tasks.review__c.verdict, x, x = "Approved") = CountA(Tasks.review__c.verdict), CountIf(Tasks.review__c.status, x, x = TaskStatus.CANCELED) = 0)` : Returns true if all participants completed their tasks and none were canceled.
### Workflow Functions {#workflow-functions} The `collection` parameter used in the `CountA` and `CountIf` functions represents a collection of task verdicts, task statuses, and fields within a workflow. The `collection` parameter accepts the following workflow identifier values: * _Workflow Items Collection_ (`Items.fieldName`): Used to evaluate a field on the document or object record in the workflow * _Workflow Task Verdict Collection_ (`Tasks.taskName.verdict`): Used to evaluate the verdict of a defined task in a document or object workflow. When used in a `CountIf` function, the` formula` must represent the verdict's configured label. For example,` CountIf(Tasks.review__c.verdict, x, x = "Reviewed")`, `Reviewed` is the configured label of the verdict. * _Workflow Task Status Collection_ (`Tasks.taskName.status`): Used to evaluate the status of a task in a document or object workflow * _Task Canceled Status_ (`TaskStatus.CANCELED`): Used to evaluate task instances that are canceled * _Task Completed Status_ (`TaskStatus.COMPLETED`): Used to evaluate task instances that are completed by the task owner * _Task Skipped Status_ (`TaskStatus.SKIPPED`): Used to evaluate optional tasks that are skipped by the workflow process due to no participants assigned to the task The `collection_item` parameter accepts a variable, such as `x`, that represents each individual item (field, task verdict, or task status) in the `collection`. This variable is used in the formula expression defined in the `formula` parameter. The `formula` parameter is used to evaluate each item in the `collection` as defined by a formula expression that must return a true or false result. For example, in the formula `CountIf(Tasks.review__c.verdict, x, x = "Rejected") > 0`, `Tasks.review__v.verdict`, represents the `collection`,` x` represents the `collection item`, and `x = "Rejected"` represents the `formula`. See the [`CountA`][20] and [`CountIf`][21] functions above for specific examples using each parameter. See below for restrictions involved with using `collection` in `CountA` and `CountIf` functions: * Null or blank field values on documents or objects are not included in calculations with the `CountA` function. These fields are included if used in the `formula` parameter of the `CountIf` function. * Skipped or canceled tasks are not included in task verdict calculations * Open tasks are not included in calculations or evaluations for task verdicts and statuses * _Workflow Items Collection_ can only be used in multi-document and multi-record workflows * You cannot use _Workflow Items Collection_ with Formula, Lookup, DateTime, Long Text, Rich Text, Component, Object reference, or multi-value Picklist fields. ### CurrencyRate {#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 {#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 {#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 {#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][5]. #### 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 {#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 {#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][5]. #### 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 {#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 {#pi} #### Description Returns the number pi #### Use `Pi()` #### Example **`Pi()`** : Returns the number pi to 255 places ### PicklistCount {#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 {#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 {#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 {#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) {#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 {#sqrt} #### Description Returns the square root of a number #### Use `Sqrt(number)` #### Example **`Sqrt(25)`** : Returns "5", the square root of 25 ### StDevP {#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 {#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 {#sum} #### Description Adds the given numbers together #### Use `Sum(number1, number2, ...)` #### Example **`Sum(1.5, 2, 3, 4, 5.0)`** : Returns 15.5 ### Trunc {#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 {#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][6].
## Logical Functions ### And {#and} #### Description Returns true when all expressions are true Users may use [`&&`][18] 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 {#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 {#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 {#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 {#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][13]. #### 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 {#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 {#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 {#ischanged} #### Description Compares a field value to the previous value and returns true if the values are different. For Long Text and Rich Text fields, the `IsChanged()` function compares the first 1,500 characters only. If the user edits the value after the first 1,500 characters, the function returns false, even when the value as a whole has changed.
#### 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 {#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 {#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 {#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 {#or} #### Description Evaluates if at least one of two values or expressions is true #### Use `(expression1) || (expression2)` Users may use [`||`][19] 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 {#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 {#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 {#vlookup} #### Description Searches an object for a record where the `field_on_lookup_object` field value matches the `lookup_value` and returns another field on that record specified as `field_to_return`. This is similar to the VLOOKUP() function in Microsoft Excel.
#### Use `VLookUp(field_to_return, field_on_lookup_object, lookup_value)` To reference fields from an object, you must use the `$ObjectField.object_name_c.field_name_c` format. #### Example `VLookUp($ObjectField.global_maximum_settings_c.maximumc, $ObjectField.global_maximum_settingsc.namev, "Maximum Retries") <= current_retry_number_c ` : 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 {#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")`
### DisplayMode {#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 {#text-functions} ### Ascii {#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 {#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 {#concat} #### Description Connects two or more text strings #### Use `Concat(text1, text2, …)` Users may use [`&`][7] in place of `Concat()` #### Example **`Concat("Product: ", name__v)`** : Returns the text string "Product: " combined with the name of the object record, for example, "Product: Cholecap"
### Contains {#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 {#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 {#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 {#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 {#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 {#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 {#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 {#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 {#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 {#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 {#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 {#text} #### Description Converts a value to text based on a specified format See [Date Formats][8] and [Number Formats][9] 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.
#### 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 {#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 {#define} #### Description Replaces long blocks of text with a shorter set of characters. `#define` cannot be set for functions or string-only static value references. #### Use `#define {text to be defined}` #### Example **Expression without `#define`**: **`if(today() > parent_object_cr.due_date_for_child_records_c, "Late", "On-Time")`** **Expression with `#define`**: `#define dueDate parent_object_cr.due_date_for_child_records_c if(today() > dueDate, "Late", "On-Time")` : This example replaces `parent_object_cr.due_date_for_child_records_c` with the defined `dueDate` in formulas throughout the expression. ### Upper {#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 {#cycle-time-functions} You can use cycle time functions in report formula fields to perform cycle time calculations. For Union-All reports, cycle time calculations are supported in lifecycle state fields but are not supported in union fields.
See FAQ: Process Reporting for a list of frequently asked questions about cycle time functions. ### CountInState {#countinstate} #### Description Returns the number of times the object has been in the specified _State_ (`state__v`) or _State Type_ (`state_type__v`), or the number of times the document has been in the specified _Status_ (`status__v`) or _State Type_ (`state_type__v`). This function also accepts an optional `by_document_major_version` parameter (`true` or `false`) for _Document with Previous Version_ reports. When omitted or set to `false`, Vault performs the calculation across all document versions. When set to `true`, Vault performs the calculation on each steady state version. A single steady state version's cycle encompasses all preceding versions after the last steady state version. #### Use `countInState(field, value, (optional) by_document_major_version)` #### Example `countInState(project__c.state__v, "In Edit")` : Returns the number of times the object has been in the _In Edit_ state. ### DurationInState {#durationinstate} #### Description Returns the number of days that the object has been in the specified _State_ (`state__v`) or _State Type_ (`state_type__v`), or the number of times the document has been in the specified _Status_ (`status__v`) or _State Type_ (`state_type__v`). If the object or document has been in the state multiple times, Vault calculates this value across all instances. This function also accepts an optional `by_document_major_version` parameter (`true` or `false`) for _Document with Previous Version_ reports. When omitted or set to `false`, Vault performs the calculation across all document versions. When set to `true`, Vault performs the calculation on each steady state version. A single steady state version's cycle encompasses all preceding versions after the last steady state version. #### Use `durationInState(field, value, by_document_major_version)` #### Example `durationInState(documents.status__v, "In Review", true)` : Returns the number of days that the document steady state version was in the _In Review_ state. ### FirstTimeInState {#firsttimeinstate} #### Description Returns the date and time the object first entered the specified _State_ (`state__v`) or _State Type_ (`state_type__v`), or the date and time the document first entered the specified _Status_ (`status__v`) or _State Type_ (`state_type__v`). This function also accepts an optional `by_document_major_version` parameter (`true` or `false`) for _Document with Previous Version_ reports. When omitted or set to `false`, Vault performs the calculation across all document versions. When set to `true`, Vault performs the calculation on each steady state version. A single steady state version's cycle encompasses all preceding versions after the last steady state version. #### Use `firstTimeInState(field, value, (optional) by_document_major_version)` #### Example `firstTimeInState(project__c.state__v, "In Review")` : Returns the date and time the object first entered the _In Review_ state. ### LastTimeEnteredInState #### Description Returns the date and time the object most recently entered the specified _State_ (`state__v`) or _State Type_ (`state_type__v`), or the date and time the document most recently entered the specified _Status_ (`status__v`) or _State Type_ (`state_type__v`). This function also accepts an optional `by_document_major_version` parameter (`true` or `false`) for _Document with Previous Version_ reports. When omitted or set to `false`, Vault performs the calculation across all document versions. When set to `true`, Vault performs the calculation on each steady state version. A single steady state version's cycle encompasses all preceding versions after the last steady state version. #### Use `lastTimeEnteredInState(field, value, (optional) by_document_major_version)` #### Example `lastTimeEnteredInState(document.status__v, "In Approval", true)` : Returns the most recent date and time the document steady state version entered the _In Approval_ state. ### LastTimeInState {#lasttimeinstate} #### Description Returns the most recent date and time the object left the specified _State_ (`state__v`) or _State Type_ (`state_type__v`), or the most recent date and time the document left the specified _Status_ (`status__v`) or _State Type_ (`state_type__v`). This function also accepts an optional `by_document_major_version` parameter (`true` or `false`) for _Document with Previous Version_ reports. When omitted or set to `false`, Vault performs the calculation across all document versions. When set to `true`, Vault performs the calculation on each steady state version. A single steady state version's cycle encompasses all preceding versions after the last steady state version. #### Use `lastTimeInState(field, value, (optional) by_document_major_version)` #### Example `lastTimeInState(documents.status__v, "Reviewed")` : Returns the most recent date and time the document left the _Reviewed_ state. ### PreviousState {#previousstate} #### Description Returns the object's previous _State_ (`state__v`) or _State Type_ (`state_type__v`), or the document's previous _Status_ (`status__v`) or _State Type_ (`state_type__v`). #### Use `previousState(field)` #### Example `previousState(project__c.state__v)` : Returns the value of the object's previous state, for example, _In Review_. ## Aggregate Reporting Functions {#aggregate-functions} Aggregate functions are available when you create report formula fields . These functions can calculate aggregated values, such as averages and cumulative sums, by performing calculations across report rows using groups. The calculated data can be used in report filters, prompts, conditional fields, and grouping. A single report formula field can contain up to three aggregate functions. ### AvgRows #### Description Calculates the average of rows within a report grouping. Available for number fields only. #### Use `AvgRows(field)` #### Example `AvgRows(invoice__c.shipping_cost__c)` : Calculates the average shipping cost in a report grouping. ### CountRows #### Description Counts the rows within a report grouping. #### Use `CountRows(field)` #### Example `CountRows(documents.name__v)` : Calculates the number of documents within a report grouping. ### MinRows #### Description Returns the minimum value of rows within a report grouping. Available for number fields only. #### Use `MinRows(field)` #### Example `MinRows(invoice__c.invoice_amount__c)` : Returns the minimum value of an invoice amount in a report grouping. ### MaxRows #### Description Returns the maximum value of rows within a report grouping. Available for number fields only. #### Use `MaxRows(field)` #### Example `MaxRows(invoice__c.invoice_amount__c)` : Returns the maximum value of an invoice amount in a report grouping. ### PrevGroupVal #### Description Returns the value of a previous group. Available for number fields only. #### Use `PrevGroupVal(aggregate_function_label, field, (optional) number)` #### Example `PrevGroupVal('SumRows',invoice__c.invoice_amount__c)` : Calculates the sum of a previous group value and the invoice amount. You can also use PrevGroupVal multiple times to perform multiple calculations, such as calculating rolling statistics: `SumRows(invoice__c.invoice_amount__c) + PrevGroupVal('SumRows',invoice__c.invoice_amount__c,1) + PrevGroupVal('SumRows',invoice__c.invoice_amount__c,2)` : In the above example, the first group calculates the invoice amount. The second group calculates the sum of the first and second group. The third group calculates the sum of the first, second, and third group. ### SumRows #### Description Calculates the total value of a field within a report grouping. Available for number fields only. #### Use `SumRows(field)` #### Example `SumRows(invoice__c.invoice_amount__c)` : Calculates the sum of the invoice amounts in a report grouping. ## Other Functions {#other-functions} ### Hyperlink {#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 {#recordbylabel} #### Description Returns object references for the specified object label. If multiple records with the same label exist, Vault returns the record with the earliest _Created Date_. Inactive records are ignored. #### Use `RecordByLabel()` #### Example **`RecordByLabel ("Cholecap")`** : Returns the object reference for the _Cholecap_ product ### RecordByUniqueField {#recordbyuniquefield} #### Description Returns a single object reference record with the specified parameters. The `object `parameter accepts an object's _Name_ value in the following component reference format: `$object_name__c`. The `{field_values}` parameter accepts a unique field's _Name_ value and field value in the following format: `{$unique_field_name__v:"field value"/field_value}`. Values must match the field type. If the referenced record is from a child or join object, you must include the parent's field and value in the string. A `true` value in the optional `exclude_inactive` parameter excludes inactive records, while a `false` value includes these records. If the parameter is not provided, it will always include inactive records. You can use the _Create Record_ action in Action Triggers to create a related record by setting the object reference field using the below formula: `CreateRecord($child_object_c, {$parent_fieldc : RecordByUniqueField($parent_object_c, {$id: id})});` #### Use `RecordByUniqueField(object,{field_values},exclude_inactive)` #### Example `If(` `country__cr.name__v = "USA",` `RecordByUniqueField($region__c, {$name__v:"US"}),` `RecordByUniqueField($region__c, {$name__v:"ROW"})` `)` : Defaults the _Region_ object field to _US_ and _ROW_ if _USA_ is entered in the _Name_ field on the _Country_ object. ### 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. ### FileSize {#filesize} #### Description Returns the file size in bytes of a file in an _Attachment_ field. #### Use `FileSize(attachment_field)` #### Example `FileSize(lab_studies__c)` : Returns the file size in bytes of the document uploaded to the _Lab Studies_ field. ### Md5Checksum {#md5checksum} #### Description Returns the MD5 checksum of a file in an _Attachment_ field. If the MD5 checksum is currently processing, Vault returns "pending" instead. #### Use `Md5Checksum(attachment_field)` #### Example `Md5Checksum(test_results__c)` : Returns the MD5 checksum of a file uploaded to the _Test Results_ field. ### MimeType {#mimetype} #### Description Returns the MIME type of a file uploaded to an _Attachment_ field. If the MIME type is still processing, Vault returns the MIME type based on the file's extension. #### Use `MimeType(attachment_field)` #### Example `MimeType(images__c)` : Returns the MIME type of a file uploaded to the _Images_ field. ## Date Formats {#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| |hh:ii:ss
HH:II:SS|12:05:39|Returns the hour, minute, and seconds for a datetime| ## Number Formats {#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} 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. When used in functions, document picklist fields are treated as text, and object picklist fields are treated as the _Picklist_ data type but can be wrapped in the `Text()` function. To populate a picklist field using an expression, document picklists expect a name of a value like `one__c`, while object picklists expect a picklist value defined as follows: `Picklist.picklist_name.value` ### Picklist Value {#picklistvalue} Returns a single value from a picklist. The `$picklist_field__c` parameter accepts an active, single-value picklist _Name_ value, such as `source__c`. The `value` parameter accepts a single value from the picklist. You can also use this expression in an Action Trigger . #### Use `PicklistValue($picklist_field__c, value)` #### Example `PicklistValue($impact_field__c, 'low__c')` : Returns the _Low_ value in the _Impact_ picklist. ### PicklistValues {#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. You can also use this expression in an Action Trigger . #### Use `PicklistValues($picklist_field__c, 'picklist_value1__c', 'picklist_value2__c')` #### Example **`PicklistValues($impact_field__c, 'high__c', 'low__c')`** : Returns the _High_ and _Low_ value in the _Impact_ picklist. ### Picklist {#picklist} Returns a single value from a picklist field. #### Use `Picklist..` #### 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_plan__c, 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 {#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 {#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 {#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.
### TimeFormat {#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 {#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".
### LinkFormat {#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 {#appstrings} 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` | [1]: #avail [2]: #concat [3]: #plus-operator [4]: #minus-operator [5]: #datevalue [6]: #equals-operator [7]: #concatenate-operator [8]: #date-formats [9]: #number-formats [10]: #regex [11]: #format-mask-functions [12]: #system-variables [13]: #appstrings [14]: #logical-operators [15]: #date-datetime-functions [16]: #and [17]: #or [18]: #and-operator [19]: #or-operator [20]: #counta [21]: #countif [22]: #workflow-functions [23]: #recordbyuniquefield