When to Use This Feature?
When registering multiple incoming devices, you may need to generate a unique number for each device based on its category and quantity.
For example, entering 3 laptops and 2 monitors should automatically generate 5 records, with each category continuing from its previously assigned numbers.
How Does It Work?
This solution uses MAPX, Data Linkage, and Automations Pro to:
Retrieve the highest existing number for each equipment category.
Generate the required number of subform rows based on the entered quantities.
Calculate new sequence numbers and save them for future submissions.
What Does It Look Like?
For example, the following numbers are generated automatically:
Category | Quantity | Previous Max No. | New Numbers |
Laptop | 3 | 12 | 13, 14, 15 |
Monitor | 2 | 7 | 8, 9 |
Each equipment number also includes the category and current month.
How to Set It Up?
Step 1 Creating the Forms
Create the following four forms:
Form Name | Purpose |
Number Sequence Base | Stores number sequences corresponding to different quantities. |
Subform Row Base | Provides predefined subform rows for data linkage. |
Number History | Stores previously generated numbers for each category. |
Equipment Registration | Generates equipment numbers based on categories and quantities. |
In Equipment Registration, create two subforms:
Inbound Details: Contains Category, Quantity, and related calculation fields.
Generated Records: Displays the automatically generated equipment numbers.
Step 2 Preparing the Base Data
1. Number Sequence Base
Add two fields:
Quantity: Number
Number Sequence: Single Line
Enter the corresponding number sequence for each quantity.
Quantity | Number Sequence |
1 | 1, |
2 | 1,2, |
3 | 1,2,3, |
4 | 1,2,3,4, |
Continue until you reach the maximum quantity required.
Note: Use English commas (,) to separate numbers and keep a trailing comma for each sequence.
2. Subform Row Base
Create a base form containing:
Total Rows: Number
Rows: Subform
Row No.: Number (subfield)
Import the predefined row-number data. You can use the 200-row template provided in Adjusting the Number of Rows in a Subform Automatically.
3. Number History
Add two fields:
Category: Single Line
Serial No.: Number
This form stores the category and final sequence number of each registered device.
Step 3 Setting Up Data Linkage
In Equipment Registration, configure the following data linkage settings.
1. Link the Number Sequence
Select the Inbound Details > Number Sequence field and go to Field Properties > Initial Value > Data Linkage.
Configure the linkage as follows:
Linked Form: Number Sequence Base
Condition: Quantity equals Inbound Details.Quantity
Linked Field: Number Sequence
2. Generate Subform Rows
Select the entire Generated Records subform and configure Data Linkage.
Linked Form: Subform Row Base
Condition: Total Rows equals Equipment Registration.Total Rows
Trigger Linkage: Display Rows in Generated Records and map Row No. to Row No.
The subform will automatically generate the required number of rows based on the total quantity.
Step 4 Configuring the Formulas
Select the corresponding field and go to Field Properties > Initial Value > Formula > Edit.
1. Inbound Details and Main Form
Configure the following formulas:
Field Name | Formula |
Previous Max No. (Number) |
|
Repeated Category (Single Line) |
|
Repeated Max No. (Single Line) |
|
Total Rows (Number) |
|
Category List (Single Line) |
|
Max No. List (Single Line) |
|
Number Sequence List (Single Line) |
|
The Previous Max No. formula retrieves the highest existing sequence number for the selected category from Number History.
2. Generated Records Subform
Add the following fields and configure their formulas:
Field Name | Formula |
Category (Single Line) |
|
Previous Max No. (Number) |
|
Sequence Offset (Number) |
|
Final Number (Number) |
|
Equipment Number (Single Line) |
|
The final formula combines the Category, Current Month, and Sequence Number into an equipment identifier.
For example, Laptop10-013 represents a laptop with sequence number 13 registered in October.
Step 5 Saving the Generated Numbers
Use Automations Pro to save the newly generated numbers in Number History.
Go to Extension > Automations > New Automation.
Set the trigger to Add record in Equipment Registration.
Add an Add Record action and select Number History as the target form.
Map the fields as follows:
Number History | Generated Records |
Category | Category |
Serial No. | Final Number |
Click Save & Enable.
After each submission, the generated numbers are recorded and can be used as the starting point for the next submission.
Step 6 Hiding Auxiliary Fields
Hide the calculation fields that users do not need to see.
Go to Field Properties > Field Permissions and uncheck View for the auxiliary fields.
Go to Form Properties > Hidden Field Default Value and select Always Recalculate for hidden formula fields.
Keep the Category, Quantity, and Equipment Number fields visible.
More to Know
Enter each category only once per submission. Repeating the same category in multiple Inbound Details rows may generate duplicate numbers within that submission.
The example uses three-digit sequence numbers. Adjust the Equipment Number formula if the sequence may exceed 999.
The month is part of the displayed identifier; the sequence does not automatically restart each month.
Concurrent submissions may generate duplicate numbers if they retrieve the same previous maximum.