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 special formula-type fields 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.
Creating Formula Fields
Admins can create document report formula fields directly within the report configuration.
Admins can no longer create or edit document formula fields from Admin > Configuration > Document Fields. With 25R2, any document formula fields created in Admin > Configuration > Document Fields that do not use Document Status or State Type return blank values. Document formula fields that use Document Status and State Type continue to function as expected.
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)
firstTimeInValue(Document.status__v, "Approved for Production") - firstTimeInValue(Document.status__v, "Pending QC")- Approval Time (Production Time)
firstTimeInValue(Document.status__v, "Approved for Distribution") - firstTimeInValue(Document.status__v, "Approved for Production")- Retired: Withdrawn Prior to Expiration
Document.expiration_date__c - firstTimeInValue(Document.status__v,"Withdrawn")- Use with Filter: greater than 1
- Expired: Withdrawn On Expiration
Document.expiration_date__c - firstTimeInValue(Document.status__v, "Withdrawn")- Use with Filter: equals 0
- Review Cycles
numTimesInValue(Document.status__v, "In MLR Review")