When to Use It

A three-level BOM is suitable for production scenarios where the product structure is relatively complex, such as hardware products and home improvement products. It is intended for products that contain level-3 materials.

What Does It Look Like?

Enter the product name and the corresponding quantity in Product Details, and the required level-2 and level-3 materials and quantities will be calculated automatically.

How to Set It Up

Step 1 Building the Form

Create a form named Three-Level BOM Material Usage List and add the following fields:

Field Name	Field Type
Product Details	Subform
└ Product Name	Single Select
└ Quantity	Number
└ Level-2 Material Collection	Single Line
└ Level-2 Material Quantity Collection	Single Line
└ Material Count	Number
└ Quantity Repetition	Single Line
Product Quantity Repetition	Single Line
Total Level-2 Material Collection	Single Line
Total Level-2 Material Row Count	Number
Level-2 Material Quantity Collection	Single Line
Level-2 Material Usage	Subform
└ Serial No.	Number
└ Material Name	Single Line
└ Material Quantity	Number
└ Material Quantity per Product	Number
└ Product Quantity	Number
└ Level-3 Material Collection	Single Line
└ Level-3 Material Quantity Collection	Single Line
└ Level-3 Material Count	Number
└ Quantity Repetition	Single Line
Level-2 Material Quantity Repetition	Single Line
Total Level-3 Material Collection	Single Line
Total Level-3 Material Row Count	Number
Level-3 Material Quantity Collection	Single Line
Level-3 Material Usage	Subform
└ Serial No.	Number
└ Level-3 Material Name	Single Line
└ Level-3 Material Quantity	Number
└ Level-3 Material Quantity per Level-2 Material	Number
└ Level-2 Material Quantity	Number
└ Level-3 Material Name + Quantity	Single Line
Level-3 Material Name Deduplication	Single Line
Row Count	Number
Level-3 Material Name + Quantity Collection	Single Line
Material Usage Summary	Subform
└ Serial No.	Number
└ Level-3 Material Name	Single Line
└ Level-3 Material Quantity	Number

Step 2 Configuring Fields

1. Configuring Fields in the Product Details Subform

1) Select the Product Details > Product Name subfield, and manually add the product names as options. These product names should correspond to the main products entered in the BOM Basic Table of the BOM Material List.

2) Select the Product Details > Level-2 Material Collection subfield. Go to Field Properties > Initial Value, select Data Linkage, and click Data Linkage Settings.

Configure the settings as follows:

Linked Form: BOM Basic Table
Condition Settings: Main Product equals to Product Details > Product Name
Triggered Linkage: Product Details > Level-2 Material Collection shows the value of Sub-Item Collection

After all settings are completed, click OK.

3) In the same way, configure the Data Linkage rule for Product Details > Level-2 Material Quantity Collection as follows:

Linked Form: BOM Basic Table
Condition Settings: Main Product equals to Product Details > Product Name
Triggered Linkage: Product Details > Level-2 Material Quantity Collection shows the value of Quantity Collection

After all settings are completed, click OK.

4) In the same way, configure the Data Linkage rule for Product Details > Material Count as follows:

Linked Form: BOM Basic Table
Condition Settings: Main Product equals to Product Details > Product Name
Triggered Linkage: Product Details > Material Count shows the value of Sub-Item Count

After all settings are completed, click OK.

5) Select the Product Details > Quantity Repetition subfield. Go to Field Properties > Initial Value, select Formula, and click Edit Formula.

6) Set the formula for Product Details > Quantity Repetition as follows:

LEFT(REPT(CONCATENATE(TEXT(Product Details.Quantity),','),Product Details.Material Count),LEN(REPT(CONCATENATE(TEXT(Product Details.Quantity),','),Product Details.Material Count))-1)

This formula repeats the product quantity according to the number of materials required by the product, so that each material can be matched with the corresponding product quantity.

2. Configuring Main-Form Fields for Level-2 Material Usage

Following the same method for configuring formulas above, set formulas for the following four fields:

Field Name	Formula	Meaning
Product Quantity Repetition	`TEXT(Product Details.Quantity Repetition)`	Aggregates all text values in Product Details > Quantity Repetition.
Total Level-2 Material Collection	`TEXT(Product Details.Level-2 Material Collection)`	Aggregates all text values in Product Details > Level-2 Material Collection.
Total Level-2 Material Row Count	`COUNT(SPLIT(Total Level-2 Material Collection,','))`	Calculates the total number of materials contained in Total Level-2 Material Collection.
Level-2 Material Quantity Collection	`TEXT(Product Details.Level-2 Material Quantity Collection)`	Aggregates all text values in Product Details > Level-2 Material Quantity Collection.

3. Configuring Fields in the Level-2 Material Usage Subform

1) Select the entire Level-2 Material Usage subform. Go to Field Properties > Initial Value, select Data Linkage, and click Data Linkage Settings.

2) On the Data Linkage Settings page, configure the settings as follows:

Linked Form: Subform Base Table
Condition Settings: Total Rows equals to Total Level-2 Material Row Count
Triggered Linkage:
- Level-2 Material Usage shows the value of Subform
- Add the Serial No. subfield and set it to show the value of the No. subfield

After all settings are completed, click OK.

3) Set formulas for the following fields in Level-2 Material Usage:

Field Name	Formula	Meaning
Level-2 Material Usage > Material Name	`SPLIT(Total Level-2 Material Collection,',')[Level-2 Material Usage.Serial No.-1]`	Splits Total Level-2 Material Collection by commas and fills the corresponding row according to the serial number in the subform.
Level-2 Material Usage > Material Quantity	`Level-2 Material Usage.Material Quantity per Product*Level-2 Material Usage.Product Quantity`	Calculates the required material quantity based on the material quantity required for one product and the product quantity.
Level-2 Material Usage > Material Quantity per Product	`SPLIT(Level-2 Material Quantity Collection,',')[Level-2 Material Usage.Serial Number-1]`	Splits Level-2 Material Quantity Collection by commas and fills the corresponding row according to the serial number in the subform.
Level-2 Material Usage > Product Quantity	`SPLIT(Product Quantity Repetition,',')[Level-2 Material Usage.Serial Number-1]`	Splits Product Quantity Repetition by commas and fills the corresponding row according to the serial number in the subform.
Level-2 Material Usage > Quantity Repetition	`LEFT(REPT(CONCATENATE(TEXT(Level-2 Material Usage.Material Quantity),','),Level-2 Material Usage.Level-3 Material Count),LEN(REPT(CONCATENATE(TEXT(Level-2 Material Usage.Material Quantity),','),Level-2 Material Usage.Level-3 Material Count))-1)`	Repeats the material quantity according to the number of level-3 materials required by each level-2 material.

4) Configure Data Linkage for the following fields in Level-2 Material Usage:

Level-2 Material Usage > Level-3 Material Collection
- Linked Form: BOM Basic Table
- Condition Settings: Main Product equals Level-2 Material Usage > Material Name
- Triggered Linkage: Level-2 Material Usage > Level-3 Material Collection shows the value of Sub-Item Collection

Level-2 Material Usage > Level-3 Material Quantity Collection
- Linked Form: BOM Basic Table
- Condition Settings: Main Product equals Level-2 Material Usage > Material Name
- Triggered Linkage: Level-2 Material Usage > Level-3 Material Quantity Collection shows the value of Quantity Collection

Level-2 Material Usage > Level-3 Material Count
- Linked Form: BOM Basic Table
- Condition Settings: Main Product equals Level-2 Material Usage > Material Name
- Triggered Linkage: Level-2 Material Usage > Level-3 Material Count shows the value of Sub-Item Count

4. Configuring Main-Form Fields for Level-3 Material Usage

Following the same method for configuring formulas above, set formulas for the following four fields:

Field Name	Formula	Meaning
Level-2 Material Quantity Repetition	`TEXT(Level-2 Material Usage.Quantity Repetition)`	Aggregates all text values in Level-2 Material Usage > Quantity Repetition.
Total Level-3 Material Collection	`TEXT(Level-2 Material Usage.Level-3 Material Collection)`	Aggregates all text values in Level-2 Material Usage > Level-3 Material Collection.
Total Level-3 Material Row Count	`COUNT(SPLIT(Total Level-3 Material Collection,','))`	Calculates the total number of materials contained in Total Level-3 Material Collection.
Level-3 Material Quantity Collection	`TEXT(Level-2 Material Usage.Level-3 Material Quantity Collection)`	Aggregates all text values in Level-2 Material Usage > Level-3 Material Quantity Collection.

5. Configuring Fields in the Level-3 Material Usage Subform

1) Select the entire Level-3 Material Usage subform. Go to Field Properties > Initial Value, select Data Linkage, and click Data Linkage Settings.

2) On the Data Linkage Settings page, configure the settings as follows:

Linked Form: Subform Base Table
Condition Settings: Total Row Count equals Total Level-3 Material Row Count
Triggered Linkage:
- Level-3 Material Usage shows the value of Subform
- Add the Serial No. subfield and set it to show the value of the No. subfield

After all settings are completed, click OK.

3) Set formulas for all fields in Level-3 Material Usage except Serial No. :

Field Name	Formula	Meaning
Level-3 Material Usage > Level-3 Material Name	`SPLIT(Total Level-3 Material Collection,',')[Level-3 Material Usage.Serial No.-1]`	Splits Total Level-3 Material Collection by commas and fills the corresponding row according to the serial number in the subform.
Level-3 Material Usage > Level-3 Material Quantity	`Level-3 Material Usage.Level-3 Material Quantity per Level-2 Material*Level-3 Material Usage.Level-2 Material Quantity`	Calculates the required level-3 material quantity based on the quantity required for one level-2 material and the level-2 material quantity.
Level-3 Material Usage > Level-3 Material Quantity per Level-2 Material	`SPLIT(Level-3 Material Quantity Collection,',')[Level-3 Material Usage.Serial No.-1]`	Splits Level-3 Material Quantity Collection by commas and fills the corresponding row according to the serial number in the subform.
Level-3 Material Usage > Level-2 Material Quantity	`SPLIT(Level-2 Material Quantity Repetition,',')[Level-3 Material Usage.Serial No.-1]`	Splits Level-2 Material Quantity Repetition by commas and fills the corresponding row according to the serial number in the subform.
Level-3 Material Usage > Level-3 Material Name + Quantity	`CONCATENATE(Level-3 Material Usage.Level-3 Material Name,Level-3 Material Usage.Level-3 Material Quantity,",")`	Combines the material name, material quantity, and a comma for each row in the Level-3 Material Usage subform using the `CONCATENATE` function.

6. Configuring Main-Form Fields for Material Usage Summary

Set formulas for the following three fields:

Field Name	Formula	Meaning
Level-3 Material Name Deduplication	`UNION(Level-3 Material Usage.Level-3 Material Name)`	Deduplicates Level-3 Material Name values in the Level-3 Material Usage subform and keeps only unique level-3 material names.
Row Count	`COUNT(UNION(Level-3 Material Usage.Level-3 Material Name))`	Calculates the number of deduplicated level-3 material names and uses the result as the number of rows in the summary subform.
Level-3 Material Name + Quantity Collection	`CONCATENATE(Level-3 Material Usage.Level-3 Material Name + Quantity)`	Combines all level-3 material names and quantities in the subform using the `CONCATENATE` function.

7. Configuring Fields in the Material Usage Summary Subform

1) Select the entire Material Usage Summary subform. Go to Field Properties > Initial Value, select Data Linkage, and click Data Linkage Settings.

2) On the Data Linkage Settings page, configure the settings as follows:

Linked Form: Subform Base Table
Condition Settings: Total Rows equals to Row Count
Triggered Linkage:
- Material Usage Summary shows the value of Subform
- Add the Serial No. subfield and set it to show the value of the No. subfield

After all settings are completed, click OK.

3) Set formulas for all fields in Material Usage Summary except Serial No. :

Field Name	Formula	Meaning
Material Usage Summary > Level-3 Material Name	`SPLIT(Level-3 Material Name Deduplication,',')[Material Usage Summary.Serial No.-1]`	Splits Level-3 Material Name Deduplication by commas and fills the corresponding row according to the serial number in the subform.
Material Usage Summary > Level-3 Material Quantity	`SUM(SPLIT(CONCATENATE(SPLIT(Level-3 Material Name + Quantity Collection,Material Usage Summary.Level-3 Material Name)), ","))`	Splits Level-3 Material Name + Quantity Collection by the material name in the current row, extracts the corresponding material quantities using commas as delimiters, and then adds the quantities together with the `SUM` function to calculate the total quantity required for that level-3 material.

Step 3 Setting Field Visibility Rules

1) For the auxiliary fields in the form, you can set the fields to be invisible in Field Properties > Field Permissions.

Keep only the following fields visible:

Product Details: Product Name, Quantity
Material Usage Summary: Level-3 Material Name, Level-3 Material Quantity

2) Go to Form Properties > Hidden Field Default Value and set the assignment rule to Always Recalculate.