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 Field Code of the Company Name field (Text field) to p_and_c_company_name
The Element ID of the Blank Space field to blank_space
The Field Code of the Sales field (Related records field) to related_records_filtered
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:
The Field Code of the Company Name (Lookup field) to s_and_l_company_name
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
.
(function() {
'use strict';
var RELATEDRECORDS = 'related_records_filtered'; // Field code of the Sales (Related Records) field in the Prospects & Customers App
var 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
var FETCH_CRITERIA_A = 'p_and_c_company_name'; // Field code of the Company Name (Text) field in the Prospects & Customers App
var FETCH_CRITERIA_B = 's_and_l_company_name'; // Field code of the Company Name (Lookup) field in the Sales & Leads App
kintone.events.on('app.record.detail.show', function(event) {
// Get all records related to the related records field
function fetchRecords(opt_Field, opt_offset, opt_limit, opt_records) {
var Id = kintone.app.getRelatedRecordsTargetAppId(RELATEDRECORDS);
var offset = opt_offset || 0;
var limit = opt_limit || 100;
var allRecords = opt_records || [];
var params = {app: Id, query: opt_Field + ' order by $id asc 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(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(function(resp) {
var filter = resp.properties[RELATEDRECORDS].referenceTable.filterCond;
var keyValue = event.record[FETCH_CRITERIA_A].value;
var opt_Field = '';
if (filter) {
opt_Field = FETCH_CRITERIA_B + '="' + keyValue + '" and ' + filter;
} else {
opt_Field = FETCH_CRITERIA_B + '="' + keyValue + '"';
}
return fetchRecords(opt_Field);
}).then(function(records) {
// Insert the total number of records into the Space field
var num = records.length;
var 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(function(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.