Track Time Between Process Management Statuses

Contents

Overview

Tracking the progress of business processes with Process Management (External link) is one of Kintone’s strongest features. To add on to that, it would help even more to be able to see how long certain stages of a business process are taking so that bottlenecks can be fixed and to optimize overall process time from start to finish. This article introduces how to track the amount of time taken between process management statuses in a Kintone App..

Sample Image

The below image shows a table with Status Name, Status Datetime, and Status Days. The current status is “Collect provider responses and summarize”.

Screenshot: A table with Status Name, Status Datetime, and Status Days.

Clicking the “Provider Responses Summarized” action shows the confirmation for proceeding the status to “Review provider options with client”.

Screenshot: User is about to proceed to the next status.

Clicking Confirm proceeds the status and adds a new row to the table. The data includes the new current status, the current datetime, and how many days it took for the status change.

Screenshot: Clicking Confirm proceeds the status and adds a new row to the table.

Prepare the App

Create the Form

In terms of App set up, this customization only requires a table to input the process management information into. Therefore, it is very easy to implement into any App that would benefit from a visualization of the process management. The field settings are as follows.

Field Type Field Name Field Code Notes
Table Log Table Status_Log -
Text Status Name Status_Name Part of Log Table table
Datetime Status Datetime Status_Datetime Part of Log Table table
Number Status Days Status_Days Part of Log Table table

The completed table should look like the following.

Screenshot: The completed table.

Set the Library

This sample uses Luxon (External link) v2.3.1. Set the following URL into the App’s JavaScript and CSS Customization settings (External link).

  • https://js.kintone.com/luxon/2.3.1/luxon.min.js

Sample Code

Prepare the following JavaScript code in a text editor and navigate to the Kintone App’s settings. Upload the file into the Upload JavaScript for PC option of the JavaScript and CSS Customization settings (External link).

 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
51
52
53
54
55
56
57
58
59
60
61
62
(function() {
  'use strict';
  var defaultRow = {
    'Status_Name': {
      'value': '',
      'type': 'SINGLE_LINE_TEXT'
    },
    'Status_Datetime': {
      'type': 'DATETIME',
      'value': ''
    },
    'Status_Days': {
      'type': 'NUMBER',
      'value': 0
    }
  };
  function handleAddProcessLog(event) {
    var record = event.record;
    var processLogTable = record.Status_Log.value;
    var processLogTableLeng = processLogTable.length;
    var rowIdx = processLogTableLeng <= 0 ? 0 : processLogTableLeng - 1;
    var currentDatetime = luxon.DateTime.local();
    var previousDatetime = processLogTable[rowIdx].value.Status_Datetime.value;
    var duration = Math.round(currentDatetime.diff(luxon.DateTime.fromISO(previousDatetime)).as('days'));
    var nextStatus = event.nextStatus ? event.nextStatus.value : record.Status.value;
    var newRow = defaultRow;
    newRow.Status_Name.value = nextStatus;
    newRow.Status_Datetime.value = currentDatetime.toISO();
    newRow.Status_Days.value = Number.isNaN(duration) ? 0 : duration;
    processLogTable.push({'value': newRow});
    return event;
  }
  function resetTableRow(event) {
    var record = event.record;
    var type = event.type;
    if (type === 'app.record.create.show') {
      if (event.reuse) {
        record.Status_Log.value = [{
          'value': defaultRow
        }];
      }
    }
    return event;
  }
  function handleInitialLog(event) {
    var record = event.record;
    var currentTableRowValue = record.Status_Log.value[0];
    currentTableRowValue.value.Status_Name.value = 'Not started';
    currentTableRowValue.value.Status_Datetime.value = luxon.DateTime.local().toISO();
    currentTableRowValue.value.Status_Days.value = 0;
    record.Status_Log.value = [currentTableRowValue];
    return event;
  }

  // Events
  kintone.events.on('app.record.detail.process.proceed', handleAddProcessLog);

  kintone.events.on(['app.record.create.show', 'app.record.edit.show', 'app.record.index.edit.show'], resetTableRow);

  // For process log, only initialize when creating a record
  kintone.events.on('app.record.create.submit', handleInitialLog);
})();

After saving the settings, update the App. Add a new record into the App and proceed the Status. Every time the Status proceeds, a new row should be added into the Table field. The rows should be filled with the Status name, the date time and the number of days between the statuses.

The defaultRow variable specifies a blank row in the process management table. This variable is used in the functions declared afterward.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
var defaultRow = {
  'Status_Name': {
    'value': '',
    'type': 'SINGLE_LINE_TEXT'
  },
  'Status_Datetime': {
    'type': 'DATETIME',
    'value': ''
  },
  'Status_Days': {
    'type': 'NUMBER',
    'value': 0
  }
};

The handleAddProcessLog function retrieves the next status and calculates the time in days that it took to get from the previous status to the current status. The information is then added as a new row to the table.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
function handleAddProcessLog(event) {
  var record = event.record;
  var processLogTable = record.Status_Log.value;
  var processLogTableLeng = processLogTable.length;
  var rowIdx = processLogTableLeng <= 0 ? 0 : processLogTableLeng - 1;
  var currentDatetime = luxon.DateTime.local();
  var previousDatetime = processLogTable[rowIdx].value.Status_Datetime.value;
  var duration = Math.round(currentDatetime.diff(luxon.DateTime.fromISO(previousDatetime)).as('days'));
  var nextStatus = event.nextStatus ? event.nextStatus.value : record.Status.value;
  var newRow = defaultRow;
  newRow.Status_Name.value = nextStatus;
  newRow.Status_Datetime.value = currentDatetime.toISO();
  newRow.Status_Days.value = Number.isNaN(duration) ? 0 : duration;
  processLogTable.push({'value': newRow});
  return event;
}

The resetTableRow function is used in the case that a record is reused that already has process management information in the table. It will reset the table information using the defaultRow variable information.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
function resetTableRow(event) {
  var record = event.record;
  var type = event.type;
  if (type === 'app.record.create.show') {
    if (event.reuse) {
      record.Status_Log.value = [{
        'value': defaultRow
      }];
    }
  }
  return event;
}

The handleInitialLog function is used to input the initial row in the process management table containing the Not started status when the record is initially created.

1
2
3
4
5
6
7
8
9
function handleInitialLog(event) {
  var record = event.record;
  var currentTableRowValue = record.Status_Log.value[0];
  currentTableRowValue.value.Status_Name.value = 'Not started';
  currentTableRowValue.value.Status_Datetime.value = luxon.DateTime.local().toISO();
  currentTableRowValue.value.Status_Days.value = 0;
  record.Status_Log.value = [currentTableRowValue];
  return event;
}

Finally, the applicable Kintone events are declared and each function is assigned to the Kintone events they should be executed at. In this case, handleAddProcessLog is executed when the process management is proceeded. resetTableRow is executed at the create record and edit record events. Finally, handleInitialLog is executed when a new record is created.

1
2
3
4
5
6
7
// Events
kintone.events.on('app.record.detail.process.proceed', handleAddProcessLog);

kintone.events.on(['app.record.create.show', 'app.record.edit.show', 'app.record.index.edit.show'], resetTableRow);

// For process log, only initialize when creating a record
kintone.events.on('app.record.create.submit', handleInitialLog);

Customization Variation

This customization can be further improved by making the process management table uneditable through the GUI. An example of the uneditable table is shown below.

Screenshot: The process management table is uneditable through the GUI.

The edited code is shown below.

 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
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
(function() {
  'use strict';
  var defaultRow = {
    'Status_Name': {
      'value': '',
      'type': 'SINGLE_LINE_TEXT'
    },
    'Status_Datetime': {
      'type': 'DATETIME',
      'value': ''
    },
    'Status_Days': {
      'type': 'NUMBER',
      'value': 0
    }
  };
  function handleAddProcessLog(event) {
    var record = event.record;
    var processLogTable = record.Status_Log.value;
    var processLogTableLeng = processLogTable.length;
    var rowIdx = processLogTableLeng <= 0 ? 0 : processLogTableLeng - 1;
    var currentDatetime = luxon.DateTime.local();
    var previousDatetime = processLogTable[rowIdx].value.Status_Datetime.value;
    var duration = Math.round(currentDatetime.diff(luxon.DateTime.fromISO(previousDatetime)).as('days'));
    var nextStatus = event.nextStatus ? event.nextStatus.value : record.Status.value;
    var newRow = defaultRow;
    newRow.Status_Name.value = nextStatus;
    newRow.Status_Datetime.value = currentDatetime.toISO();
    newRow.Status_Days.value = Number.isNaN(duration) ? 0 : duration;
    processLogTable.push({'value': newRow});
    return event;
  }
  function resetTableRow(event) {
    var record = event.record;
    var type = event.type;
    if (type === 'app.record.create.show') {
      if (event.reuse) {
        record.Status_Log.value = [{
          'value': defaultRow
        }];
      }
    }
  }
  function handleInitialLog(event) {
    var record = event.record;
    var currentTableRowValue = record.Status_Log.value[0];
    currentTableRowValue.value.Status_Name.value = 'Not started';
    currentTableRowValue.value.Status_Datetime.value = luxon.DateTime.local().toISO();
    currentTableRowValue.value.Status_Days.value = 0;
    record.Status_Log.value = [currentTableRowValue];
    return event;
  }
  function disableTableEdits(event) {
    var tableRows = event.record.Status_Log.value;
    tableRows.forEach(function(row) {
      row.value.Status_Datetime.disabled = true;
      row.value.Status_Days.disabled = true;
      row.value.Status_Name.disabled = true;
    });
  }

  // Events
  kintone.events.on('app.record.detail.process.proceed', handleAddProcessLog);

  kintone.events.on(['app.record.create.show', 'app.record.edit.show', 'app.record.index.edit.show'], function(event) {
    resetTableRow(event);
    disableTableEdits(event);
    return event;
  });

  // For process log, only initialize when creating a record
  kintone.events.on('app.record.create.submit', handleInitialLog);
})();

The main change to the code is the addition of the disableTableEdits function. This function goes through each row in the table and sets the disabled property of each field to true.

1
2
3
4
5
6
7
8
function disableTableEdits(event) {
  var tableRows = event.record.Status_Log.value;
  tableRows.forEach(function(row) {
    row.value.Status_Datetime.disabled = true;
    row.value.Status_Days.disabled = true;
    row.value.Status_Name.disabled = true;
  });
}

The other change is in the events declarations. disableTableEdits is added to the three events that resetTableRow is executed at, and the event is returned within the event declaration instead of within each individual function.

1
2
3
4
5
kintone.events.on(['app.record.create.show', 'app.record.edit.show', 'app.record.index.edit.show'], function(event) {
  resetTableRow(event);
  disableTableEdits(event);
  return event;
});

Reference