Add Formula

Introduction

Feature Overview

Add Formula nodes can perform formula calculations or aggregations on the data stored in the preceding nodes within Automations Pro. The calculation results can be utilized by subsequent nodes.

Note:

Add Formula only stores calculation results, such as "6", the number, or "2024-06-08", the date, rather than storing a specific record.

Scenario

Add Formula nodes are suitable for scenarios where the workflow requires intermediate calculation for subsequent nodes. It supports both Formula Calculation and Aggregation:

Formula Calculation: Custom values, arithmetic operations (addition, subtraction, multiplication, division), math functions, logical functions, text functions, and date functions. For example, calculating profits, calculating the time difference, concatenating text, extracting key information, etc.
Aggregation: Summation, maximum value, minimum value, average value, and counting. For example, calculating total sales/employee performance, obtaining the number of customer complaints, etc.

Preview

1. Formula Calculation

Taking the example of updating employee information, we can trigger Automations Pro on the employee's start date, calculate their length of service, and annually update the corresponding field in the record:

2. Aggregation

In a customer order management scenario, when an order is submitted, Automations Pro automatically retrieves all historical order data for that customer. It then calculates the customer's total spending and writes this information back into the current order, helping sales staff to easily view and analyze customer value.

Setting Automations Pro

Click Extension > Automations > New Automation. Select a trigger, and, after Automations Pro is created, you will see the design canvas. Go on adding the action nodes that you need.

Adding Add Formula Nodes

Add an Add Formula node after the existing nodes according to actual needs.

Choosing a Method

At Add Formula, you can choose either of the calculating methods:

Formula Calculation: Supports formula calculations on single records from preceding nodes, including custom values, arithmetic operations (addition, subtraction, multiplication, division), and functions;
Aggregation: For multiple records from preceding nodes, it can aggregate the number of records. Alternatively, for a Number field among these records, it can perform aggregation calculations such as summation, maximum value, minimum value, and average value.

Setting Formula

1. Rules for Formula Calculation

Select the Add Formula node, choose Formula Calculation as the method, and click Add Formula to enter the editing page.

Set the formula, select the type for Calculation Result, and click OK.

Note:

1. For rules of editing formula, see Formula Rules.

2. Add Formula nodes support calculation results in types of Text, Number, and Date&Time.

3. You should set the output type of Calculation Result manually; the initial option is Number.

2. Rules for Aggregation

Select the Add Formula node, and choose Aggregation as the method:

Select a rule for the aggregation, and the field(s) to be aggregated. If the rule is Count (COUNT), select the node(s) for which you want to count the number of records.

Note:

1. Only Number fields from Query Records nodes can be selected to aggregate. When the rule is Count, select one of the following two types of nodes:

For preceding nodes storing multiple records, you can select the Number fields from them.
For preceding nodes storing a single record, which may include subform record(s), you can select the Number subfield(s).

2. When the calculation method is Aggregation, the Calculation Result is of the Number type and can be accessed by subsequent nodes.

Notes

General Instructions

1. When the trigger method is Scheduled Trigger > Custom Time, data from the trigger node cannot be referenced for calculations or aggregations.

2. If the Add Formula node is on a Path, it cannot reference nodes on other paths; if it is not on any Path, it cannot reference nodes on paths.

3. Calculation Result can be referenced by all types of subsequent execution nodes. For details, see Action Nodes.

Rule Setting Instructions

1. Add Record nodes only support formula calculating with data from the preceding node with a single record, including the trigger node, Query Record, Add Record (single record), Add Formula, and Add Plugin.

Note:

If the trigger is Scheduled Trigger > Custom Time, the trigger node cannot be quoted to assign values.

2. Add Record node supports custom value calculation, as well as math functions, logical functions, text functions, and date functions. Among them:

Supported parameters:

Single Line, Multi Line, Number, Date&Time, Radio, Single Select, Serial No., Member, Department, Created Time, Updated Time, URL Parameter, Checkbox, Multi Select.

Unsupported function:

Text functions: IP(), ISEMPTY(), UNION(), SPLIT()
Math functions: SUMIF(), SUMIFS(), SUMPRODUCT()
Advanced functions: GETUSERNAME(), MAPX(), RECNO(), TEXTPHONE()

3. When rules are being set, the compliance should be verified. The formula that does not comply with the requirements cannot be saved.