Extract Information from the Grid using the Java API
- svitlana (Deactivated)
- Hung Techlead
- andrii (Deactivated)
- Artem Ohanjanyan (Deactivated)
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:
Connecting an external data source
How to add option attributes to a list
How to fill in a list with dynamic options
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 https://marketplace.atlassian.com/apps/6820/scriptrunner-for-jira?tab=overview&hosting=cloud
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 | |
|---|---|
| 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
|
| 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 Click |
| 15 | In Grid Fields, click Add Grid button to add new grid |
| 16 | Fill information for “Name” and “Description” In this example, user used
|
| 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 Type “price” as Key In Dynamic options, choose your database for Data Source and type the SQL query: SELECT name, price FROM productsClick Get data to get data from your database In Mapping:
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: 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
|
| 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
Turnthe field to show it on project screen even when empty
Final result on issue screen
Open an issue of your project, click Edit the Shopping List field
Click
Click
to save the grid
Total amount is calculated automatically from Total values in Shopping List grid