# 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:

- In the
**Entry Actions**page, select the**Update Record Field**action and a object record field to update. - Click the calculator icon to open the formula builder.
- 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. - Click
**Validate**. Vault will let you know if your expression is valid. - View the
**Advanced Settings**by clicking on the heading. - Optional: Choose a type of
**Run-Time Error Handling**. This dictates what occurs when there are errors that prevent Vault from executing the formula. - Optional: Choose an option for
**Blank Value Handling**. This dictates how Vault handles blank field values within the formula. - 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:

- Click into the
*Update Record Field*workflow step. - From the step’s
**Actions**menu, click**Edit**. - Select a
**Field**to update in the workflow step. - Click the calculator icon to open the formula builder.
- 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. - Click
**Validate**. Vault will let you know if your expression is valid. - 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.