In this use case, you will learn how to use a simple script to get the values from the grid table and reuse these values in JQL and so on.

You will also learn how to use several features in TGNG app such as:

Introduction

This article shows an example of how you can extract the grid data using the API and a scripted field in Jira

The scripted field is a custom field, provided by the Script Runner. To save the sum of the numbers in the grid column as a value of another custom field

Prerequisites

User logged into their account, installed Table Grid Next Generation Server / Datacenter app
User created a related Jira project
User had the admin role on Jira
User had a database system such as: MySQL, Postgres, SQL Server, etc
Table Grid with a dynamic list of products
User installed ScriptRunner app ScriptRunner for Jira | Atlassian Marketplace

Post-condition

User can use the grid to update related information for the list of products
User can create issues using the Shopping List grid
User can download/upload the Configuration for personal usage
User can add more rows to fill the shopping list such as: total, quantity,..
User can view the total amount in the Total amount script field

Conditions

Required fields are marked with a red asterisk *
The grid must contain at least one column, or else it will show the pop-up message
“Error table-config-column-config-required”.
The grid can be displayed in more than one project
User have to set the name of Column to be an exact match with Column of the database.
User need to type the correct statement for the “SQL query”. For example:
SELECT column1, column2, ... FROM table_name;

Flow chart diagram

Configure Grid and Script field

Basic Flow

	Action

	Action
1	Click Add Data Source in Data Source
2	Fill all required information in Data Source
3	Click Test Connection to examine the connection between database and Jira
4	Click Save to save data source
5	In Table Grid section, choose Grids
6	In Driving Table, click Add Driving Table button to add new driving table
7	Fill information for “Name” In this example, user used Name = “Products”
8	In Configuration - Column, click Add new column to create new column
9	In Add Column, choose String in Column type User fill information for Identifier and Title Type “code“ as Identifier and type “Code”* as Title* Click Add to save the information
10	In Add Column, choose String in Column type User fill information for Identifier and Title Type “name“ as Identifier and type “Name”* as Title* Click Add to save the information
11	In Add Column, choose Number in Column type User fill information for Identifier and Title Type “price“ as Identifier and type “Price”* as Title* Click Add to save the information
12	In Configuration - Data Mirror, Select the Data Source that you created in step #4 Fill the Database table name The Database table name* must be unique in your database* Click Create table to create table in your database
13	Click Save to save the Driving Table Config
14	Click to add row in the Driving Table Fill the product information into Driving Table Click to save the Driving Table
15	In Grid Fields, click Add Grid button to add new grid
16	Fill information for “Name” and “Description” In this example, user used Name = “Shopping List” Description = “Shopping List use case”
17	In Configuration - Column, click Add new column to create new column
18	In Add Column, choose Single Select List in Column type User fill information for Identifier and Title Type “jproduct“ as Identifier and type “Product”* as Title* To display the price automatically, use the dynamic option mapping. You need to map the option label to display the product name and the option value to display the price. In Option attributes, click to add option attribute Type “price” as Key In Dynamic options, choose your database for Data Source and type the SQL query: `SELECT name, price FROM products` Click Get data to get data from your database In Mapping: for label, select “name” for price, select “price” Click to save the dynamic options Click Add to save the information
19	In Configuration - Column, click Add new column to create new column
20	In Add Column, choose Formula in Column type User fill information for Identifier and Title Type “jprice“ as Identifier and type “Price”* as Title* In Formula expression, type: `var result; if($(jproduct).hasOwnProperty('optionObject')){ result = $(jproduct).optionObject.price; }else{ result = $(jproduct).price; } return result;` Click Add to save the information
21	In Configuration - Column, click Add new column to create new column
22	In Add Column, choose Integer in Column type User fill information for Identifier and Title Type “jquantity“ as Identifier and type “Quantity”* as Title* Click Add to save the information
23	In Configuration - Column, click Add new column to create new column
24	In Add Column, choose Formula in Column type User fill information for Identifier and Title Type “jtotal“ as Identifier and type “Total”* as Title* In Formula expression, type: `return $(jprice)$(jquantity)` Click Add* to save the information
25	Click Save to save the grid configuration
26	Navigate to ScriptRunner, choose Fields
27	Click Create Script Field
28	Click Custom Script Field
29	In Field Name, type “Total amount” In Template, select Text Field (multi-line)
30	In Script field, type the script The script below helps to create a scripted custom field that calculates the sum of all numbers in the specified column of the TGNG grid import com.atlassian.crowd.embedded.api.User; import com.atlassian.jira.component.ComponentAccessor; import com.atlassian.jira.issue.CustomFieldManager; import com.atlassian.jira.issue.fields.CustomField; import com.atlassian.jira.security.JiraAuthenticationContext; import com.atlassian.plugin.PluginAccessor; import com.atlassian.jira.user.ApplicationUser; import org.apache.log4j.Logger; // set up logger Logger log=Logger.getLogger("com.idalko.scripts"); // get an issue String tgngCustomFieldName = "TGNG Grid"; //Change the grid custom field name to relevant one String columnIdToSum = "price"; //Change the column ID to the relevant one // get TGNG custom field CustomFieldManager customFieldManager = ComponentAccessor.getOSGiComponentInstanceOfType(CustomFieldManager.class); CustomField tgngCustomField = customFieldManager.getCustomFieldObjectsByName(tgngCustomFieldName).get(0); Long tgngCustomFieldId = tgngCustomField.getIdAsLong(); ApplicationUser applicationUser = ComponentAccessor.getJiraAuthenticationContext().getLoggedInUser(); // read grid data PluginAccessor pluginAccessor = ComponentAccessor.getPluginAccessor(); Class apiServiceClass = pluginAccessor.getClassLoader().findClass("com.idalko.tgng.jira.server.api.GridService"); def gridFieldData = ComponentAccessor.getOSGiComponentInstanceOfType(apiServiceClass); def callResult = null; try { callResult = gridFieldData.readFieldData(issue.getId(), tgngCustomFieldId, applicationUser, null); } catch (Exception e) { log.error(e.getMessage()) } def gridRows = callResult.getRows(); Double sum = 0; for (row in gridRows) { def column = row.getColumns().get(columnIdToSum); sum += column.toDouble() } return sum; Note: You need to change the grid custom field name and column ID in the script as below: `// configuration (update it to match your JIRA settings) String tgngCustomFieldName = "TGNG Grid"; String columnIdToSum = "summary";` tgngCustomFieldName - the name of the TGNG grid custom field that will be used for calculation columnIdToSum - a column identifier(a unique variable to identify the column) which will be used to get data for the summary calculation Click on to view more example scripts
31	Click Add to save the Script Field

Exceptional Flow

Users clicks “Cancel” at “Data Source”

Continued from step #2 in the Basic Flow:

Step	Description
3	The system will automatically come back to the “Data Source” page
Use case stops.

Users type the wrong information about the data source

Continued from step #4 in the Basic Flow:

Step	Description
5	An “Errors” message appears
Use case stops.

Users type wrong SQL query

Continued from step #18 in the Basic Flow:

Step	Description
19	An “Errors” message appears
Use case stops.

Users click “Cancel” when saving the config

Continued from step #25 in the Basic Flow:

Step	Description
26	The system shows a pop-up message: “Exit without saving?”
27	User click Yes button
Use case stops.

Users click “Cancel” when saving the script field

Continued from step #31 in the Basic Flow:

Step	Description
32	The system will automatically come back to the “Script Fields” page
Use case stops.

Extract Total amount from Shopping List Grid

Create a Jira project

Click Project on the Navigation bar
Click Create project
Fill required information to create project

Add Grid and Script field on project screen

Click Issues tab, in Screens section choose Screens
Click Configure on your project

In Configure Screen, select Total amount & Shopping List as a custom field
Click Add to add field on project screen
Turn the field to show it on project screen even when empty