Extended Formulas

Extended formulas are a more flexible and powerful mechanism for computing values than the formula mechanism used to make simple computations based on fields in the same record.

Extended formulas can rely on data that is external to the record’s own business object and can perform more advanced operations than just add, subtract, multiply, and divide. The system supports the following extended formula types:

  • Single-Hop Query: The query token refers to a query with a $$RECORDID$$ association filter.
  • Single-Hop Association Token: The association token refers to a field on an associated business object.
  • Multi-Hop Query: The query token refers to a query with an association filter referencing another query. This can reference queries n-levels deep until reaching a query with a $$RECORDID$$ association filter.
  • Multi-Business Object Query: The query token refers to a multiple business objects where a $$RECORDID$$ field filter is against one of the query’s associated (non-primary) business objects.
  • Multi-Hop Association Token: The association token refers to a field that is in a business object associated n-levels away from the business object in which the extended formula resides.

Extended formulas are available in the Data Modeler for fields. They also are available in the Workflow Builder for object maps and as part of Workflow Condition Builder criteria. At workflow runtime, a workflow field map does not map a value from the source record to the destination record when the target field has a formula defined in its Business Object Field definition. The target field value is calculated and derived from its Data Modeler formula mapping. If a formula field is grayed out on the Object Mapping form, it means that the formula map defined in the Data Modeler is used as the source value for this field. Note that a total field that adds up a sum field in a smart section is also considered a formula field for the purposes of this behavior. To have mapping executed instead of the mapping defined in the Data Modeler, you must remove the formula mapping defined for this field from the Data Modeler; then workflow mapping is applied to the field.

In the Workflow Builder, extended formulas can access fields in the source record of the object map. More information on object mapping can be found in the “Creating Workflows” chapter of Application Building. Extended formulas are available for fields of any data type.

Extended formulas can be used in the Workflow Condition Builder as criteria for conditions specifying when a workflow needs to decide which path to take, for example in Start conditions, Switch tasks, and Break tasks. More information on the Workflow Condition Builder can be found in the “Workflow Tasks” section of the “Creating Workflows” chapter of Application Building.

An extended formula is always calculated for a particular record. The system determines the active project for the extended formula calculation as follows:

  • If the record that the formula is being calculated for is a capital project, the active project for that extended formula calculation is that record itself.
  • If the formula is being calculated in a capital project record, the active project for that extended formula calculation is that record itself.
  • Whether or not the active project is used in the extended formula calculation is dictated by the data scope on the query definition of the extended formula’s individual query tokens. If the data scope of the query definition is Active Project, the records returned by the query for the calculation will be restricted to the active project as defined above. If the data scope of the query definition is not Active Project, the active project as defined above is of no consequence.

At runtime, if all parameters in an extended formula are not defined, the system continues processing as though the extended formula did not exist and puts an entry in the log.

An extended formula with a query association filter can follow the association or the reverse association path.

The rest of this chapter focuses on how to create extended formulas from within the Data Modeler.

In the Data Modeler, extended formulas can access fields in any record, so long as there is a sequence of associations that connect the record that contains the formula’s field to the record that the formula accesses. Extended formulas are available for fields with data type of Number, Date, or any other data type with a Formula check box in its properties.