Set Automatic Numbering

Contents

Overview

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 (External link) , 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.

Sample image

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:

  • 22-00001
  • 22-00002
  • 22-00003

Prepare the App

Create an App (External link) 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.
Text Title Title
Text Detail Detail

Sample Code 

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50

(function() {
  'use strict';

  function autoNum(event) {
    var record = event.record;

    var dt = record.Date_for_auto_num.value;
    var dtyy = dt.substring(0, 4);
    var dtmin = dtyy + '-01-01';
    var dtmax = (parseInt(dtyy, 10) + 1) + '-01-01';

    var query = {
      'app': kintone.app.getId(),
      'query': 'Date_for_auto_num >= "' + dtmin + '" and Date_for_auto_num < "' + dtmax + '" order by Auto_num desc limit 1'
    };

    return kintone.api(kintone.api.url('/k/v1/records.json', true), 'GET', query).then(function(resp) {
      var records = resp.records;
      if (records.length > 0) {
        var rec = records[0];
        var autono = rec.Auto_num.value;
        autono = parseInt(autono.substring(3), 10) + 1;
        autono = '00000' + autono;
        autono = dt.substring(2, 4) + '-' + autono.substring(autono.length - 5);
        event.record.Auto_num.value = autono;
      } else {
        event.record.Auto_num.value = dt.substring(2, 4) + '-00001';
      }
      return event;
    }).catch(function(e) {
      alert('Error occured getting record - error: ' + e.message);
      return false;
    });
  }

  kintone.events.on('app.record.create.submit', autoNum);

  kintone.events.on('app.record.create.show', function(event) {
    var record = event.record;
    record.Auto_num.disabled = true;
    return event;
  });

  kintone.events.on(['app.record.edit.show', 'app.record.index.edit.show'], function(event) {
    var record = event.record;
    record.Auto_num.disabled = true;
    record.Date_for_auto_num.disabled = true;
    return event;
  });
})();

Code Explanation

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:

1

'Date_for_auto_num >= "' + dtmin + '" and Date_for_auto_num < "' + dtmax + '" order by Auto_num desc limit 1';

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:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

if (records.length > 0) {
  var rec = records[0];
  var autono = rec.Auto_num.value;
  autono = parseInt(autono.substring(3), 10) + 1;
  autono = '00000' + autono;
  autono = dt.substring(2, 4) + '-' + autono.substring(autono.length - 5);
  event.record.Auto_num.value = autono;

} else {
  event.record.Auto_num.value = dt.substring(2, 4) + '-00001';
}

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".

Restrict Inputs

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:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

kintone.events.on('app.record.create.show', function(event) {
  var record = event.record;

  record.Auto_num.disabled = true;
  return event;
});

kintone.events.on(['app.record.edit.show', 'app.record.index.edit.show'], function(event) {
  var record = event.record;

  record.Auto_num.disabled = true;
  record.Date_for_auto_num.disabled = true;
  return event;
});

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