Node - Calculation
Node: The result calculated through this node is used by subsequent nodes, and the node itself does not directly modify field values.
Differences between [Calculation] node and [Formula] control:
In the [Calculation] node, the fields involved in the calculation can be from records in different worksheets, while in the [Formula] control, only fields from the current worksheet can be used for calculation.
In the [Calculation] node, the calculation is done after editing and saving the field through the workflow, whereas in the [Formula] control, the result is directly calculated when editing the field.
Supported operations:
- Mathematical calculations for numbers and amounts
- Duration between two dates
- Add or subtract a time from/to a date
- Function calculations
Save calculation results:
Direct calculation
When the workflow reaches the [Calculation] node, it calculates and saves the result for subsequent nodes to use. Even if the field values involved in the calculation change, the result will not change.
Dynamic calculation
When the workflow reaches the [Calculation] node, it does not calculate immediately. The calculation is done in real-time when reaching nodes that use the calculation result. If the field values involved in the calculation change, the result will also change.
1. Number/Amount calculation
For example, to add a new order, query the latest order number and automatically number the new order.
Workflow Configuration
1. Add a [Calculation] Node and select [Numerical Operation]
2. Select the required fields
You can choose number or amount fields from previous nodes.
You can also input numbers.
3. Set the formula
You can use +, -, *, /, () operators, or choose system formulas.
Custom Formula
System Formula
Tips
It defaults to two decimal places (rounded).
When using system formulas, separate fields with commas. When editing a custom formula, operators (+ * /) are required.
If a field involved in the calculation is deleted, the result will be empty.
4. Treat empty fields as 0
When editing a custom formula like "Number Field 1 + Number Field 2 + Number Field 3," if one field is empty, the result will also be empty. Check [If the field value involved is null, it will be considered as 0.] to ensure normal calculation.
If the field used as a denominator is empty, the result will always be empty.
5. Use the calculation result in subsequent nodes
If the calculation result may be empty, set branch flows based on the result.
2. Add/Subtract a time to/from a date
A: Select a date field.
B: Set the format, date or date/time.
- Date: For example, if it is 2021-11-11 12:50, the date used in the calculation is 2021-11-11.
- Date/Time: The date and time used in the calculation is 2021-11-11 12:50.
C: Set the formula, to add 9 hours to the date.
D: Output the calculation result based on the selected format.
Tips:
Formulas must start with an operator symbol (+/-).
Y/M/d represent year/month/day, h represents hour, m represents minute. M and m are case sensitive.
There must be an operator symbol (+/-) before the number and a unit (Y/M/d/h/m) after the number, like +8h, +1m.
The unit (Y/M/d/h/m) must be placed after the number field.
When selecting a number field from another node, write the unit without any symbols in between, as shown below.
Example 1: Calculation Format: Date
Input date: 2020-10-12 13:50
Calculation format: Date
Formula: +7d+3h-2m
The date used in the calculation is 2020-10-12 00:00. Add 7 days and 3 hours, and subtract 2 minutes from that date.
Result: 2020-10-19 02:48
Example 2: Calculation Format: Date/Time
Input date: 2020-10-12 13:50
Calculation format: Date/Time
Formula: +3h
Result: 2020-10-12 16:50
Example 3: Get 8:00 a.m. of the day
As shown, input the current system time and the time at the current node is 2020-12-21 14:37.
Calculation format: Date. The date used in the calculation is 2020-12-21 00:00.
Formula: +8h. This means adding 8 hours to the date to get 2020-12-21 08:00.
3. Duration between two dates
Select the fields for the start date and end date, and set the format for the two dates in calculation.
Format 1: Start date 0:00, end date 24:00
Format 2: Start date 0:00, end date 00:00
Formatting is only available for dates without a time part. If there is a time part, the calculation will be based on the actual time involved.
Example 1
Start date 2020-12-13, end date 2020-12-14, unit: day
If using format 1, the dates for calculation are 2020-12-13 00:00 and 2020-12-14 24:00, and the result is 2 days.
If using format 2, the dates for calculation are 2020-12-13 00:00 and 2020-12-14 00:00, and the result is 1 day.
Example 2
Start date 2020-12-13, end date 2020-12-14 12:00, unit: hour
If using format 1, the dates for calculation are 2020-12-13 00:00 and 2020-12-14 12:00, and the result is 36 hours.
If using format 2, the dates for calculation are 2020-12-13 00:00 and 2020-12-14 12:00, the result is still 36 hours. This is because the end date has a time part, so the calculation is based on the actual time and only the start date is formatted.
4. Functions
- Use the YEAR() function to get the year of a date.
- Use the REPLACE() function to remove the +86 from a phone number.
- Use the DISTANCE() function to get the distance between two positioning fields.
Example: Remove +86 from a phone number with a function.
Workflow Configuration
1. Add a [Calculation] node and select [Function Calculation]
2. Edit the function
3. Select the output type
4. Use the calculation result in subsequent nodes
Tips
- In addition to functions, formulas also support custom calculation rules. When using custom calculation rules, if the fields involved in the calculation are deleted or empty, the result will be empty. In this case, please use built-in functions, which treat empty fields as 0 by default.
- If the calculation result failed to convert to the selected output type, the result will be empty. For example, if the calculation result of 1+1 is 2, but it is selected to be output as a date type, the result will be empty.
Have questions about this article？ Send us feedback