Display the Total Number of Related Records

Contents

Overview

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.

Screenshot: 3 different Related records fields, each with the total number of records displayed above the 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.

Screenshot: The 3 Apps created from adding the Sales CRM pack.

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

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 (External link).

 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
(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', 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.

Animated GIF: The number of records displayed in the Blank space field and the number of records within the Related records fields match.