This article introduces how to set automatic numbering to records of a Kintone App, based on the year in a Date field.
Apps in Kintone have a field called Record number , which is a unique number given to the record. This number automatically increases for new records that are added, but the format of this number cannot be changed. This customization introduces a numbering method that assigns unique strings to a Text field based on the year of the Date field.
The automatic numbering format in this example places 2 digits in front of the string that represents the year. This is then followed by a "-" symbol and 5 digits that represent the nth record created for that year.
When on the Record Edit page, the Automatic Numbering field is disabled. Users will not be able to tamper with the numbering.
If the date field is October 15th 2022, then the first few records that are created with this Date value would become:
Prepare the App
Create an App with the following fields and settings.
|Field Type||Field Name||Field Code|
|Text||Automatic Numbering||Auto_num||Check the Prohibit duplicated values settings.|
|Date||Date||Date_for_auto_num||Check the Required field settings.|
Calculate the next Auto Numbering
When a new record is saved, the app.record.create.submit event runs the autoNum function.
The newest record created for the target year is obtained, by using a query. The query statement is as follows:
If a new record is created with the Date field value of October 15th 2022, the query statement above represents the following condition: "The first record from the list of records ranged with dates from January 1st 2022 to December 31st 2022, in descending order of the value in the Auto_num field.”
Through this, the record with the newest automatic numbering in the target year can be obtained.
The next part of the automatic numbering string is then calculated:
If a record already exists, the automatic numbering that is created will be calculated by "the number in the Auto_num field + 1".
If no records exists (likely to happen at the beginning of the year), it sets the number to "00001".
Automatic Numbering should only be allocated when saving new records. The field needs to be disabled so that users will not tamper with it.
This can be done by doing the following:
The code is written so that:
- the Automatic Numbering field cannot be edited in the Record Create page
- the Automatic Numbering and Date field cannot be edited in the Record Edit and Record List page