This article introduces how to implement custom automatic numbering to records, 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. The format of this number cannot be changed though, so there are issues when you wish to assign a unique record number string for your records. This customization introduces a numbering method that assigns unique strings to your records with numbers that are based on the year of a Date field.
The automatic numbering string in this example places 2 digits in the front, that represents the year, 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 will be disabled, so that users will not be able to tamper with the numbering.
If the date field is October 15th 2017, then the first few records that are created with this Date value would become:
- Use a Text field to store your Automatic Numbering string. Check the "Prohibit duplicated values" settings for this field. Give this a field code of "Auto_num".
- Use a Date field for the customization to retrieve Date values from. Check the "Required field" settings for this field. Give this a field code of "Date_for_auto_num".
Source Code Explanation
Calculating the next Auto Numbering
When a new record is saved, the 'app.record.create.submit' event runs the autoNum function.
This first obtains the newest record that was created for the target year, by using a query.
The query statement is as follows:
If we create a new record with the Date field value of October 15th 2017, the query statement above represents the following condition: "The first record from the list of records ranged with dates from January 1st 2017 to December 31st 2017, 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 code calculates the next automatic numbering string that should be created, from the obtained record:
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 "00001".
Since Automatic Numbering should only be allocated when saving new records, we need to disable the Automatic Numbering field so that users will not tamper with it.
This can be done by doing the following:
The code is written so that:
- he 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.
This particular sample shows how to create Automatic Numbering in the format of YY-XXXXX. You may customize the format within the code to fit your needs.
One thing you should watch out for when using this customization, is the fact that it is incompatible with Record Permission settings. This code relies on the user creating the record to be able to search for the newest Automatic Numbered record. If the user is not able to view all of the records, the desired query result would be wrong, and will result in an error (the code will try to create an Automatic Numbering String that already exists, and the Automatic Numbering String prohibits duplicate values).
Just keep that in the back of your mind when using this code!