Skip to main content

Control - Rollup

The rollup control aggregates data from multiple related records or subform records, enabling calculations such as sum or average of number fields, count of related records, or the latest date from date fields.

Use Case 1: In the order worksheet, a single order record may contain multiple order details records (which can be stored in subforms or related records). The rollup control can automatically calculate subtotal amounts for each product in the order details.

Example 2: In the project worksheet, for projects with multiple subtasks, it can summarize the number of incomplete tasks or number of completed tasks.

All such requirements can be implemented using the rollup control.

How to set up a rollup field​

  1. Select a relationship field (multiple rows) or subform field in the current worksheet to specify which related records to aggregate.

  2. Choose the field to aggregate and the aggregation method.

  3. Set the scope of aggregation.

  4. Configure the display format for the aggregation result.

Example Demonstration​

Use Case 1: In an order record, aggregate the total amount from order detail records​

Go to the Order Worksheet editing page and add a Rollup control.

Field Configuration

  • A Field name: 

    Assign a name to the aggregation result (e.g., "Order Total").

  • B Related worksheet:

    Select the related worksheet to aggregate. In this case, choose the "Order Details" worksheet, which is related to the current order worksheet.

    Only the relationship fields (multiple rows) in the current worksheet can be selected.

  • C Rollup method:

    Choose the field and aggregation method (e.g., Sum for the "Subtotal" field in order details).

    For different types of fields, HAP provides different rollup methods:

    • Number/Currency fields: Sum, Max, Min, Average.

    • Date/Time fields: Earliest or latest date/time.

    • Other fields: Count of filled/unfilled items only.

  • D Rollup scope:

    Optionally filter records before aggregation (e.g., only include matching conditions). See use case 2 below for details.

  • E Display format of result:

    Customize the result format (decimal places, thousand separators, units, etc.).

    • Display as percentage

      If enabled, 1 → 100%; 0.99 → 99%.

    • Note: Units (prefix/suffix) are hidden if "Show as percentage" is enabled.

Use Case 2: Aggregate the count and percentage of finished tasks in a project record​

First go to the editing page of the Project Worksheet and add a Rollup control.

In "Summary scope", enable "Set up the filter" and configure the filtering criteria.

1. Filter related records

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 needed, it is recommended to use other methods, such as creating charts.

2. Display format of result

After aggregating some data, to display the result more intuitively, you can choose to directly display the number or show the percentage of total.

Percentage calculation:

Percentage = Aggregated Value / Total Value (All Records)

The Rollup field can be converted to Number field.

Number of aggregated records​

A rollup field can aggregate up to 1,000 related records or subform records. To aggregate all records:

  • Option 1: Open the record details page and click the refresh button next to the rollup field.

  • Option 2: Use the [Calibration] (/workflow/node-calibrate) node in workflows (calibrate single data).

Update of Rollup fields​

There are two update mechanism, Immediate Update and Delayed Update. Updating here refers to database-level refreshes, not just UI displays.

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

    Triggered when:

    • Editing the related worksheet data in the main worksheet record.

    • Clicking the refresh button of the rollup field in the main worksheet.

    • Batch refreshing worksheet data.

    • Calibrating data through the Calibrate node in the workflo.

    • Updating the relationship between the main worksheet and the related worksheet through the workflow, such as ralating or unrelating a record.

  • Delayed update

    Occurs 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.

    • 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.

tip

Due to the possibility of delayed updating of rollup field values, if the workflow involves the 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.

Suitable cases​

  • Small datasets (typically no more than 100 records)

  • Infrequently updated data, even if larger in volume (typically no more than 10000 records)

Unsuitable cases​

  • Large and continuously growing datasets (not recommended for rollup implementation)