Creating Formulas in Vault

You can create formulas to accomplish a variety of tasks in Vault. Vault uses an Excel-like formula language, including functions and operators that allow you to calculate dates and text strings, as well as numbers.

Some examples of formulas in Vault include:

  • Calculating values or dynamically displaying icons through a formula-type object field.
  • Setting default field values on object records or documents.
  • Setting field values on a document or object record as part of a workflow or lifecycle state change.

This article includes information about formula syntax, valid fields and data types, and general guidelines for writing formulas. The Vault Formula Reference Guide provides details for all functions and operators.

Valid Fields

Valid field types will vary based on where you’re building a formula. For supported field types and other use-specific information, see the following articles:

Lifecycle State Field

You can reference an object record’s lifecycle state within a formula. At runtime, Vault replaces these with the name or label for the record’s current lifecycle state. For example:

  • state__v or [Object Name].state__v retrieves the name, planned_state__v
  • Text(state__v) retrieves the label as text, “Planned”

Object Reference Field

You can retrieve the object reference to a record within a formula. For example:

  • country__c retrieves the object reference to the record
  • Text(country__c) retrieves the record label as text, “United States”
  • ID(country__c) retrieves the record ID as text

Data Types

When defining a formula, be sure that the operators, functions, fields, and values in the formula match the field’s data type. For example, you cannot multiply text values, but if a text field contains numbers, you can convert the string to a number value before multiplying.

Converting Data Types

Vault automatically converts the following data types when used in a formula:

  • Picklist → Text (except in formula fields on objects or field defaults)
  • DateTime → Date (except in formula fields on objects or field defaults)

Vault provides functions that you can use to convert data types:

  • Date → Text: Text(date, format)
  • DateTime → Text: Text(datetime, format)
  • Number → Interval: Days(number), Months(number), Years(number)
  • Text → Number: Value(text)
  • Object Reference → Text: ID(object_record), Text(object_record)

Guidelines for Formulas

Use these guidelines when creating formula expressions in Vault.

Formula Expressions

Use these guidelines when writing your formula expression:

  • Decimal points are periods, regardless of your Vault’s locale.
  • The maximum formula length is 1,500 characters.
  • When a formula contains more than one expression, surround the expression in parentheses ().

Fields

Use these guidelines when adding document or object fields to a formula expression:

  • Use the field name (as shown in the Fields panel) rather than the field label. For example status__v rather than Status.
  • When referencing documents, field names include a prefix to indicate if they apply to documents (Document.) or to a document’s related object (document_product__vr.). When referencing an object field directly (not through a relationship), there is no prefix, but referencing an object’s related object requires the relationship name (campaign__cr.)
  • Field names are case sensitive.
  • When adding from the Fields panel, Vault automatically inserts the correct syntax.
  • You cannot reference a formula-type field in another formula expression.
  • Use system variables in the Fields panel to create fields that reference specific resources in your Vault, such as the Vault’s domain (@Vault.domain) timezone (@Vault.timezone) and ID (@Vault.id).

Functions

Use these guidelines for adding functions to a formula expression:

  • Function names are case sensitive.
  • Functions may have one or multiple arguments.
  • You can use a function inside of another function.
  • Some functions have a field argument and a value argument, where the value depends on the field. To enter a value, use the value label and place it inside of double quotes, for example, “Draft”.

Arithmetic Operators

Use these guidelines for adding arithmetic operators to a formula expression:

  • Standard order of operations applies: multiplication and division before addition and subtraction unless you override by using parentheses to group.
  • You can enter operators directly or use the buttons below the Formula field.
  • You can use math operations to add both numbers and number-type fields, for example, durationInValue(Document.status__v, “Draft”) + 5.

Time Zone Handling with Date Fields

Vault stores all values for Date and DateTime type fields in the database in UTC. When calculating a date difference using subtraction, we use the following rules for time zone conversions:

  • For calculations involving two Date fields, we do not perform a time zone conversion. Date fields are considered without a time.
  • For calculations involving two DateTime fields, we do not perform a time zone conversion. Both field values are stored in UTC, so the function is not affected by time zone. We perform a date-to-date calculation and round values to the nearest integer. When defined, document formula field values can include decimal places to accurately reflect date differences.
  • For calculations involving a Date field and a DateTime field, we convert the DateTime value from UTC to the Vault’s Default Time Zone setting and truncate the time portion of the value. We then perform a date to date calculation. Note that Vault does not account for daylight savings time.