Some important questions about your documents and processes cannot be answered simply by reporting on the values of document fields. Your organization may need to measure a value that can only be derived from a combination of multiple fields or by analyzing the history of field values.
You can do this by creating a special formula-type field and using it in reports. Formula fields use an Excel-like formula language to calculate a result from other field values, a set of built-in functions, and standard math operators.
Note: Formula fields on documents can only be used in reports.
For information about supported functions and operators, see Vault Formula Reference Guide: Document Reporting Metrics (PDF).
How to Create Formula Fields
To create a formula field:
- From Admin > Configuration > Document Fields, click on the document type where you would like to define this formula field.
- Click Add… and select New Field.
- Select Formula as the field type.
- Enter a label for the field. This label will appear when using the field as a report column, filter, or group by field.
- Set the Status. If Active, the field will be available as soon as you save. If Inactive, you’ll have to change the status before the field is available.
- Choose the number of Decimal Places that Vault should round to for the field’s final value.
- Use items from the Fields and Functions panels, plus math operators as needed, to create an expression in the Formula field. To move an item from these panels to the Formula field, highlight it and click the arrow icon. The Formula field functions like a basic text editor. Learn more about available functions and operators in the Vault Formula Reference Guide.
- Click Validate. Vault will let you know if your expression is valid.
- Choose a Field Value Handling option. Treat blank values as zeros allows Vault to perform calculations even if one or more values is blank. Treat blank values as blanks returns a null or invalid result if the formula contains blank values. Learn more about Blank Value Handling.
- Optional: Enter text for Help Content. This text appears when a user hovers over the document field name.
- Click Save.
Note: In the 24R3 release, only Document State Type and Document Status are available for selection from the Fields panel when the following functions are used:
DurationInValue()NumTimesInValue()FirstTimeInValue()LastTimeInValue()PreviousValue()
Formula fields created prior to 24R3 that use other field types with these functions will operate as normal.
Blank Value Handling
When using the Treat blank values as blanks option for Field Value Handling, one blank field value causes the entire expression to return a null/blank value. See the example below for the formula expression Document.days_in_draft__c + Document.days_pending_qc__c.
| Days in Draft | Days Pending QC | Result |
|---|---|---|
| 12 | 5 | 17 |
| 5 | 0 | 5 |
| 9 | blank | blank |
History functions return a blank value if the field evaluated has never had the value specified. For example, the function firstTimeinValue ( Document.status__v, "In MLR Approval") returns a blank if the document has never been in the In MLR Approval status.
Vault always treats blank values as blanks (not zeros) for a formula field if that formula contains multiple functions, for example, numTimesInValue(Document.status__v,"Draft") + numTimesInValue(Document.status\_\_v, "Pending QC"). You can bypass this by creating a separate formula field for each function and using those fields within the final formula field, for example, Times.in.draft__c + Times.in.pending.qc__c.
Time Zone Handling with Date Fields
Vault stores all values for Date and DateTime 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 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. For reports referencing these fields, values can include decimal places when defined 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. Vault does not account for daylight savings time.
Formula Examples
These common formula fields may be helpful for analyzing documents and processes in your Vault. You can copy the formula expression directly into the Formula text field.
- Approval Time (Total)
dateDiff(firstTimeInValue(Document.status__v, "Pending QC"), firstTimeInValue(Document.status__v, "Approved for Production"))- Approval Time (Production Time)
dateDiff(firstTimeInValue(Document.status__v, "Approved for Production"), firstTimeInValue(Document.status__v, "Approved for Distribution"))- Retired: Withdrawn Prior to Expiration
dateDiff(firstTimeInValue(Document.status__v,"Withdrawn"), Document.expiration_date__c)- Use with Filter: greater than 1
- Expired: Withdrawn On Expiration
dateDiff(firstTimeInValue(Document.status__v, "Withdrawn"), Document.expiration_date__c)- Use with Filter: equals 0
- Review Cycles
numTimesInValue(Document.status__v, "In MLR Review")