Skip to main content

Node - Rollup

Function: This node can help to sum up the data in the worksheet that meets the conditions, such as the number of records, or to find the sum, average, maximum, minimum of fields, and so on. Note that this node may have some delay when the data is changed frequently.

Use Case:

  • Query the details of an order from the Order Details worksheet and sum up the order subtotals.

  • Query work orders from the Work Order worksheet for the last month and get the average response time.

  • Query the work order records in the last month and get the total number of records.

Rollup Method​

There are two ways:

  • Summary from worksheet

    It can get the number of records, the maximum, the minimum and the average of fields, and the number of filled and unfilled records.

  • Get the number of data items

    After the [Get Multiple Data] node gets multiple records or data, this node can get the number of items of data.

There is no limit to the number in both the above ways.

I. Summary from worksheet​

It can query matched records from a worksheet, and get the number of records, the maximum, the minimum and the average of fields, and the number of filled and unfilled records. There is no limit to the number of records.

Example 1:Get the number and average duration of work orders (Maintenance) in the last month​

  • 1. Add the [Rollup] node and select [Summary from worksheet]

  • 2. Get the number of responded work orders

    Add filters to get the target records and sum up the number of records.

  • 3. Add another [Rollup] node to get the average response time of work orders

    The filters are the same as in the previous node. Select [Response Duration] and [Average].

  • 4. Operation mode

    • Direct calculation

      Directly calculate and save the result when executing to the operation node for use in subsequent nodes. Even if the values of the fields involved in the calculation change, the result of the calculation no longer changes.

    • Dynamic calculation

      When executing to an operation node, it does not calculate first, and then calculates in real time when executing to a node that uses the result of the operation. If the values of the fields involved in the calculation change, the results obtained by different nodes may be different.

  • 5. Use the rollup result in subsequent nodes

    In the subsequent nodes, you can select the result of the rollup node if needed.

Example 2: Sum up the order amount (sum of subtotal fields in Order Detail worksheet)​

If it is multiple records obtained through associated records, it cannot be summed up, but you can select the rollup node to query and sum it up directly.

  • 1. Add the [Rollup] node and select [Summary from worksheet]

  • 2. Query records in Order Detail

    • Query records directly from the worksheet. If it is a subform, it must be an entity sheet to be queried.

    • Filter: There is an association between the Order worksheet and the Order Detail worksheet, so when querying for records related to an order from the Order Detail worksheet, the filter is [Associated Field (Order) = Record ID]. As shown in the following, it means that the order (associated field) in the Order Detail worksheet is equal to the record ID of the order record.

  • 3. Select the target field

    Select the [Subtotal] field in the Order Detail and [Sum] it.

  • 4. Use the rollup result in subsequent nodes

    In the subsequent nodes, you can select the result of the rollup node if needed.

II. Get the number of data items​

For this way, the object node can only be a [Get Multiple Data] node. The object to be summed up can be either records in a worksheet or an array of data returned by a code block node or an API request node.

Example: Get the number of uncompleted tasks in the project.

  • 1. Get multiple data through [Get Multiple Data] node

    Get uncompleted tasks in a project through the associated records.

  • 2. Add the [Rollup] node and select [Get the number of data items]

  • 3. Select the object to be summed up and return the results

    • Object: Select the previous [Get Uncompleted Tasks] node and get the number of these records.

    • [Limit return results by number of aggregated objects]

      For example, when querying records in the [Get Multiple Data] node, there are 135 rows that meet the condition, but in the [Get Multiple Data] node, you set to get up to 100 rows. If [Limit return results by number of aggregated objects] is checked here, the result is 100; if it is unchecked here, the result is 135.

  • 4. Use the rollup result

    You can write the results directly to the field or set branches depending on the results.

Have questions about this article？ Send us feedback