Defining rules that use mathematical formulas
You can create simple mathematical formulas in business rules to calculate field values (such as Total Cost). Only Integer, Real Number and Simple Text fields can be used for calculation results.
Formulas follow the standard order of operations. The supported operators are * (multiply), / (divide), + (add), and - (subtract). You can use parentheses to group operations. When the rule runs, the system validates the formula and displays an error message if it finds unmatched parentheses, parentheses in incorrect order (that is, a closing parenthesis prior to the opening parenthesis), parentheses with no data within them, and so on. You can save a rule with an invalid formula so make sure to test these rules in a working environment to validate them.
You can use variables for any field (of any type) in a formula. In production, the system will try to convert values for the variables to numbers. If some value cannot be converted, then the system generates an error.
Formulas and dependent field groups
Because fields used in dependent field groups contain text, mathematical formulas may not work with them. For more information about dependent field groups, see Creating dependent field groups.
The following topics are provided:
In this example, we want to add the values in two fields and place that value in a third field. That formula would look as follows: ([Field1]+[Field2]). The result would be placed in the Result field that you select.
Another example might be to calculate tax on a sold item. That formula might look at follows: (Cost*.07).
An example of a complex formula is attached to this topic.
To create a mathematical, formula, you must first create the integer and/or real number fields to be used in the formula, including a "result" field for the result of the formula. Once you have created the fields, you can create the mathematical formula.
To define a rule that includes a mathematical formula
- Open the container and item where you want to define the rule:
- Click the Administration tab.
- In the appropriate section, click the name of the container or click Manage to open the container Administration page. Then, double-click the container.
The container details page appears.
- In the left pane, click the item link (such as Record Definitions in workspaces).
The Items page appears.
- Double-click the item that you want to modify.
The Item details page appears.
- In the left pane, click Business Rules.
Icons for the rules associated with this item appear in the right pane.
- Click New Business Rule, and select the appropriate rule type.
The Rule Builder page appears. For detailed information about configuring different types of rules, see Types of business rules.
- In the Business Rule Name field, enter a descriptive name for this rule.
- (Optional) In the Description field, type a useful description for this field.
- In the Business Rule Schedule field, accept the default of Run Any Time, or select from custom schedules defined for this container.
- In the Triggers section, select a trigger and configure the details.
- In the Criteria section, define the criteria for this rule, following the instructions in Configuring business rules.
- In the Actions section:
- Click Add new action.
- In the Action field, select Calculate field value.
- In the next field, select the field where the result should be placed (such as Total Cost).
- In the Value field, type the formula for calculating the value for the result field. To use field variables in your formula, place your cursor where the field should be placed in the formula, select the field in the Field Variables field, and click Insert.
Variables appear in the format [field_xxx] where field_xxx is the internal name for the field that you selected as a variable.
- Review the formula to verify it is correct.
- Click Create.
- When you have finished configuring the rule, click Save.
Publish the container to implement your changes:
In the breadcrumb trail, click the container link.
Click Save and Publish.
A confirmation message appears.
The Publication Succeeded message appears, showing the number of errors and warnings.
- Make sure to verify that the rule performs as expected.