When to Use This Feature?

In inventory management, parts are often stored individually but shipped in boxes and pallets.

For example, when shipping parts from different batches, you may need to calculate the number of boxes based on the quantity of parts per box and determine which pallet each box belongs to.

How Does It Work?

This solution uses two subforms and formulas to:

Calculate the required number of boxes for each shipping batch.
Automatically generate one subform row for each box.
Assign boxes to pallets based on the specified number of boxes per pallet.

What Does It Look Like?

For example, each box holds 5 parts, and each pallet holds 2 boxes.

Batch No.	Shipping Quantity
A	12
B	8

The system automatically generates the following packing details:

How to Set It Up?

Step 1 Creating a Subform Base Table

Create a Subform Base Table to store predefined row numbers for different subform row counts.

Field Name	Field Type
Total Rows	Number
Subform	SubForm
Serial No.	Subfield - Number
Number	Subfield - Number

Go to Manage Data > Import and import the prepared data:200-row Subform Base Table Serial Number Complete.xlsx.

Step 2 Creating a Parts Shipment Form

Create a form named Parts Shipment with the following fields.

1. Main Form Fields

Field Name	Field Type
Boxes per Pallet	Number
Total Boxes	Number
Total Pallets	Number
Batch List	Single Line
Parts List	Single Line

2. Packing Details Subform

Field Name	Field Type
Batch No.	Single Line
Shipping Quantity	Number
Parts per Box	Number
Box Count	Number
Batch Helper	Single Line
Parts Helper	Single Line

Set the initial value of Parts per Box to 5.

3. Box Allocation Subform

Field Name	Field Type
Box No.	Number
Batch No.	Single Line
Parts in Box	Number
Pallet No.	Number

Step 3 Configuring Packing Details Formulas

In the Packing Details subform, go to Field Properties > Initial Value > Formula > Edit and configure the following formulas.

1. Box Count

CEILING(Packing Details.Shipping Quantity/Packing Details.Parts per Box,1)

Rounds up the shipping quantity divided by the number of parts per box.

2. Batch Helper

REPT(CONCATENATE(Packing Details.Batch No.,","),Packing Details.Box Count)

Repeats the batch number according to the box count.

3. Parts Helper

CONCATENATE(   REPT(     CONCATENATE(TEXT(Packing Details.Parts per Box,"0"),","),     INT(Packing Details.Shipping Quantity/Packing Details.Parts per Box)   ),   IF(     MOD(Packing Details.Shipping Quantity,Packing Details.Parts per Box)>0,     CONCATENATE(TEXT(MOD(Packing Details.Shipping Quantity,Packing Details.Parts per Box),"0"),","),     ""   ) )

Generates the parts quantity for each box, including any remaining parts in the last box.

Step 4 Configuring the Main Form Formulas

Set the following formulas in the main form.

Field Name	Formula
Total Boxes	`SUM(Packing Details.Box Count)`
Batch List	`CONCATENATE(Packing Details.Batch Helper)`
Parts List	`CONCATENATE(Packing Details.Parts Helper)`
Total Pallets	`CEILING(Total Boxes/Boxes per Pallet,1)`

These formulas calculate the total boxes and pallets and combine the batch numbers and parts quantities for the Box Allocation subform.

Step 5 Configuring Box Allocation

1. Setting Up Data Linkage

Select the entire Box Allocation subform and go to Field Properties > Initial Value > Data Linkage > Data Linkage Settings.

Configure the following:

Linked Form: Subform Base Table
Condition: Total Rows = Total Boxes
Triggered Linkage: Display Subform in Box Allocation.
Subfield Mapping: Serial No. → Box No.

Click OK to save. The Box Allocation subform will automatically generate one row for each box.

2. Setting Up Formulas

Configure the remaining subfields as follows.

Batch No.

SPLIT(Batch List,",")[Box Allocation.Box No.-1]

Parts in Box

SPLIT(Parts List,",")[Box Allocation Subform.Box No.-1]

Pallet No.

CEILING(Box Allocation.Box No./Boxes per Pallet,1)

These formulas assign each box its corresponding batch number, parts quantity, and pallet number.

Step 6 Hiding Auxiliary Fields

To keep the form clean:

Select the auxiliary fields and go to Field Properties > Field Permissions. Uncheck View.
Go to Form Properties > Hidden Field Default Value and select Always Recalculate.

Keep Packing Details, Boxes per Pallet, Total Boxes, Total Pallets, and Box Allocation visible.

Automatically Calculating Boxes and Pallets for Parts Shipments