Introduction
This article explains how to generate a serial number only when needed. Based on the form data, users can manually choose whether to generate a serial number for the current form.
Application Scenarios
In HR Management, when processing employee onboarding, you can decide whether to generate a corresponding serial number based on whether the employee needs accommodation.
In Financial Management, you can decide whether to generate a serial number for deferred payments based on whether the customer applies for a payment extension.
Solution Design
Use a Radio field to set the option for whether to generate a serial number.
Use the
TODAY()function to obtain the current date, 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?
When Generate Serial Number is set to Yes, a serial number is generated. Otherwise, the serial number remains empty.
How to Set up?
Step 1 Building the Form
Create a form named Choose Whether to Generate a Serial Number and add the following fields:
Field Name | Field Type |
Generate Serial Number | Radio |
Current Date | Single Line |
Count | Number |
Custom Serial Number | Single Line |
Step 2 Setting Formulas
1. Select the Current Date field. Go to Field Properties > Initial Value > Formula > Edit.
2. Set the formula for Current Date as follows:
TEXT(TODAY(),"yyyyMMdd")
3. In the same way, set the formula for Count as follows:
IF(Generate Serial Number=="Yes",MAPX("max",Current Date,Current Date,Count)+1,"")Note: For the last two field arguments in the MAPX function, select the corresponding fields from All Form Fields > Choose Whether to Generate a Serial Number.
4. Set the formula for Custom Serial Number as follows:
IF(Generate Serial Number=="Yes",CONCATENATE("Jodoo","-",Current Date,"-",RIGHT("0000"+Count,4)),"")
Step 3 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 4 Setting Field Permissions
For auxiliary fields such as Current Date 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.
