This article will explain how to sum the quantities of data with the same type/name in a subform when multiple potentially duplicate entries are entered, and then aggregate this data into another subform.

When to Use this Feature?

In department procurement scenarios, the person in charge consolidates the procurement needs of all department members and sums the quantity of the same procurement needs into a subform.
In BOM scenarios, where materials lists are split, the same raw materials and their quantities are aggregated together to facilitate material usage statistics.

How does it work?

First, aggregate the "Type + Product" and "Type + Product + Quantity" for each row in the subform.
In the main form, deduplicate "Type + Product" and retain only unique "Type + Product" combinations.
In the main form, calculate the quantity for each unique "Type + Product" to determine the number of rows in the subform.
In the main form, aggregate all "Type + Product + Quantity" combinations into a string for easier subsequent quantity calculations.
Split the "Type + Product" using characters, and based on the subform row number, extract the corresponding "Type + Product", then split by "+" to extract the type and product, where the first part is the procurement type, and the second part is the product.
Use the "Type + Product + Quantity" aggregation to split by "-" to extract the quantity for the corresponding product. Finally, sum the quantities using the SUM function to get the total procurement quantity.

What does it Look Like?

For the procurement demand aggregation scenario, the result is as follows:

Note: Due to the template installation limitation of a maximum of 100 records per form, when using the template, you must re-enter data following the steps in 2.2 "Subform Base Table Data Entry" to achieve the desired effect. Otherwise, the effect will not work as expected.

How to Set up?

It is recommended to first install the Single Subform Aggregation application template before proceeding. This article will explain the process using the example data in the template.

For the procurement demand aggregation scenario, the steps are as follows:

Step 1 Creating the form

Create a "Procurement Demand Form" with the following field settings:

Field Name	Field Type
Procurement Demand Entry	Subform
Procurement Type	Subfield - Dropdown
Procurement Product	Subfield - Dropdown
Quantity	Subfield - Number
Type + Product	Subfield - Single Line Text
Type + Product + Quantity	Subfield - Single Line Text
Type + Product Deduplication	Single Line Text
Row Count	Number
Type + Product + Quantity Summary	Single Line Text
Procurement Demand Summary	Subform
Serial Number	Subfield - Number
Procurement Type	Subfield - Single Line Text
Procurement Product	Subfield - Single Line Text
Quantity	Subfield - Number

2. Create a "Subform Base Table" auxiliary form with the following field settings:

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

Step 2 "Subform Base Table" Data Entry

In the "Subform Base Table >> Data Management >> Import" section, import the example data from the attached file: 200-row Subform Base Table Serial Number Complete.xlsx.
Ensure the data is correctly linked to the form fields as shown in the example.

Step 3 Setting Up Data Linking

Open the "Procurement Demand Form" form, select the entire "Procurement Demand Summary" subform, and under Field Properties >> Default Value, set the option to Data Linking. Then, click Data Link Settings:
In the data link settings page, configure the following:

Linked Form: "Subform Base Table"
Condition: Set "Total Rows" to equal "Row Count"
Trigger Link:
- Display the values from the "Subform" field in the "Procurement Demand Summary" field.
- Add the "Serial Number" subfield and display its value.
- Click Confirm to complete the setup.

Step 4 Setting Up Formulas

4.1 "Row Count" Formula Setup

Select the "Row Count" field, go to Field Properties >> Default Value, and set the default value to Formula Editing. Then, click Edit Formula:
In the formula editing page, set the formula for "Row Count" as follows:

COUNT(Select Product)

4.2 "Product" Formula Setup

Select the "Product" subfield, go to Field Properties >> Default Value, and set the default value to Formula Editing. Then, click Edit Formula:
In the formula editing page, set the formula for the "Product" field as follows:

CONCATENATE(Procurement Demand Entry.Procurement Type, "+", Procurement Demand Entry.Procurement Product)

3. Similarly, set formulas for other fields as follows:

Field Name	Formula	Formula Meaning
Procurement Demand Entry.Type + Product + Quantity	`CONCATENATE(Procurement Demand Entry.Procurement Type, "+", Procurement Demand Entry.Procurement Product, "+", Procurement Demand Entry.Quantity, "-")`	Concatenate procurement type, product, quantity, and delimiters
Type + Product Deduplication	`UNION(Procurement Demand Entry.Type + Product)`	Deduplicate "Type + Product" and retain unique combinations
Row Count	`COUNT(UNION(Procurement Demand Entry.Type + Product))`	Count the number of unique "Type + Product" combinations
Type + Product + Quantity Summary	`CONCATENATE(Procurement Demand Entry.Type + Product + Quantity)`	Concatenate all "Type + Product + Quantity" combinations
Procurement Demand Summary.Procurement Type	`SPLIT(TEXT(SPLIT(Type + Product Deduplication, ",")[Procurement Demand Summary.Serial Number - 1]), "+")[0]`	Extract the procurement type from the deduplicated "Type + Product"
Procurement Demand Summary.Procurement Product	`SPLIT(TEXT(SPLIT(Type + Product Deduplication, ",")[Procurement Demand Summary.Serial Number - 1]), "+")[1]`	Extract the procurement product from the deduplicated "Type + Product"
Procurement Demand Summary.Quantity	`SUM(SPLIT(CONCATENATE(SPLIT(Type + Product + Quantity Summary, CONCATENATE(Procurement Demand Summary.Procurement Type, "+", Procurement Demand Summary.Procurement Product, "+"))), "-"))`	Sum the quantities of the corresponding products by splitting and summing the values