I am calculating total of number field and showing it on top of record list. It was working for few days, but now total seems wrong when I add more records. Also, when I change sort order of list, total value changes. I don't understand why sorting can change a total. Any help or advice please!
Current Situation
I am building a POC for handling sales data. Right now I am making a mock with dummy data. My customization calculates total of amount field and displays it at top of the app list view.
It was working okay at first. But recently when I add new records the total does not go up as much as I expect. Sometimes total goes down??? Then I accidentally click header column and sorted the list, and I saw the total value change. When I change asc/desc order of other fields, total number changes.
App Configuration
I have two number fields calculated to output into amount field.
I use JavaScript customization to calculate total of amount field when list view event is triggered.
(function() {
'use strict';
kintone.events.on('app.record.index.show', function(event) {
let records = event.records;
let total = 0;
records.forEach(function(record) {
total += Number(record.amount.value);
});
let spaceElement = kintone.app.getHeaderMenuSpaceElement();
spaceElement.innerHTML = 'Total Amount in All Records: ' + total;
return event;
});
})();
Error Message
No error messages in console
Screenshot
You can see total number changing when order is different
I want total of amount to be calculated from the App and be displayed at top of App.
I don't want total to change when no data is edited. Total amount should only change when new data is added, or current data is updated.
Based on your script, it appears that it is calculating only the records displayed on the current list page because it is using event.records. As a result, when you change the sort order, a different set of records may be displayed on the current page, which would explain why the total changes.
To calculate the total across the entire app, you can use the Get Records REST API to retrieve all applicable records and calculate the total from the API response instead of using event.records. Please note that a single Get Records request can retrieve up to 500 records, so if your app contains more than 500 records, multiple requests will be required to retrieve all records.
Here are a few additional help pages you can refer to while writing your script:
Yes I am using event object to get record data. I did not realize 100 records was the max amount of data in event object. I have more than 100 records in kintone app
Is there any way to increase max records stored in event object?
Or should I ignore event object and go ahead to use REST API to get all record data?
According to the Kintone documentation (Noted on the Limitations), the maximum number of records that can be stored in event.records is 100, and this limit cannot be increased. If you navigate to another page in the record list, the Event Object is updated with the records from that page.
Thus, if you need to calculate the total across more than 100 records, I would recommend using the Get Records REST API instead of event.records. The Get Records API can retrieve up to 500 records per request, and you can make multiple requests if your app contains more than 500 records.
So in your case, since your app contains more than 100 records, using the REST API would be the recommended approach to ensure the total is calculated correctly regardless of the current page or sort order.
(function() {
'use strict';
function getAllRecords(lastRecordId, records) {
//Pass on empty array for first call
records = records || [];
//Add no record related query for first call
let query = lastRecordId ? '$id > ' + lastRecordId : '';
query += ' order by $id asc limit 500';
let params = {
app: kintone.app.getId(),
query: query
};
return kintone.api('/k/v1/records', 'GET', params).then(function(resp) {
records = records.concat(resp.records);
if (resp.records.length === 500) {
return getAllRecords(resp.records[resp.records.length - 1].$id.value, records);
}
return records;
});
}
kintone.events.on('app.record.index.show', function(event) {
getAllRecords().then(function(records) {
let total = 0;
records.forEach(function(record) {
total += Number(record.amount.value);
});
let spaceElement = kintone.app.getHeaderMenuSpaceElement();
spaceElement.innerHTML = 'Total Amount in All Records: ' + total;
});
return event;
});
})();
