Introduction
This article explains how to generate serial numbers based on a selected project and enable duplicate validation. If a duplicate serial number is detected during form submission, users can click Refresh Serial Number to generate the latest serial number directly in the form.
Application Scenarios
In Project Management, when team members work on multiple projects, serial numbers can be generated separately for different projects.
In course registration, serial numbers can be generated separately based on the courses selected by students.
Solution Design
Use a Radio field to set the option for refreshing the serial number.
Use a Single Select field to select a project and record its name.
When a serial number needs to be generated, use the
MAPX()function to retrieve the maximum existing count for the selected project and add 1.Use the
RIGHT()function to pad the count to a fixed number of digits, ensuring consistent serial number length.Finally, use the
CONCATENATE()function to combine all components of the serial number.
What Does It Look Like?
When different projects are selected in the form, the corresponding serial numbers are generated automatically. If a duplicate serial number is detected during submission, select Refresh Serial Number to update it to the latest serial number.
How to Set up?
Step 1 Building the Form
Create a form named Generate Serial Numbers Based on Project Name and add the following fields:
Field Name | Field Type |
Refresh Serial Number | Radio |
Project | Single Select |
Count | Number |
Custom Serial Number | Single Line |
Step 2 Setting the Refresh Option
Select the Refresh Serial Number field. In Field Properties, uncheck Show Title and set the option to Refresh Serial Number.
Step 3 Setting Formulas
1. Select the Count field. Go to Field Properties > Initial Value > Formula > Edit.
2. Set the formula for Count as follows:
IF(ISEMPTY(Refresh Serial Number)!=1,MAPX("max",Project,Project,Count)+1,MAPX("max",Project,Project,Count)+1)
Note: For the last two field arguments in the MAPX function, switch to All Form Fields > Generate Serial Numbers Based on Project Name and select the corresponding fields.
3. Set the formula for Custom Serial Number as follows:
CONCATENATE("Jodoo","-",Project,"-",RIGHT("0000"+Count,4))
Step 4 Setting Duplicate Validation
To prevent duplicate serial numbers when multiple members open the form at the same time, select the Custom Serial Number field and go to Field Properties > Validation. Enable No duplicates and set the prompt message.
Step 5 Setting Field Permissions
For the auxiliary Count field, which does not need to be displayed when filling out the form, go to Field Properties > Field Permissions and make the field invisible.
After setting the auxiliary field to invisible, go to Form Properties > Hidden Field Default Value and select Always Recalculate.
