Code Examples For Bulk Record Retrieval

Contents

Overview

This article introduces sample codes for three bulk record retrieval methods.

  • The Seek Method - used when the records are not sorted by any particular criteria. (i.e., the records are sorted just by the Record ID).
  • The Cursor API Method - used when the records are sorted, and more than 10,000 records are expected to be retrieved.
  • The Offset Method - used when the records are sorted, and fewer than 10,000 records are retrieved.

Prepare the App

Navigate to the Kintone Marketplace and search for the Ticketing App. The App is within the NPO Scenario section. Add this App while the option for With Sample Data is checked. The code examples refer to the default field codes of the Ticketing App, so there is no need to modify the App to run the example bulk record retrieval codes.
For help with creating Apps from the Kintone Marketplace, refer to the following Creating an App from the Marketplace (External link) article in the Kintone Help site.

Sample Code

The Seek Method

The Seek Method uses the Get Records API to sort and retrieve the records by the Record ID, in ascending order. For more information on the Seek Method and the suitable scenarios in using it, refer to the following article:

Navigate to the Ticketing App, and open the browser's developer tools. Type the following code into the console and run it.

 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
78
79
80
81
82
83

/*
 * @param {Object} params
 * - app {String}:        APP ID (Default value is current app id)
 * - filterCond {String}: Query condition
 * - sortConds {Array}:   Sort conditions
 * - fields {Array}:      Fields to retrieved
 * @return {Object} response
 * - records {Array}:     Target records
 */
var getRecords = function(_params) {
  var MAX_READ_LIMIT = 500;

  var params = _params || {};
  var app = params.app || kintone.app.getId();
  var filterCond = params.filterCond;
  var sortConds = params.sortConds || ['$id asc'];
  var fields = params.fields;
  var data = params.data;

  if (!data) {
    data = {
      records: [],
      lastRecordId: 0
    };
  }

  var conditions = [];
  var limit = MAX_READ_LIMIT;
  if (filterCond) {
    conditions.push(filterCond);
  }

  conditions.push('$id > ' + data.lastRecordId);

  var sortCondsAndLimit =
    ' order by ' + sortConds.join(', ') + ' limit ' + limit;
  var query = conditions.join(' and ') + sortCondsAndLimit;
  var body = {
    app: app,
    query: query
  };

  if (fields && fields.length > 0) {
    // To sort by $id, add a "$id" field if it is not included in the fields array.
    if (fields.indexOf('$id') <= -1) {
      fields.push('$id');
    }
    body.fields = fields;
  }

  return kintone.api(kintone.api.url('/k/v1/records.json', true), 'GET', body).then(function(r) {
    data.records = data.records.concat(r.records);
    if (r.records.length === limit) {
      // Call getRecords function recursively to retrieve more records than the limit.
      data.lastRecordId = r.records[r.records.length - 1].$id.value;
      return getRecords({app: app, filterCond: filterCond, sortConds: sortConds, fields: fields, data: data});
    }
    delete data.lastRecordId;
    return data;
  });
};

/* Query parameters:
  * filter by
    * status in ("Not started")
    * end_date <= THIS_YEAR()
  * order by
    * $id asc
    * start_date desc
    * project_name asc
  * limit 500
 */

var params = {
  // If not specified, currently viewing App's App ID is used.
  // app: 1,
  filterCond: 'Status in ("Not started") and Created_datetime <= THIS_YEAR()',
  sortConds: ['ContactName asc'], // Sort condition is specified by 'field_code asc or desc'
  fields: ['RecNumber', 'ContactName', 'Email', 'TicketDescription', 'DateTime']
};
getRecords(params).then(function(resp) {
  console.log(resp);
});

Data of all records inside the App should be output into the console.

The Cursor API Method

This method uses the Get Cursor API to retrieve the records. For more information on the Cursor API method and the suitable scenarios in using it, refer to the following article:

Navigate to the Ticketing App, and open the browser's developer tools. Type the following code into the console and run it.

 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
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93

// Create a cursor using Add Cursor API
var postCursor = function(_params) {
  var MAX_READ_LIMIT = 500;

  var params = _params || {};
  var app = params.app || kintone.app.getId();
  var filterCond = params.filterCond;
  var sortConds = params.sortConds;
  var fields = params.fields;

  var conditions = [];
  if (filterCond) {
    conditions.push(filterCond);
  }

  var sortCondsAndLimit =
    (sortConds && sortConds.length > 0 ? ' order by ' + sortConds.join(', ') : '');
  var query = conditions.join(' and ') + sortCondsAndLimit;
  var body = {
    app: app,
    query: query,
    size: MAX_READ_LIMIT
  };
  if (fields && fields.length > 0) {
    body.fields = fields;
  }

  return kintone.api(kintone.api.url('/k/v1/records/cursor.json', true), 'POST', body).then(function(r) {
    return r.id;
  });
};

// Retrieve records from the cursor using Get Cursor API
var getRecordsByCursorId = function(_params) {
  var params = _params || {};
  var id = params.id;

  var data = params.data;

  if (!data) {
    data = {
      records: []
    };
  }

  var body = {
    id: id
  };
  return kintone.api(kintone.api.url('/k/v1/records/cursor.json', true), 'GET', body).then(function(r) {
    data.records = data.records.concat(r.records);
    if (r.next) {
      return getRecordsByCursorId({id: id, data: data});
    }
    return data;
  });
};

/*
 * @param {Object} params
 * - app {String}:        APP ID (Default value is current app id)
 * - filterCond {String}: Query condition
 * - sortConds {Array}:   Sorting conditions
 * - fields {Array}:      Fields to retrieve
 * @return {Object} response
 * - records {Array}:     Target records
 */
var getRecords = function(_params) {
  return postCursor(_params).then(function(id) {
    return getRecordsByCursorId({id: id});
  });
};

/* Query parameters:
  * filter by
    * status in ("Not started")
    * end_date <= THIS_YEAR()
  * order by
    * $id asc
    * start_date desc
    * project_name asc
  * limit 500
 */

var params = {
  // If not specified, currently viewing App's App ID is used.
  // app: 1,
  filterCond: 'Status in ("Not started") and Created_datetime <= THIS_YEAR()',
  sortConds: ['ContactName asc'], // Sort condition is specified by 'field_code asc or desc'
  fields: ['RecNumber', 'ContactName', 'Email', 'TicketDescription', 'DateTime']
};
getRecords(params).then(function(resp) {
  console.log(resp);
});

Data of all records inside the App should be output into the console.

The Offset Method

The Offset Method uses the Get records API to retrieve records sequentially by specifying the offset as a request parameter. For more information on the Offset Method and the suitable scenarios in using it, refer to the following article:

Navigate to the Ticketing App, and open the browser's developer tools. Type the following code into the console and run it.

  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
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100

/*
 * @param {Object} _params
 * - app {String}:        APP ID (Default value is current app id)
 * - filterCond {String}: Query condition
 * - sortConds {Array}:   Sort conditions
 * - fields {Array}:      Fields to retrieved
 * - limit {Number}:      Number of records to be acquired (Default: All records that meet the filtering conditions are acquired)
 * @return {Object} response
 * - records {Array}: Target records
 */
var getRecords = function(_params) {
  var MAX_READ_LIMIT = 500;

  var params = _params || {};
  var app = params.app || kintone.app.getId();
  var filterCond = params.filterCond;
  var sortConds = params.sortConds;
  var limit = params.limit || -1;
  var offset = params.offset || 0;
  var fields = params.fields;
  var data = params.data;

  if (!data) {
    data = {
      records: []
    };
  }

  var willBeDone = false;
  var thisLimit = MAX_READ_LIMIT;

  // The number of records to acquired is specified with params.limit.
  // willBeDone is true when the next round of 500 record retrieval will satisfy the limit.

  if (limit > 0) {
    if (thisLimit > limit) {
      thisLimit = limit;
      willBeDone = true;
    }
  }

  var conditions = [];
  if (filterCond) {
    conditions.push(filterCond);
  }

  var sortCondsAndLimit = (sortConds && sortConds.length > 0 ? ' order by ' + sortConds.join(', ') : '')
    + ' limit ' + thisLimit;
  var query = conditions.join(' and ') + sortCondsAndLimit + ' offset ' + offset;
  var body = {
    app: app,
    query: query
  };
  if (fields && fields.length > 0) {
    body.fields = fields;
  }
  return kintone.api(kintone.api.url('/k/v1/records.json', true), 'GET', body).then(function(resp) {
    data.records = data.records.concat(resp.records);
    var _offset = resp.records.length;
    if (limit > 0 && limit < _offset) {
      willBeDone = true;
    }
    // Exit when all the desired records are acquired.
    if (_offset < thisLimit || willBeDone) {
      return data;
    }
    // Recursively call to retrieve the remaining records.
    return getRecords({
      app: app,
      filterCond: filterCond,
      sortConds: sortConds,
      limit: limit - _offset,
      offset: offset + _offset,
      fields: fields,
      data: data
    });
  });
};

/* Query parameters:
  * filter by
    * status in ("Not started")
    * end_date <= THIS_YEAR()
  * order by
    * $id asc
    * start_date desc
    * project_name asc
  * limit 500
 */

var params = {
  // If not specified, currently viewing App's App ID is used.
  // app: 1,
  filterCond: 'Status in ("Not started") and Created_datetime <= THIS_YEAR()',
  sortConds: ['ContactName asc'], // Sort condition is specified by 'field_code asc or desc'
  fields: ['RecNumber', 'ContactName', 'Email', 'TicketDescription', 'DateTime']
};
getRecords(params).then(function(resp) {
  console.log(resp);
});

Data of all records inside the App should be outputted into the console.

Choose the Appropriate Method

All 3 methods introduced in this article are good approaches to retrieving all records from an App. Each method has its own pros and cons which should be considered before usage. For more information on which method to use, refer to The Three Methods for Bulk Record Retrieval article.

Reference