Control - Rollup
The rollup control can perform summary calculations on multiple related records or data in subforms, such as summing up a numeric field, calculating the average, counting the number of related records, or obtaining the latest date from a specific date field. This control greatly simplifies the process of data analysis and report generation, allowing users to avoid manual calculations or complex formula writing.
Example 1: In the order worksheet, a single order record may contain multiple order detail records (which can be stored in subforms or related records). In this case, it is necessary to automatically summarize the subtotal amount of each product in the order details directly on the order worksheet, in order to quickly determine the total amount of the order.
Example 2: In the project worksheet, a project often includes multiple sub-tasks. Project managers may need to real-time statistics on the number of tasks not yet completed, the number of tasks completed, and so on. Similarly, through the rollup control, project managers can quickly obtain these statistical data, thereby effectively monitoring the progress of the project.
For similar requirements, whether in financial management, project management, or any other case requiring data processing and analysis, the rollup control can easily achieve automatic summarization and calculation of data. This control not only improves work efficiency but also ensures the accuracy and consistency of data.
How to set a rollup field
Determine the data source for summarization: select the related record (multiple row) or subform in the current worksheet, specifying which records from the related worksheet to summarize.
Select the fields and method for summarization: from the related records or subform, choose the fields you wish to summarize and determine the method of summary. For example, you can select to sum up, calculate the average, find the maximum, minimum, etc. for a numeric field.
Set the summarization range: Specify the specific scope of the summarization, which may include a time range (such as records within a certain period), specific conditions (such as records that meet certain filter criteria), or all records.
Configure the display of the summarization results: set the display format for the summarization results.
Example Demonstration
Example 1: Summarizing the amount of order details in an order record
First go to the editing page of the order worksheet and add a rollup control.
Configuration Items
A Field Name:
Set a name for the summarization result that is easy to understand and identify, for example, "Total Amount."
B Related Worksheet:
Select the related worksheet to be summarized here. In this case, we choose the "Order Details" worksheet, which is related to the current order worksheet.
Only the related fields (multiple row) in the current worksheet can be selected.
C Rollup:
You need to select the field to be summarized and the method of summarization. For example, summing up the "Subtotal" field in the order details worksheet to obtain the total amount of the order.
For different types of fields, the system provides different summarization methods:
Amount/Numeric type fields: You can choose to sum up, find the maximum value, minimum value, or average value.
Time type fields: Obtain the earliest time or the latest time.
Other type fields: Count the number of filled and unfilled items.
D Summary scope:
In practical applications, you may not need to summarize all related records, but rather only summarize records that meet specific conditions. In this case, you can set the summarization range. For specific settings, please refer to the example 2 below.
E Display format of the summary results:
To display the summary results more intuitively, you can customize the display format. The system supports various display formats, including decimal places, thousand separators, and percentage display.
Display by percentage
If you wish to present the summarization results in percentage form, you can enable this option. For example, when the calculated result is 1, the system will automatically display it as 100%; when the calculated result is 0.99, it will display as 99%.
It is important to note that when percentage display is enabled, the system will no longer display units (prefix/suffix) to avoid information redundancy and confusion.
Example 2: Summarizing the number of incomplete tasks and their percentage under a project
First go to the editing page of the project worksheet and add a rollup control.
In the setting area of the rollup control, locate the "Summarization Scope" option and check "Set up the filter" to configure the filtering conditions of the records.
1. Set the summary scope
Filter conditions can be one or multiple.
If there are multiple conditions, you need to clarify the relationship between the conditions. All conditions must either be "AND" (indicating all conditions must be met) or "OR" (indicating any one condition can be met).
Note: For condition values, dynamic values ( variables) are not currently supported. For example, if you wish to filter tasks where "Time equals 'Today'," "Today" is a dynamically changing value.
If statistical analysis based on dynamic values is indeed needed, it is recommended to use other methods, such as creating charts.
2. Display of the summary result
After summarizing some data, to visually display the data more intuitively, you can choose to directly display the numerical value or show the percentage that value represents of the total data.
When the summarized content is "number of records" or "sum result of numeric/currencyfields," you may consider displaying the results in percentage form. This display method helps you quickly understand the proportion of a particular part within the whole.
The calculation formula for percentage is:
Percentage = Statistical value within the summarization range / Statistical value of all records × 100%
The Rollup field can be converted to Numeric field.
Update of Rollup Fields
When summarizing data, there are two update mechanisms: Immediate Update and Delayed Update. Updating here refers to updating the values in the database, not just refreshing the field values in the editing interface to display the latest values.
Let's take the Order worksheet (referred to as the "main worksheet") and the Order Details worksheet (referred to as the "related worksheet") as examples for details.
Immediate Update
When editing the related worksheet data in the main worksheet record, the rollup value is immediately updated.
Clicking the refresh button of the rollup field in the main worksheet, the rollup value is immediately updated.
After batch refreshing data in the worksheet, the rollup value is immediately updated.
After calibrating data through the Calibrate node in the workflow, the rollup value is immediately updated.
When updating the relationship between the main worksheet and the related worksheet through the workflow, such as adding or canceling a related record, the rollup value is immediately updated.
Delayed Update
- When opening and editing a record directly in the related worksheet, the rollup field in the main worksheet is queued for calculation and update, which may cause a delayed update.
- When updating the field values in the related worksheet through the workflow, the rollup field in the main worksheet is queued for calculation and update, which may cause a delayed update.
Due to the possibility of delayed updating of rollup values, if the workflow requires the use of rollup values, it is recommended to use the Calibrae node to refresh the rollup fields, or to obtain the latest rollup results through the Rollup node.
Recommended usage cases for Rollup control
Applicable cases
1. Small amount of data to be summarized (usually not exceeding one hundred records)
For example, when you need to summarize order amounts, expense details, cost accounting, or the number of customer leads in a small-scale dataset, using the rollup control can easily obtain the total sum, average, or other statistical information of these data.
2. Data changes infrequently, with a relatively large amount of data but usually not exceeding tens of thousands of records
For such scenarios, although the amount of data is relatively large, since data changes infrequently, the rollup control can still demonstrate its advantages. For example, when summarizing the number of participants in an event, gender ratio, actual attendance, etc., despite the potentially large number of participants, there is usually a time frame, and the data will not change further.
Have questions about this article？ Send us feedback