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 creating a formula field for a report, the Blank Field Handling setting allows you to select how Vault treats blank fields when evaluating an expression in a report.
When the Blank Field Handling option is set to Treat blank values as blanks, one blank field value causes the entire expression to return a null/blank value. For example, Concat("Hello",documents.title__v) returns a blank value when the Document Title field is blank.
When the Blank Field Handling option is set to Treat blank values as zeroes and empty strings, Vault treats a blank field value as a zero or an empty string when evaluating the expression. For example, Concat("Hello",documents.title__v) returns Hello when the Document Title field is blank.
See the example below for the formula expression Document.days_in_draft__c + Document.days_pending_qc__c:
| Document | Days in Draft | Days Pending QC | Result: Treat Blank Values as Blanks | Result: Treat Blank Values as Zeroes or Empty Strings |
|---|---|---|---|---|
| Document A | 12 | 5 | 17 | 17 |
| Document B | 5 | 0 | 5 | 5 |
| Document C | 9 | blank | blank | 9 |
Functions return a blank value if the field evaluated has never had the value specified, regardless of the Blank Field Handling setting. For example, the function firstTimeinState(Document.status__v, "Pending QC") returns a blank for Document C because the document has never been in the Pending QC status.
The isBlank() function always returns false if Blank Field Handling is set to Treat blank values as zeroes and empty strings.
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")