Query Jira values to add dynamic grid data

Owned by svitlana (Deactivated)
Last updated: May 02, 2024 by Hung Techlead
10 min read

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

Data Mirror

How to add option attributes to a list

How to fill in a list with dynamic options

Jira values placeholders

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

Action

1

Click Add Data Source in Data Source

image-20240424-043311.png
2

Fill all required information in Data Source

image-20240424-044139.png
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 image-20240424-051445.png to add row in the Driving Table
Fill the license information into Driving Table

Click image-20240424-052023.png to save the Driving Table

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
So they will not disappear when they are blank

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

  • Name = “Licenses”

  • Description = “Licenses use case”

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?

  1. Navigate to Jira Administration, click on Issues

  2. In Fields section, choose Custom fields

  3. Click image-20240427-092554.png button on a custom field and select Configure Context

  4. You can see the Custom field ID in the URL

 

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}

 

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

In Option attributes, click image-20240424-052712.png to add option attribute

Type “price” as Key

In Dynamic options, choose your database for Data Source and type the SQL query:

 

In Mapping:

  • for label, select “jlicense”

  • for price, select “jprice”

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 image-20240424-052712.png to add option attribute

Type “percent” as Key

In Static options, click image-20240424-052712.png to add static option

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:

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

  1. Click Project on the Navigation bar

  2. Click Create project

  3. Fill required information to create project

Add Grid on Project screen

  1. Click Issues tab, in Screens section choose Screens

  2. Click Configure on your project

  1. Select Licenses as a custom field
    Click Add to add field on project screen
    Turn image-20240424-090222.png the field to show it on project screen even when empty

Use License Grid on Issue view

  1. In Company Name, type your company name

  2. In Company Size, select one option:

    Click image-20240427-104940.png to save

  3. In License Type, select one option:

    Click image-20240427-104940.png to save

  4. In License Grid, click image-20240427-105600.png button to add row
    Select a License

  5. Select Discount

     

  6. The Price value is calculated automatically

    Click image-20240427-104940.png to save the grid

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.