Query Jira values to add dynamic grid data
- svitlana (Deactivated)
- Hung Techlead
In this use case, you will learn how to populate the grid with dynamic data from Jira objects using Jira values placeholders.
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
Introduction
You can query data from Jira using Jira values placeholders. For example, by initializing the grid with some default data when users create an issue.
Table Grid Next Generation allows you to populate a grid with database on the values from Jira fields.
These values are used when the grid is initialized for a specific issue or when the grid query is refreshed. You can get issue-specific values with the help of placeholder queries.
Jira values placeholders allow you to manipulate dynamic data in a Jira issue. This data can be either data from the external database or from your Jira database.
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.
User created a Driving Table including licenses information
User created custom fields to link the input to the grid
Table Grid with a dynamic list of licenses.
Post-condition
User can use the grid to update related information for the list of licenses
User can using the Licenses grid on the issue view
User can download/upload the Configuration for personal usage
User can add more rows to fill the licenses list such as: license, discount,…
Condition
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 License Grid
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 Name = “Licenses” |
| 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 “jcompanysize“ as Identifier and type “Company Size” 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 “jlicensetype“ as Identifier and type “License Type” as Title  Click Add to save the information |
| 11 | In Add Column, choose String in Column type User fill information for Identifier and Title Type “jlicense“ as Identifier and type “License” as Title  Click Add to save the information |
| 12 | In Add Column, choose Number in Column type User fill information for Identifier and Title Type “jprice“ as Identifier and type “Price” as Title  Click Add to save the information |
| 13 | 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 |
| 14 | Click Save to save the Driving Table Config |
| 15 | Click  Click |
| 16 | Click “Issue” tab, in Fields section choose Custom fields |
| 17 | In Custom Fields, click Add custom field button to add custom field |
| 18 | Select Text Field (single line), click Next  |
| 19 | Fill information for “Name” In this example, user used Name = “Custom Name” Click Next |
| 20 | Fill Configure context information  Click Create to create this custom field |
| 21 | Associate field to screen by tick on your project screen  Click Update to save the information |
| 22 | In Custom Fields, click Add custom field button to add custom field |
| 23 | Select Select List (single choice), click Next  |
| 24 | Fill information for “Name” In this example, user used Name = “Company Size” Add Options depending on the license information in Driving Table There are 2 options here: “1 - 500 users” and “501 - 2000 users” Type the option content and click Add button to add option  Click Next |
| 25 | Fill Configure context information, click Create to create this custom field |
| 26 | Associate field to screen by tick on your project screen Click Update to save the information |
| 27 | In Custom Fields, click Add custom field button to add custom field |
| 28 | Select Select List (single choice), click Next |
| 29 | Fill information for “Name” In this example, user used Name = “License Type” Add Options depending on the license information in Driving Table There are 2 options here: “Commercial” and “Permissive” Type the option content and click Add button to add option  Click Next |
| 30 | Fill Configure context information, click Create to create this custom field |
| 31 | Associate field to screen by tick on your project screen Click Update to save the information |
| 32 | In Screens section, choose Screens |
| 33 | In View Screens, click Configure on your project screen  |
| 34 | Turn On the option Show when empty and then these fields are always shown on the issue screen  |
| 35 | Click Manage Apps, in Table Grid section choose Grids |
| 36 | In Grid Fields, click Add Grid button to add new grid  |
| 37 | Fill information for “Name” and “Description” In this example, user used
 |
| 38 | In Configuration - Column, click Add new column to create new column |
| 39 | In Add Column, choose Sequence in Column type User fill information for Identifier and Title Type “jorder“ as Identifier and type “Order” as Title  Click Add to save the information |
| 40 | In Configuration - Column, click Add new column to create new column |
| 41 | In Add Column, choose String in Column type User fill information for Identifier and Title Type “jcompany“ as Identifier and type “Company” as Title Fill in Default value: {customfield:10527} In this case, “10527” is the custom field “Company name” ID How to know a Custom field ID?
 Click Add to save the information |
| 42 | In Configuration - Column, click Add new column to create new column |
| 43 | In Add Column, choose String in Column type User fill information for Identifier and Title Type “jsoldby“ as Identifier and type “Sold By” as Title Fill in Default value: {issue:assignee:full_name}Read more: Jira values placeholders
 Click Add to save the information |
| 44 | In Configuration - Column, click Add new column to create new column |
| 45 | In Add Column, choose Single Select List in Column type User fill information for Identifier and Title Type “jlicense“ as Identifier and type “License” as Title  To display the Price value automatically when select the Discount value, use the dynamic option mapping. You need to map the option label to display the license 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 jlicense, jprice FROM licenses WHERE jcompanysize = '{customfield:10532}' AND jlicensetype = '{customfield:10533}'In this case: {customfield:10533} is the custom field placeholder for calling the “License Type” field value For more information: Jira values placeholders | Custom Field values
 In Mapping:
Click to save the dynamic options Click Add to save the information |
| 46 | In Configuration - Column, click Add new column to create new column |
| 47 | In Add Column, choose Single Select List in Column type User fill information for Identifier and Title Type “jdiscount“ as Identifier and type “Discount” as Title  In Option attributes, click Type “percent” as Key  In Static options, click Type the value for 7 options: (No;0); (5%;5); (10%;10); (15%;15); (20%;20); (25%;25); (30%;30)  Click Add to save the information |
| 48 | In Configuration - Column, click Add new column to create new column |
| 49 | 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 price;
if($(jlicense).hasOwnProperty('optionObject')){
price = $(jlicense).optionObject.price;
}else{
price = $(jlicense).price;
}
var percent;
if($(jdiscount).hasOwnProperty('optionObject')){
percent = $(jdiscount).optionObject.percent;
}else{
percent = $(jdiscount).percent;
}
var discount = (price / 100 * percent)
if (discount) {
price = price - discount;
}
return price;In Summary Label, type: “Total:” In Aggregation operation, select “sum”  Click Add to save the information |
| 50 | Click Save to save the grid configuration  |
Exceptional Flow
Users click “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 the existed database table name
Continued from step #13 in the Basic Flow:
14 | An “Errors” message appears |
Use case stops. | |
Users type wrong SQL query
Continued from step #49 in the Basic Flow:
Step | Description |
50 | An “Errors” message appears |
Use case stops. | |
Users click “Cancel” when saving the config
Continued from step #50 in the Basic Flow:
Step | Description |
51 | The system shows a pop-up message: “Exit without saving?” |
52 | User click Yes button |
Use case stops. | |
Use License Grid in a Jira project
Create a Jira project
Click Project on the Navigation bar
Click Create project
Fill required information to create project
Add Grid on Project screen
Click Issues tab, in Screens section choose Screens
Click Configure on your project
Select Licenses as a custom field
Click Add to add field on project screen
Turnthe field to show it on project screen even when empty
Use License Grid on Issue view
In Company Name, type your company name
In Company Size, select one option:
Click
to save
In License Type, select one option:
Click
In License Grid, click
button to add row
Select a LicenseSelect Discount
The Price value is calculated automatically
Click
Set default column values based on the value from another Jira issue field
On the issue view screen in Jira there's a grid with some default values. These default values are set based on the values from different Jira fields.
Check the example below with the grid columns populated with values from other Jira issue fields:
Company column value in the grid is set automatically based on the value of the Company Name custom field
Sold By column value is using the value from the issue assignee field
Filter out the grid data depending on the value from another Jira field
Below you can see the issue view with custom fields and grid values.
