Kintone to Power BI Connection: Sub-tables only showing 500 records in Power BI

1

We have an App in Kintone that has sub-tables of data.

We’re using CData to connect Kintone to Power BI and noticed that while we are able to pull all records of the App into Power BI, the sub-tables within those records are only pulling the last 500 records in the sub-table, and not showing any older sub-table records.

How do we remove the limitation to pull all records in a sub-table in Power BI?

2

Hi Elena,

The “GET” bulk record retrieval process can retrieve information on up to 500 records(The default value is 100) in a single request.
By running the API repeatedly, you can retrieve more than 500 records.

(function() {
"use strict";

// Record list screen
kintone.events.on('app.record.index.show', function(event) {
var manager = new KintoneRecordManager;
manager.getRecords(function(records) {
// Process after record retrieval
console.log(records);
});

return event;
});

/**
* Class that communicates with kintone
*/
var KintoneRecordManager = (function() {
KintoneRecordManager.prototype.records = []; // Records retrieved
KintoneRecordManager.prototype.appId = null; // AppID
KintoneRecordManager.prototype.query = ''; // Search query
KintoneRecordManager.prototype.limit = 100; // Maximum number of retrievals per time
KintoneRecordManager.prototype.offset = 0; // Offset

function KintoneRecordManager() {
this.appId = kintone.app.getId();
this.records = [];
}

// Retrieve all records
KintoneRecordManager.prototype.getRecords = function(callback) {
kintone.api(
kintone.api.url('/k/v1/records', true),
'GET',
{
app: this.appId,
query: this.query + (' limit ' + this.limit + ' offset ' + this.offset)
},
(function(_this) {
return function(res) {
var len;
Array.prototype.push.apply(_this.records, res.records);
len = res.records.length;
_this.offset += len;
if (len < _this.limit) { // Are there any more records?
_this.ready = true;
if (callback !== null) {
callback(_this.records); // callback after getting the record
}
} else {
_this.getRecords(callback); // Call myself
}
};
})(this)
);
};
return KintoneRecordManager;
})();
})();


Please note that the maximum number of table rows in "kintone" is 5,000 rows.

Limitations:
https://developer.kintone.io/hc/en-us/articles/212495188-Kintone-REST-API-Overview

If you are asking about the tables, the “GET” process can retrieve up to 5,000 rows of records at once,
and I would recommend checking to see if the CData setting has any effect.

The response for the table will be as follows

{type: “SUBTABLE”, value: Array(5000)}

Hopefully, this helps.