Filter Through Record Data and Display a Summary

Contents

Overview

This article introduces how to use Underscore.js (External link) to filter and sort through records of a Kintone App. A summary is made out of the filtered data, and is displayed on the Record List page.

What is Underscore.js?

Underscore is a JavaScript library that provides many useful helper functions without having to extend built-in objects. This tutorial uses the following 5 helper functions:

  1. _.max (External link) , which can find the maximum element in a list
  2. _.filter (External link) , which returns all values that pass some predicate
  3. _.sortBy (External link) , which can sort the values of an object by some criteria
  4. _.pluck (External link) , which isolates a certain property of an object
  5. _.chunk (External link) , which can break up a single array into multiple arrays, each with a certain number of elements

Sample Image

This tutorial builds a database of students with their school grades.

Information is then displayed in the header space above the record list using Underscore.js. Using functions described later in this tutorial, the following can be calculated and displayed:

  • The student with the highest score in the science class
  • The students whose averaged grades exceed 90
  • Study partners for students in the same grade range in the math class

Prepare the App

Create the Form

Create an App (External link) with the following fields and settings.

Field Type Field Name Field Code
Text Name name
Number Language Arts language_arts
Number Science science
Number Math math
Number Social Studies social_studies
Number P.E. pe

The layout on the form settings looks like the following:

Set the Library

This sample uses Underscore.js (External link) v1.9.2. Set the following URL into the App's JavaScript and CSS Customization settings (External link) .

  • https://js.kintone.com/underscore/1.9.2/underscore-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
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
(function() {
  'use strict';
  kintone.events.on('app.record.index.show', function(event) {
    // define a function that uses the REST API to get all the app's records:
    function fetchRecords(appId, opt_offset, opt_limit, opt_records) {
      var offset = opt_offset || 0;
      var limit = opt_limit || 100;
      var allRecords = opt_records || [];
      var params = {app: appId, query: 'limit ' + limit + ' offset ' + offset};
      return kintone.api('/k/v1/records', 'GET', params).then(function(resp) {
        allRecords = allRecords.concat(resp.records);
        if (resp.records.length === limit) {
          return fetchRecords(appId, offset + limit, limit, allRecords);
        }
        return allRecords;
      });
    }

    // check if the header already contains the information:
    if (document.getElementById('p1') !== null) {
      return;
    }

    // if it does not, get the header space and the app records:
    var header = kintone.app.getHeaderSpaceElement();
    fetchRecords(kintone.app.getId()).then(function(records) {

      // (1) USING THE _.max FUNCTION
      // find the student with the highest science score:
      var maxSciScore = _.max(records, function(record) {
        return parseInt(record.science.value, 10);
      });

      var p1 = document.createElement('p');
      p1.id = 'p1';
      p1.innerHTML = 'The student with the highest science score is: ' + maxSciScore.name.value;
      header.appendChild(p1);

      // (2) USING THE _.filter FUNCTION
      // find all students with a 90+ grade average in all classes:
      var highGrades = _.filter(records, function(record) {
        return (parseInt(record.math.value, 10) + parseInt(record.language_arts.value, 10) +
                        parseInt(record.science.value, 10) + parseInt(record.social_studies.value, 10) +
                        parseInt(record.pe.value, 10)) / 5 >= 90;
      });

      var highGradeStudents = '';
      for (var i = 0; i < highGrades.length; i++) {
        highGradeStudents += '• ' + highGrades[i].name.value + ' ';
      }

      var p2 = document.createElement('p');
      p2.innerHTML = 'The students with above a 90 grade average are: ' + highGradeStudents;
      header.appendChild(p2);

      // (3) USING THE _.sortBy, _.pluck, AND _.chunk FUNCTIONS
      // create study partners for math based on grades:
      // (the two people with the lowest grades work together, and then up the list)
      var sortedByMathGrades = _.sortBy(records, function(record) {
        return parseInt(record.math.value, 10);
      });

      sortedByMathGrades = _.pluck(_.pluck(sortedByMathGrades, 'name'), 'value');
      var partners = _.chunk(sortedByMathGrades, 2);

      var groups = '';
      for (var j = 0; j < partners.length; j++) {
        groups += '• ' + partners[j][0] + ' and ' + partners[j][1] + ' ';
      }

      var p3 = document.createElement('p');
      p3.innerHTML = 'The study groups for math class are: ' + groups;
      header.appendChild(p3);

    });
  });
})();

Type the following code into a text editor and save it as a JavaScript file. Upload it to the App's JavaScript and CSS Customization settings (External link) . The settings should look like the following:

caution
Attention

Caution: The order in which JavaScript and CSS are uploaded to an app matters. In this example, ensure that the Underscore.js library is uploaded before the sample JavaScript file. The order of the uploads can be changed by clicking and dragging on the arrows for each item on the Upload JavaScript / CSS page.

Save the JavaScript and CSS Customization settings and update the App. Add a few records into the App, then navigate to the Record List page. The summary of the grades should be displayed above the list of records.

Code Explanation

kintone.events.on Event

1
kintone.events.on('app.record.index.show', function(event) {...});

An event is triggered when the App's list view is displayed. The rest of the program runs after this trigger.
For more information, refer to the Kintone Event Handling API article.

fetchRecords Function

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
function fetchRecords(appId, opt_offset, opt_limit, opt_records) {
  var offset = opt_offset || 0;
  var limit = opt_limit || 100;
  var allRecords = opt_records || [];
  var params = {app: appId, query: 'limit ' + limit + ' offset ' + offset};
  return kintone.api('/k/v1/records', 'GET', params).then(function(resp) {
    allRecords = allRecords.concat(resp.records);
    if (resp.records.length === limit) {
      return fetchRecords(appId, offset + limit, limit, allRecords);
    }
    return allRecords;
  });
}

This section declares a function that calls the Get Records REST API to retrieve all the records available in the App. Although the Record Index Show event specified in the previous code returns the App's records in the event object, the maximum number of records that can be returned with the event object is 100 (the total number of records that can be displayed in one page on the record list page). The fetchRecords function is used instead to make sure that all records are retrieved no matter the total number of records.

Return if ID Already Exists

1
2
3
if (document.getElementById('p1') !== null) {
  return;
}

This section checks to see if the HTML element with the ID p1 already exists. This is necessary so that the fetchRecords function is not triggered each time the user proceeds or goes back a page of records since it is unnecessary to retrieve the records each time, if all records are retrieved when the record list is initially loaded. Therefore, if the p1 element already exists, the function is terminated with a return statement, and the program is ended. If p1 does not exist, the program continues.

Get Header Space Element

1
var header = kintone.app.getHeaderSpaceElement();

The header space element is retrieved so that the data can be displayed in the header. It is placed in the variable header to reference later.

Execute fetchRecords Function

1
fetchRecords(kintone.app.getId()).then(function(records) {...});

The fetchRecords function is run.

_.max Helper Function

1
2
3
4
5
6
7
8
var maxSciScore = _.max(records, function(record) {
  return parseInt(record.science.value, 10);
});

var p1 = document.createElement('p');
p1.id = 'p1';
p1.innerHTML = 'The student with the highest science score is: ' + maxSciScore.name.value;
header.appendChild(p1);

The _.max helper function of Underscore.js is used to find the student whose science score is the highest. The createElement method is used to create a new p element. Text is placed in that states the student with the highest science score is the one that was determined by the _.max helper function.

_.filter Helper Function

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
var highGrades = _.filter(records, function(record) {
  return (parseInt(record.math.value, 10) + parseInt(record.language_arts.value, 10) +
            parseInt(record.science.value, 10) + parseInt(record.social_studies.value, 10) +
            parseInt(record.pe.value, 10)) / 5 >= 90;
});

var highGradeStudents = '';
for (var i = 0; i < highGrades.length; i++) {
  highGradeStudents += '• ' + highGrades[i].name.value + ' ';
}

var p2 = document.createElement('p');
p2.innerHTML = 'The students with above a 90 grade average are: ' + highGradeStudents;
header.appendChild(p2);

The _.filter helper function of Underscore.js is used to find the students whose average score of all five subjects is 90 or higher. A new variable highGradeStudents is created to create a string of students who meet the criteria. The createElement method is used to create a new p element. Text is placed in that states the students with an average score of 90 or higher are the ones determined by the _.filter helper function.

_.sortBy, _.pluck, and _.chunk Helper Functions

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
var sortedByMathGrades = _.sortBy(records, function(record) {
  return parseInt(record.math.value, 10);
});

sortedByMathGrades = _.pluck(_.pluck(sortedByMathGrades, 'name'), 'value');
var partners = _.chunk(sortedByMathGrades, 2);

var groups = '';
for (var j = 0; j < partners.length; j++) {
  groups += '• ' + partners[j][0] + ' and ' + partners[j][1] + ' ';
}

var p3 = document.createElement('p');
p3.innerHTML = 'The study groups for math class are: ' + groups;
header.appendChild(p3);

The _.sortBy, _.pluck, and _.chunk helper functions are used to sort the students by their math grades and create pairs of students whose grades are the most similar.

First, the _.sortBy helper function is used to sort the students by their math grade and insert the array of students into a variable called sortedByMathGrades.

Then, the _.pluck helper function is used to extract the name property value from the sorted students and replace the sortedByMathGrades with only the name property.

Next, the _.chunk helper function is used to group the students into pairs. A new variable groups is created to put the partners together into a string.

Finally, the createElement method is used to create a new p element. Text is plaed in that states who the student pairs are for study groups in math class.

Reference