When to Use It?
A two-level BOM is suitable for production scenarios where the product structure is relatively simple, such as clothing, food, and daily necessities. It is intended for products that contain only level-2 materials.
What Does It Look Like?
Enter the product name and the corresponding quantity in Product Details, and the required materials and quantities will be calculated automatically.
How to Set It Up
Step 1 Building the Form
Create a form named Two-Level BOM Material Usage 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 |
└ Material Name + Quantity | Single Line |
Material Name Deduplication | Single Line |
Row Count | Number |
Material Name + Quantity Collection | Single Line |
Material Usage Summary | Subform |
└ Serial No. | Number |
└ Material Name | Single Line |
└ 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 |
| Aggregates all text values in Product Details > Quantity Repetition. |
Total Level-2 Material Collection |
| Aggregates all text values in Product Details > Level-2 Material Collection. |
Total Level-2 Material Row Count |
| Calculates the total number of materials contained in Total Level-2 Material Collection. |
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 Row Count equals Total Level-2 Material Row Count
Triggered Linkage:
Level-2 Material Usage shows the value of Subform
Add the Serial Number subfield and set it to show the value of the Serial Number subfield
After all settings are completed, click OK.
3) Set formulas for all fields in Level-2 Material Usage except Serial No. :
Field Name | Formula | Meaning |
Level-2 Material Usage > Material Name |
| 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 |
| Calculates the required material quantity based on the quantity required for one product and the product quantity. |
Level-2 Material Usage > Material Quantity per Product |
| 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 |
| Splits Product Quantity Repetition by commas and fills the corresponding row according to the serial number in the subform. |
Level-2 Material Usage > Material Name + Quantity |
| Combines the material name, material quantity, and a comma for each row in the Level-2 Material Usage subform using the |
4. Configuring Main-Form Fields for Material Usage Summary
Set formulas for the following three fields:
Field Name | Formula | Meaning |
Material Name Deduplication |
| Deduplicates Material Name values in the Level-2 Material Usage subform and keeps only unique material names. |
Row Count |
| Calculates the number of unique material names and uses the result as the number of rows in the summary subform. |
Material Name + Quantity Collection |
| Combines all material names and quantities in the subform using the |
5. 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 Row Count 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 Serial 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 > Material Name |
| Splits Material Name Deduplication by commas and fills the corresponding row according to the serial number in the subform. |
Material Usage Summary > Material Quantity |
| Splits Material Name + Quantity Collection by the material name in the current row, extracts the corresponding quantities using commas as delimiters, and then adds the quantities together with the |
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: Material Name, Material Quantity
2) Go to Form Properties > Hidden Field Default Value and set the rule to Always Recalculate.
