This article explains how to aggregate multi-row data from multiple subforms into a single subform based on a specific field. This approach enables the consolidation and calculation of data across several subforms.

When to Use this Feature?

Employee or department annual evaluation scenarios: Employees or departments are evaluated across multiple dimensions. Scores from different subforms can be aggregated to calculate total scores for each dimension.
Inventory management scenarios: When the same batch of goods is stored across different warehouses, multiple subforms can be used to calculate total inbound quantities for each warehouse.

How does it work?

In each subform to be aggregated, combine the fields in each row to form “Department + Deduction”.
In the main form, deduplicate all “Department” values across subforms, keeping only unique departments.
Calculate the number of unique departments and use this value to determine the number of rows in the summary subform.
Aggregate all “Department + Deduction” values into a single string for subsequent calculations.
Split the deduplicated “Department” values and populate them into each row of the summary subform.
Split the “Department + Deduction Summary” using the department name and the delimiters “+” and “, then use the SUM function to calculate the total deduction for each department.

What does it Look Like?

In the Department Evaluation Form, enter department names and deduction values across different evaluation dimensions. The system will automatically aggregate deduction values for the same department in the summary subform, as shown below:
Click to experience the result

Note: Due to the template installation limit of 100 records per form, you must re-enter data following the steps in 2.2 “Subform Base Table Data Entry” after installing the template. Otherwise, the expected result cannot be achieved.

How to Set up?

Before proceeding, it is recommended to install the Multiple Subform Content Aggregation application template. This guide explains the process using the template’s sample data.

The following example uses a department evaluation scenario.

Step 1 Creating the form

1. Create a “Department Evaluation Form” with the following fields:

Field Name	Field Type
Sales Completion Rate	Subform
Department	Subfield – Dropdown
Deduction	Subfield – Number
Department + Deduction	Subfield – Single Line Text
Repurchase Completion Rate	Subform
Department	Subfield – Dropdown
Deduction	Subfield – Number
Department + Deduction	Subfield – Single Line Text
Service Quality Evaluation	Subform
Department	Subfield – Dropdown
Deduction	Subfield – Number
Department + Deduction	Subfield – Single Line Text
Department Deduplication	Single Line Text
Department Count	Number
Department + Deduction Summary	Single Line Text
Summary Table	Subform
Serial Number	Subfield – Number
Department	Subfield – Single Line Text
Deduction	Subfield – Number

2. Create an auxiliary “Subform Base Table” with the following fields:

Field Name	Field Type
Total Rows	Number
Subform	Subform
Serial Number	Subfield – Number
Number	Subfield – Number

Step 2 Subform Base Table Data Entry

Go to Subform Base Table >> Data Management >> Import, and import the sample file:
200-row Subform Base Table Serial Number Complete.xlsx
During import, confirm that the sample data is correctly mapped to the corresponding form fields.

Step 3 Configuring Data Linking

Open the Department Evaluation Form, select the entire Summary Table subform, and under Field Properties >> Default Value, choose Data Linking, then click Data Link Settings.
Configure the data linking as follows:

Linked Form: Subform Base Table
Condition: Total Rows = Department Count
Triggered Actions:
- Display the Subform field value in the Summary Table field
- Add the Serial Number subfield and display its value
Click Confirm to complete the setup.

Step 4 Formula Configuration

1. Sales Completion Rate.Department + Deduction

Set the default value to Formula Editing, then configure the formula as:

CONCATENATE(Sales Completion Rate.Department, "+", Sales Completion Rate.Deduction, ",")

This concatenates the department, deduction, and delimiter for each row in the Sales Completion Rate subform.

Click Confirm to save.

2. Configure other fields using the following formulas

Field Name	Formula	Description
Repurchase Completion Rate.Department + Deduction	`CONCATENATE(Repurchase Completion Rate.Department,"+",Repurchase Completion Rate.Deduction,",")`	Concatenates department and deduction for each row
Service Quality Evaluation.Department + Deduction	`CONCATENATE(Service Quality Evaluation.Department,"+",Service Quality Evaluation.Deduction,",")`	Concatenates department and deduction for each row
Department Deduplication	`UNION(Sales Completion Rate.Department, Repurchase Completion Rate.Department, Service Quality Evaluation.Department)`	Deduplicates department names across all subforms
Department Count	`COUNT(SPLIT(Department Deduplication, ","))`	Counts unique departments for summary row generation
Department + Deduction Summary	`CONCATENATE(Sales Completion Rate.Department + Deduction, Repurchase Completion Rate.Department + Deduction, Service Quality Evaluation.Department + Deduction)`	Aggregates all department + deduction data
Summary Table.Department	`SPLIT(Department Deduplication, ",")[Summary Table.Serial Number - 1]`	Assigns department names to summary rows
Summary Table.Deduction	`SUM(SPLIT(CONCATENATE(SPLIT(Department + Deduction Summary, CONCATENATE(Summary Table.Department,"+"))), ","))`	Calculates total deduction per department