Setting Fields with Formulas


You can set fields using formulas through the Update Record Field object lifecycle state entry action and object workflow step. These options enable you to set the value of an object field using an Excel™-like formula language. The language includes functions and operators that allow you to calculate dates and text strings, as well as numbers. 

Some example uses include:

  • Calculating a periodic review date by adding day, month, or year offsets from another date
  • Incrementing a field like Times in Review by adding one (1) each time the object record enters the In Review state

Vault provides the same formula language, with different options, for formula fields on objects and object field defaults.

For information about formula syntax and supported functions and operators, see the Vault CDMS Formula Reference Guide

How to Configure the Update Record Field Entry Action

The Update Record Field entry action allows you to set the value of an object field during a state change. To configure an entry action that uses the Update Record Field entry action:

  1. In the Entry Actions page, select the Update Record Field action and a object record field to update.
  2. Click the calculator icon to open the formula builder.
  3. Use items from the Fields and Functions lists, plus math/text operators, to create an expression in the formula field. To move an item from these panels to the formula field, double click on it. The formula field operates like a basic text editor. Learn about functions and valid operators in the Vault CDMS Formula Reference Guide.
  4. Click Validate. Vault will let you know if your expression is valid.
  5. View the Advanced Settings by clicking on the heading.
  6. Optional: Choose a type of Run-Time Error Handling. This dictates what occurs when there are errors that prevent Vault from executing the formula. 
  7. Optional: Choose an option for Blank Value Handling. This dictates how Vault handles blank field values within the formula. 
  8. Click Save to close the formula builder.

How to Configure an Update Record Field Step

To configure an Update Record Field workflow step using the formula builder:

  1. Click into the Update Record Field workflow step. 
  2. From the step’s Actions menu, click Edit.
  3. Select a Field to update in the workflow step.
  4. Click the calculator icon to open the formula builder.
  5. Use items from the Fields and Functions lists, plus math/text operators, to create an expression in the formula field. To move an item from these panels to the formula field, double click on it. The formula field operates like a basic text editor. Learn about functions and valid operators in the Vault CDMS Formula Reference Guide.
  6. Click Validate. Vault will let you know if your expression is valid.
  7. Click Save to close the formula builder.

Supported Field Types

You can only update Date, Number, and Text fields using an entry action or update record field step to set a field with a formula. When defining the formula, be sure that the operators, functions, fields, and values in the formula match the field’s data type

Run-Time Error Handling

These options control what occurs when vault cannot solve the formula expression. For each entry action, you can choose the type of error handling to apply:

  • Strict Handling: If there are any errors when calculating the value, Vault does not allow the record to change states. Instead, the record stays in its original state. If the state change is part of a workflow, the workflow cannot progress.
  • Flexible Handling: If there are errors when calculating the value, Vault modifies the results to allow the formula calculation to complete. This means that Vault may set the field value to “0” or blank.
Error Result with Flexible Handling
Calculated value is over the field's maximum value Set the field to the maximum value
Calculated value is under the field's minimum value Set the field to the minimum value
Calculated text value is over the field's maximum length Truncate the value at the maximum length
Calculation results in division by zero Division results in zero
Calculation results in a blank value for a required field Ignore the "Required" setting

Blank Value Handling

This dictates how Vault handles blank field values within the formula:

  • With Treat blank values as zeros, Vault substitutes a zero for the blank value, allowing you to complete the formula calculation.
  • With Treat blank values as blanks, one blank field value causes the entire expression to return a null/blank value.

Example

Formula: Object.days_in_review__c + Object.days_pending_review__c

Days in Draft Days Pending QC "Blanks as zeros" Result "Blanks as blanks" Result
5 0 5 5
9 blank 9 blank

Blanks in Multi-Function Formulas

Vault always treats blank values as blanks (not zeros) for a formula field if that formula contains multiple functions, for example, numTimesInValue(Object.status__v,”Draft”) + numTimesInValue(Object.status__v, “Pending Review”).

Time Zone Handling with Date Fields

Vault stores all values for Date and Date/Time type fields in the database in UTC. When calculating a dateDiff value, 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 Date/Time 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.
  • For calculations involving a Date field and a Date/Time field, we convert the _Date/Time _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.