Introduction
This article explains how to generate serial numbers based on a selected date 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 purchasing, sales, and inventory management, date-based serial numbers can be used for order management, inventory management, and purchase management.
In Equipment Management, date-based serial numbers can be used for equipment maintenance, inspection, and usage records.
Solution Design
Use a Radio field to set the option for refreshing the serial number.
Use the
DATE()function to obtain the date from the Date&Time field, then use theTEXT()function to convert it into text as the date component of the serial number.When a serial number needs to be generated, use the
MAPX()function to retrieve the maximum existing count from submitted data 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?
Select a date in the form to automatically generate the corresponding serial number. 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 Date and add the following fields:
Field Name | Field Type |
Refresh Serial Number | Radio |
Date&Time | Date&Time |
Date Helper | Single Line |
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 Date Helper field. Go to Field Properties > Initial Value > Formula > Edit.
2. Set the formula for Date Helper as follows:
TEXT(DATE(Date&Time),"yyyyMMdd")
3. In the same way, set the formula for Count as follows:
IF(ISEMPTY(Refresh Serial Number)!=1,MAPX("max",Date Helper,Date Helper,Count)+1,MAPX("max",Date Helper,Date Helper,Count)+1)
Note: For the last two field arguments in the MAPX function, select the corresponding fields from All Form Fields > Generate Serial Numbers Based on Date.
4. Set the formula for Custom Serial Number as follows:
CONCATENATE("Jodoo","-",Date Helper,"-",RIGHT("00000"+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 auxiliary fields such as Date Helper and Count, which do not need to be displayed when filling out the form, go to Field Properties > Field Permissions and make these fields invisible.
After setting the auxiliary fields to invisible, go to Form Properties > Hidden Field Default Value and select Always Recalculate.
