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:

How to Set up?

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 No.	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 No.	Subfield – Number
Number	Subfield – Number

Step 2 Subform Base Table Data Entry

Go to Subform Base Table >> Manage Data >> 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 Linkage

Open the Department Evaluation Form, select the entire Summary Table subform. Under Field Properties >> Initial Value, click Data Linkage and set Data Linkage Settings.
Configure the data linkage 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 No. subfield and display its value
Click OK to complete the setup.

Step 4 Setting Up Formulas

1. Select the Sales Completion Rate. Department + Deduction field, go to Field Properties >> Initial Value>> Formula>>fx Edit.

In the formula editor, set the formula for "Sales Completion Rate.Department + Deduction" to:

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 OK 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