Utilizing Table Data

Introduction

This article introduces how to retrieve data from a table in your record details view using JavaScript.

Preparing the App

First, prepare an app with a table inside.
For information on how to create tables, check the kintone help page


The app in this example has the fields below. Make sure to also set the Field Code of each field in their field options.

Field Type Field Name Field Code
Date Date Date
Table - order_items
Blank Space - sub_total_area
Created Datetime Created Datetime Created_Datetime
Updated Datetime Updated Datetime Updated_Datetime


The table(order_items) listed above, is structured as below:

Field Type Field Name Field Code
Single choice Category Category
Single-Line Text Product Name Product_Name
Number Unit Price($) Unit_Price
Number Amount Amount

Getting data from the table

First, place some data in the record. Here's an example:



As with getting any other record data, you can get table data by specifying the field code of the table in the event object, when an event is triggered. You would get this data byThis would look something like this:

var tableRecords = event.record.order_items.value;

You can check the page for field formats for the data format of tables, which would look like the below:

Now as an example, let's go ahead to obtain the product name listed in the second row of the table.
Note that the table data is in an array, and that arrays start from 0. To specify the second row, you will have to specify array 1.

	(function() {
		"use strict";
		kintone.events.on('app.record.detail.show', function(event) {
			if (!event.record) {
				return;
			}

			//Obtaining field code "order items" record object
			var tableRecords = event.record.order_items.value;

			//Obtaining the value of second "Product Name" field
			var secondItemName = tableRecords[1].value['Product_Name'].value;

			alert(secondItemName);
		});
	})();

You may notice that when you run this script, the alert dialog will show up before the table data is displayed. This is because the 'app.record.detail.show' event runs before tables are displayed, and the alert dialog stops the page until the dialog is confirmed. This isn't a problem right now, but it may be something to keep in mind for future customizations.

Calculating with data in tables

Now that we know how to get data from a table, we can use this skill for other calculations.
For example, here is a script to work out the total earnings per category and to display it into the Space field:

	(function() {
		"use strict";
		kintone.events.on('app.record.detail.show', function(event) {
			if (!event.record) {
				return;
			}

			var subTotals = new Array();
			var tableRecords = event.record.order_items.value;

			// Summing each record in the table by category
			for (var i = 0; i < tableRecords.length; i++) {
				var category = tableRecords[i].value['Category'].value;
				if (!subTotals[category]) {
					subTotals[category] = 0;
				}
				var number = tableRecords[i].value['Amount'].value;
				var price = tableRecords[i].value['Unit_Price'].value;
				subTotals[category] += price * number;
			}

			// Displaying the total result in the space field
			var myTotalSpace = document.createElement('ul');
			for (var category in subTotals) {
				var categorySpace = document.createElement('li');
				categorySpace.innerHTML = category + ': ' + '$' + subTotals[category];
				myTotalSpace.appendChild(categorySpace);
			}

			kintone.app.record.getSpaceElement('sub_total_area').appendChild(myTotalSpace);
		});
	})();

Finally

In this article, the final output was displayed on a Space field, so this data was not necessarily saved onto the record itself. If you would like to insert the calculations into a number or text field (so the data will be saved onto kintone), use the app.record.create.submit and app.record.edit.submit events and overwrite data of the event object instead of using the app.record.detail.show event.
The API documents for these events explain how to do this

Was this article helpful?
0 out of 0 found this helpful
Comments
Please sign in to leave a comment.