Skip to main content

Automatically Generating Sequential Numbers in a Subform by Category

Learn how to automatically generate sequential numbers for multiple items in a subform based on their categories and quantities.

When to Use This Feature?

When registering incoming equipment, you may need to generate consecutive equipment numbers based on the stock-in type and quantity.

This solution automatically generates equipment numbers for each type, continuing from the highest number in previous records.

How Does It Work?

  1. Use MAPX to retrieve the highest existing number for each stock-in type.

  2. Generate the corresponding number of subform rows through Data Linkage.

  3. Use SPLIT to assign the stock-in types and sequence numbers to each row.

  4. Use Automations Pro to save the generated numbers for future submissions.

What Does It Look Like?

For example, if Type A has a previous maximum number of 6 and the new quantity is 3, the generated sequence numbers will be 7, 8, and 9.

How to Set It Up?

Step 1 Creating a Sequence Base Table

Create a Sequence Base Table to store predefined number sequences for different quantities.

Field Name

Field Type

Quantity

Number

Sequence List

Single Line

Enter the following sample data:

Quantity

Sequence List

1

1

2

1,2

3

1,2,3

4

1,2,3,4

Continue adding records up to the maximum quantity required. Separate the numbers with English commas (,).

Step 2 Creating a Subform Base Table

Create a Subform Base Table to control the number of generated subform rows.

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 3 Creating a Numbering History Form

Create a Numbering History form to store the generated sequence numbers for each stock-in type.

Field Name

Field Type

Stock-in Type

Single Line

Sequence No.

Number

The records will be added automatically through Automations Pro after each submission.

Step 4 Creating the Equipment Numbering Form

Create an Equipment Numbering form with the following fields. Go to Field Properties > Initial Value > Formula > Edit and configure the formulas below：

1. Stock-in Details Subform

Field Name

Field Type

Field Settings

Stock-in Type

Single Select

Select a stock-in type

Quantity

Number

Enter the quantity

Previous Max No.

Number

Previous Max No.

MAPX("max",Stock-in Details.Stock-in Type,Stock-in Type,Sequence No.)+0

Select Stock-in Type and Sequence No. from the Numbering History form as the cross-form references.

Repeated Type

Single Line

Repeated Type

REPT(CONCATENATE(Stock-in Details.Stock-in Type,","),Stock-in Details.Quantity)

Repeated Max No.

Single Line

Repeated Max No.

REPT(CONCATENATE(Stock-in Details.Previous Max No.,","),Stock-in Details.Quantity)

Sequence List

Single Line

For Sequence List, configure Data Linkage:

  • Linked Form: Sequence Base Table

  • Condition: Quantity = Stock-in Details.Quantity

  • Triggered Linkage: Display Sequence List

2. Main Form Fields

Add the following fields and configure their formulas.

Field Name

Field Type

Formula

Row Count

Number

SUM(Stock-in Details.Quantity)

Type Summary

Single Line

CONCATENATE(Stock-in Details.Repeated Type)

Previous Max Summary

Single Line

CONCATENATE(Stock-in Details.Repeated Max No.)

Sequence Summary

Single Line

JOIN(Stock-in Details.Sequence List,",")

3. Equipment Numbers Subform

Add another SubForm named Equipment Numbers, select the entire Equipment Numbers subform and go to Field Properties > Initial Value > Data Linkage > Data Linkage Settings. Configure the following:

  • Linked Form: Subform Base Table

  • Condition: Total Rows = Row Count

  • Triggered Linkage: Display Subform in Equipment Numbers

  • Subfield Mapping: Serial No. → Serial No.

Click OK to save. For the remaining subfields, configure the following formulas.

Field Name

Field Type

Field Settings

Serial No.

Number

Stock-in Type

Single Line

Stock-in Typ

SPLIT(Type Summary,",")[Equipment Numbers.Serial No.-1]

Previous No.

Number

Previous No.

VALUE(SPLIT(Previous Max Summary,",")[Equipment Numbers.Serial No.-1])

Sequence Offset

Number

Sequence Offset

VALUE(SPLIT(Sequence Summary,",")[Equipment Numbers.Serial No.-1])

Final Sequence No.

Number

Final Sequence No.

Equipment Numbers.Previous No.+Equipment Numbers.Sequence Offset

Equipment ID

Single Line

Equipment ID

CONCATENATE(Equipment Numbers.Stock-in Type,MONTH(TODAY()),"-",TEXT(Equipment Numbers.Final Sequence No.,"000"))

For example, Type A with a final sequence number of 13 in October will generate A10-013.

Step 5 Configuring Automations Pro

Go to Extension > Automations >+ New Automation. Configure the following:

  • Trigger Type: Form Trigger

  • Triggering Form: Equipment Numbering

  • Trigger Event: Add record

  • Action Node: Add Record

  • Target Form: Numbering History

Set the field assignments:

Target Field

Source Field

Stock-in Type

Equipment Numbers.Stock-in Type

Sequence No.

Equipment Numbers.Final Sequence No.

Click Save & Enable. After submission, each generated sequence number will be saved to Numbering History.

Step 6 Hiding Auxiliary Fields

For auxiliary fields, go to Field Properties > Field Permissions and uncheck View.

Then go to Form Properties > Hidden Field Default Value and select Always Recalculate.

Related Articles
Did this answer your question?