Configuring Formula Fields on Objects

A Formula field calculates field values based on a formula expression entered during field configuration. This allows Admins to automatically derive a field’s value based on other data. Formula field values are never editable; they simply display the calculated value.

Because formula fields are calculated, rather than entered, they are not indexed, so you cannot search or filter on formula field values. Vault does not include formula field values in audit histories or audit trails.

Create a Formula Field

You can add formula fields to both custom and standard objects.

To add a formula field to an object:

  1. Follow the basic steps for field creation described in Configuring Vault Objects.
  2. Select Formula as the Field Type.
  3. Select a Return Type.
  4. Enter a Formula Expression. You cannot reference another formula field in your formula expression.
  5. Optional: If previously enabled, modify the Blank Field Handling setting, which is set to Treat blank fields as blanks by default. You can change this by selecting Treat blank fields as zeros and empty strings.
  6. Enter a Format Mask. To add a format mask, the return type must be Text or Number.
  7. Click Save.

For more detailed instructions on how to write a formula expression, see Creating Formulas in Vault.

Return Types

Formula fields on objects support the following data types:

The Return Type must match the data type returned by your Formula Expression. Some return types allow additional options, such as Minimum value or Maximum value for numbers. Currency formula fields return a value in the selected Currency Type.

Blank Field Handling

By default, Vault treats blank fields in formula expressions as blanks.

While adding or editing a formula field, select the Treat blank fields as zeros and empty strings option to modify the behavior for several functions and operators as follows:

Concat, &
Concat ignores all nulls and blanks, and only returns a blank if all arguments passed to it are nulls and/or blanks.
For example, Concat(field1, field2) where field1 = Hello and field2 = blank returns Hello.
Sum, Average, Min, Max
Average, Min, and Max ignore blanks. There is no difference between Sum ignoring blanks or treating them as zeros (0).
If you pass all blanks to these functions, Vault returns zero (0).
+, -
When working with two (2) numerical fields, + treats blanks and nulls on both sides as zeros (0), and returns zero (0) if both arguments are null and/or blank.
When working with date or datetimes as the left argument with + or -, Vault returns a blank or null result if the field is blank or null. If only the right argument is null or blank, Vault returns the original date or datetime. Expressions such as Date/DateTime - Date/DateTime and Time - Time return null if any field is blank.
*, ^, /
These operators handle blanks as zeros (0).
Includes
Includes returns false if one argument is blank, and returns null if both arguments are blank.
And, &&
These functions treat blank fields as false.
Contains
Contains returns false if one argument is blank, and returns null if both arguments are blank.
>, <, \>=, <=
These operators handle blanks as zeros (0) in expressions with at least one (1) number. If an expression does not contain at least one (1) number, Vault returns null.
Comparisons to null or blank fields returns null.
||, OR
These operators treat blanks as false, unless all operands are null.
IsChanged
This function returns false if both values are null.

Example: Incremental Task Due Dates

You can configure a formula field that increments a User Task record’s due date. The formula below adds a date seven (7) days from the Current Due Date field value.

current_due_date__c + Days(7)

Using Formula Fields for Icons

You can display icons on object records using a formula field. When configured, a formula field can display as an icon based on the formula’s evaluation. Icons display on object record list views and the object record details page.

To configure a formula field that displays an icon:

  1. Create a Formula type field and select Icon as the Return Type.
  2. Click the Calculator icon in the Formula Expression field.
  3. Enter your Formula Expression using the Icon(name, color, text) function. When using the Vault formula editor, select your icon from Icons. See Vault Supported Icons.
  4. To show an icon in the header of an object record details page, select Show in header. To indicate whether the icon should display left- or right-aligned in the header, select Left or Right. Note that only one icon can display in the header per object.
  5. Click Validate. If there are errors in your formula expression, correct them.
  6. Click Save.

Example: Status Icons

You may want to display a status icon to indicate completion status. In the example below, Vault displays a different status icon based on a Completeness picklist value.

If(completeness__v = Picklist.completeness__v.complete__v, Icon("circle", "#00C345", "Complete"), If(completeness__v = Picklist.completeness__v.inprogress__v), Icon("adjust", "#FFA60C", "Adjust"), Icon("circle-o", "#FA2819", "In Progress")))

Alternate Text

The “text” argument in the icon (name, color, text) formula is optional and allows you to provide alternate text for users. When users hover their cursor on the icon, this text shows up to help explain what the icon represents. If you do not provide a value, Vault shows the default alternate text for the icon.

When exporting records, the alternate text is also exported.

Using the Time Return Type

You can add or subtract numbers from the Time return type. Vault interprets integers as minutes and seconds as decimals of up to three (3) decimal places. For example:

  • 1.000 = one (1) minute
  • 0.017 = one (1) second
  • 1.017 = one (1) minute and one (1) second
  • "event_time__c + 480" adds eight (8) hours to the event time.
  • "event_time__c - 120" removes two (2) hours from the event time.

Adding or subtracting 1440 does not change the time. Adding or subtracting a value greater than 1440 loops around the 24 hours. For example, event_time__c + 1441 adds 1 minute to the event time. You can calculate the difference between two Time return types only by using the subtract operator (-). 00:00:00 is the smallest value while 23:59:59 is the largest value. For example:

  • "00:00:00" - "23:59:59" = -1439.983
  • "23:59:59" - "00:00:00" = 1439.983
  • "event_start_time__c - event_end_time__c" returns a negative number, if the event ended before midnight
  • "event_end_time__c - event_start_time__c" returns a positive number, if the event ended before midnight.
  • "event_end_time__c + event_start_time__c" returns the error message: “Can only add numbers to a Time field”

You can use all logical operators on formula expressions with two Time fields. All DateTime functions can use the Time return type.

Available Functions & Operators

For details on available functions and operators, see the Vault Formula Reference Guide.

Limits

By default, Vault allows you to configure up to 25 formula fields on each object. If your configuration requires more, contact Veeva Support.

You can complete all steps in this article with the standard System Admin or Vault Owner security profile.

If you Vault uses custom security profiles, your profile must grant the following permissions:

Type Permission Label Controls
Security Profile Admin: Configuration: Objects: Edit Ability to edit Vault object configuration in order to create a formula field

Learn more about permissions.