This article introduces how to calculate the total number of records listed within a Related records field.
Sample Image
An event is triggered when the Record Details page is displayed. A Kintone REST API is called to retrieve records with queries matching the one used in the Related records field. The total number of retrieved records is calculated, and inserted into a Blank space field.
Prepare the App
Create the Sales CRM Pack
Navigate to the
Kintone Marketplace
and search for the Sales CRM Pack. Add the App Pack into the Kintone environment so that 3 Apps are created. This article will use the Prospects & Customers App and the Sales & Leads App.
Prepare the Prospects & Customers App
Navigate to the Prospects & Customers App. Add a new record into the App, and make sure to fill out the Company Name field. Access the App's settings and update the following form settings:
The Element ID of the Blank Space field to blank_space
Prepare the Sales & Leads App
Navigate to the Sales & Leads App. Add several records into the App, making sure to fill the Company Name field. This field looks up the company added in the earlier step. Access the App's settings and update the following form settings:
Check the Related Records field
Navigate back to the Prospects & Customers App. Access the Record Details page of a record, and check that the Related records field is displaying data pulled in from the Sales & Leads App.
Sample Code
Prepare the following JavaScript code in a text editor and navigate to the Kintone App's settings of the Prospects & Customers App. Upload the file into the Upload JavaScript for PC option of the
JavaScript and CSS Customization settings
.
(() => {
'use strict';
const RELATEDRECORDS = 'sales'; // Field code of the Sales (Related Records) field in the Prospects & Customers App
const SPACEFIELD = 'blank_space'; // Element ID of the Space field in the Prospects & Customers App
// Field codes of the fields set for the Fetch Criteria of the Related Records field
const FETCH_CRITERIA = 'company_name'; // Field code of the Company Name (Text) field in the Prospects & Customers App
kintone.events.on('app.record.detail.show', (event) => {
// Get all records related to the related records field
let fetchRecords = (opt_Field, opt_offset, opt_limit, opt_records) => {
let Id = kintone.app.getRelatedRecordsTargetAppId(RELATEDRECORDS);
let offset = opt_offset || 0;
let limit = opt_limit || 100;
let allRecords = opt_records || [];
let params = {app: Id, query: opt_Field + ' order by $id asc limit ' + limit + ' offset ' + offset};
return kintone.api('/k/v1/records', 'GET', params).then((resp) => {
allRecords = allRecords.concat(resp.records);
if (resp.records.length === limit) {
return fetchRecords(offset + limit, limit, allRecords);
}
return allRecords;
});
};
// Create query based on the Filter settings for the related records field
kintone.api(kintone.api.url('/k/v1/app/form/fields.json', true), 'GET', {
'app': kintone.app.getId()
}).then((resp) => {
let filter = resp.properties[RELATEDRECORDS].referenceTable.filterCond;
let keyValue = event.record[FETCH_CRITERIA].value;
let opt_Field = '';
if (filter) {
opt_Field = FETCH_CRITERIA + '="' + keyValue + '" and ' + filter;
} else {
opt_Field = FETCH_CRITERIA + '="' + keyValue + '"';
}
return fetchRecords(opt_Field);
}).then((records) => {
// Insert the total number of records into the Space field
let num = records.length;
let divTotalAmount = document.createElement('div');
divTotalAmount.style.textAlign = 'center';
divTotalAmount.style.fontSize = '16px';
divTotalAmount.innerHTML = String(num) + ' related sale(s)';
kintone.app.record.getSpaceElement(SPACEFIELD).appendChild(divTotalAmount);
return event;
}).catch((err) => {
console.log(err);
});
});
})();
After saving the settings and clicking on Update App, navigate to a record of the Prospects & Customers App. The number of related records should be displayed in the Blank space field.
and search for the Sales CRM Pack. Add the App Pack into the Kintone environment so that 3 Apps are created. This article will use the Prospects & Customers App and the Sales & Leads App.