Skip to main content

Type Formulas Overview

Updated

Type formulas let Level 1, Level 2, and Level 3 records in a UTA/module run advanced calculations and logical operations in their formula fields. Because the system processes these formulas on the server when a record is saved, they can populate or update fields without the repeated saving that a browser-based calculated field sometimes requires. Each level can hold formulas in two places: a general formula that applies to every record at that level, and a type formula that applies only to records of a specific type.

Who: Global administrators and UTA/module administrators

NOTE  Level 1 formulas were previously called template formulas. The current term for all levels is type formula.

What Are Type Formulas

A type formula is an expression stored in a formula field available in the UTA/module settings at the appropriate level tab, or within the process tab of type configuration. It can combine field values and logical conditions, then write the result back to a field on the same record or on a related record at another level.

Formulas can be set in two places for each level in a UTA/module:

  • The general formula, set on the Application Configuration page, applies to all records saved at that level.
  • The type formula, set on the Type page, applies only to records that use that specific type.

For example, to calculate values on fields at Level 3 and store the results in fields at Level 2, use the Level 2 formula. Any expression and a range of built-in functions are supported.

A type formula is often a better choice than a calculated field because the system runs it on the server rather than in the browser. When a calculation depends on a value that must be populated on the same Level 3 record, a type formula returns the final result without a second save.

IMPORTANT  Type formulas run on the server, so they should be used only when needed to help maintain system performance.

↑ Back to top

When to Use Type Formulas

In general, use type formulas when a calculation cannot be completed with a calculated field on the page, usually because it depends on a value that is not finalized until after the record is saved. For example, a type formula can update a field that is not on the current page at all, typically a field on a different level, such as updating a Level 1 field from a Level 2 record.

Use type formulas when:

  • A record name should build automatically from other fields, such as combining an organization name and program year, or a contact's first and last name.
  • Values entered at Level 3 need to total into a Level 2 field and roll up to Level 1, such as summing Level 3 budget line items into a Level 2 category and a Level 1 grant total.
  • Amounts on related Level 2 records need to total on the Level 1 record, such as a total paid and remaining balance from individual payment records.
  • The count of related records needs to show on a parent record, such as the number of submitted reports, reviewer forms, or line items.
  • A date needs a business-day calculation, such as a due date set a number of working days after a start or award date, excluding weekends and holidays.
  • A calculation should apply only to records of a specific type rather than to every record at that level.

↑ Back to top

How Type Formulas Work

Type formulas run when a record is saved or saved as a draft. The general formula on the Application Configuration page runs first, followed by the type formula for the record's type, if one is set.

Saving a Level 2 or Level 3 record also triggers the formulas on its parent levels. When a Level 3 record is deleted, the formulas for Level 2 and Level 1 also run.

Execution Sequence

The formulas run in this order for each level:

  • Level 1: the Level 1 formula runs, then the Level 1 type formula.
  • Level 2: the Level 2 formula, then the Level 2 type formula, then the Level 1 formula, then the Level 1 type formula.
  • Level 3: the Level 3 formula, then the Level 3 type formula, then the Level 2 formula, then the Level 2 type formula, then the Level 1 formula, then the Level 1 type formula.
NOTE  The Level 1 formula and Level 1 type formula run only if one of them references a Level 2 variable. The Level 2 formula and Level 2 type formula run only if one of them references a Level 3 variable.

↑ Back to top

Requirements

IMPORTANT  Type formulas run only when the Type standard field is enabled at the level. Enable the standard field before configuring a type formula.

↑ Back to top

Limitations

  • Status cannot be set with a type formula.
  • The Amount standard field cannot be set with a type formula.
  • The sum, max, and avg functions work only with the Amount standard field from Level 2 or Level 3.
  • Summing from Level 3 to Level 1 using a custom field ID is not supported. Aggregation is limited to the Amount and Duration fields.

↑ Back to top

Resources

For more information about type formulas, see:

↑ Back to top

Helpful Links