Aggregated Table

Overview

An Aggregated Table allows you to efficiently calculate data from multiple worksheets and generate a virtual table.

Aggregated Tables can be used as data sources for charts. In workflows, you can retrieve aggregated data through the Get Multiple Data node.

Aggregated Tables cannot currently be used as data sources for worksheets.

Example scenario:

In an inventory management application, there are four worksheets: Product, Stock-In, Stock-Out, and Returns.

An Aggregated Table is used to calculate the total purchased quantity, total shipped quantity, and total returned quantity for each product, and then calculate the current inventory level with real-time updates.

Aggregated Table data can also be displayed to users through pivot tables on custom pages.

Create an Aggregated Table

1. Open the Creation Entry

Application administrators can go to:

App Management > Aggregated Table

Then click Create Aggregated Table.

2. Select Data Sources

Select Worksheets

An Aggregated Table can calculate data from one or multiple worksheets.

Click + Worksheet and select the worksheets to include.

Each worksheet can only be selected once. You can also select worksheets from other applications.

Filter Data

Configure filter conditions for each worksheet to exclude unnecessary data from the aggregation calculation.

Only records that meet the filter conditions will be included in the calculation.

In this example, only products with the status Active are included.

3. Group Data

Add fields to the Aggregated Table and summarize data based on grouping fields.

In this example, data needs to be grouped by Product Name and Product Code.

Each worksheet must contain these two fields. The field names do not need to be identical, but they must represent the same business object.

Grouping fields can be renamed and reordered.

Show null: If Show null is disabled, records with empty values in any grouping field will not be displayed in the Aggregated Table.

When an array-type field is used as a dimension, you can choose whether to split or combine the statistics.

For example, the source data is:

Name	Interests (Multiple Select Field)
Alex	Basketball, Volleyball
Emma	Basketball, Tennis
Daniel	Football, Tennis

When aggregating by the Interests field with combined statistics:

Interest	Record Count
Basketball, Volleyball	1
Basketball, Tennis	1
Football, Tennis	1

When aggregating by the Interests field with split statistics:

Interest	Record Count
Basketball	2
Volleyball	1
Tennis	2
Football	1

4. Aggregate Data

Aggregation fields are fields generated by performing calculations on fields from source worksheets.

Aggregation fields support the following calculation methods:

Sum
Maximum
Minimum
Average
Count
Distinct count

You can also create a calculation field to calculate values based on other aggregation fields.

An Aggregated Table supports up to 20 aggregation fields.

Unable to Select a Field?

If a field name contains the special characters @@ or a backtick (`), the field cannot be selected as an aggregation field or synchronization field.

In this example, we need to calculate:

Total purchased quantity for each product
Total shipped quantity for each product
Total returned quantity for each product

Then, calculate the inventory quantity using a newly created calculation field.

Worksheet Fields

Click + Field, select the fields to calculate, and configure the aggregation method and data format.

Calculation Fields

Calculation fields can only calculate custom fields created in the Aggregated Table.

In this example, the inventory quantity is calculated based on:

Total purchased quantity
Total shipped quantity
Total returned quantity

5. Preview Data and Publish

After completing the configuration, click View Data and wait for the preview data to be generated.

After confirming that the data is correct, rename the Aggregated Table and publish it.

Once published, the aggregated data can be used by other modules.

Use Aggregated Data

In charts on custom pages, select an Aggregated Table as the data source.
The workflow Get Multiple Data node supports querying data from Aggregated Tables.

Only data queries are supported. You cannot update or delete aggregated data.