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 purchase request aggregation scenario, the result is as follows:

How to Set up?

For the purchase request aggregation scenario, the steps are as follows:

Step 1 Creating the form

Create a new "Purchase Request Form" with the following field settings:

Field Name	Field Type
Purchase Request Entry	Subform
Purchase Type	Subfield - Dropdown
Purchased 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
Purchase Request Summary	Subform
Serial No.	Subfield - Number
Purchase Type	Subfield - Single Line Text
Purchased 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 No.	Subfield - Number
Number	Subfield - Number

Step 2 Importing data for Subform Base Table

In the Subform Base Table >>Manage Data >> Import, 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 Linkage

Open the Purchase Request Form, select the entire Purchase Request Summary subform. Under Field Properties >> Initial Value, click Data Linkage and set Data Linkage 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 "Purchase Request Summary" field.
- Add the "Serial No." subfield and display its value.
- Click Confirm to complete the setup.

Step 4 Setting Up Formulas

Select the "Purchase Request Entry. Type + Product" field, go to Field Properties >> Initial Value>> Formula>>fx Edit.

In the formula editor, set the formula for "Purchase Request Entry. Type + Product" to:

CONCATENATE(Purchase Request Entry.Purchase Type, "+", Purchase Request Entry.Purchased Product)

This formula combines the Purchase Type, Purchased Product, and a string using the CONCATENATE function for each row in the subform.

Don't forget to click OK to save.

Using the same method, set the formulas for other fields as follows:

Field Name	Formula	Formula Meaning
Purchase Request Entry.Type + Product + Quantity	`CONCATENATE(Purchase Request Entry.Purchase Type, "+", Purchase Request Entry.Purchased Product, "+", Purchase Request Entry.Quantity, "-")`	This formula combines Purchase Type, Purchased Product, Quantity, and the strings "+" and "-" using the `CONCATENATE` function.
Type + Product Deduplication	`UNION(Purchase Request Entry.Type + Product)`	Deduplicates the Type + Product values in the subform, keeping only unique combinations of Type + Product.
Row Count	`COUNT(UNION(Purchase Request Entry.Type + Product))`	Calculates the number of unique Type + Product values, used as the row count for the subform.
Type + Product + Quantity Summary	`CONCATENATE(Purchase Request Entry.Type + Product + Quantity)`	Combines all Purchase Type, Product, and Quantity values from the subform using the `CONCATENATE` function.
Purchase Request Summary.Purchase Type	`SPLIT(TEXT(SPLIT(Type + Product Deduplication, ",")[Purchase Request Summary.Serial Number - 1]), "+")[0]`	Splits the deduplicated Type + Product by comma, selects the corresponding row based on the Serial Number, and extracts the first part (Purchase Type).
Purchase Request Summary.Purchased Product	`SPLIT(TEXT(SPLIT(Type + Product Deduplication, ",")[Purchase Request Summary.Serial Number - 1]), "+")[1]`	Similar to the above, but extracts the second part (Purchased Product).
Purchase Request Summary.Quantity	`SUM(SPLIT(CONCATENATE(SPLIT(Type + Product + Quantity Summary, CONCATENATE(Purchase Request Summary.Purchase Type, "+", Purchase Request Summary.Purchased Product, "+"))), "-"))`	Splits the Type + Product + Quantity Summary by the combination of Type + Product + "+", then splits by "-" to extract the Quantity values, and finally sums them up using the `SUM` function.