Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Info

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

Table of Contents
stylenone

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:

    Code Block
    languagejs
    SELECT column1, column2, ... FROM table_name;

Flow chart diagram

Drawio
mVer2
zoom1
simple0
inComment0
custContentId102891690
pageId102826039
lbox1
diagramDisplayNameUntitled Diagram-1714122026851.drawio
contentVer2
revision2
baseUrlhttps://tablegrid.atlassian.net/wiki
diagramNameUntitled Diagram-1714122026851.drawio
pCenter1
width481
links
tbstyle
height2061

Configure License Grid

Basic Flow

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

image-20240424-045524.png
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

image-20240426-105156.png

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

image-20240427-075306.png

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

image-20240427-080238.png

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

image-20240427-080307.png

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

Note

The Database table name must be unique in your database

image-20240427-080515.png

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

image-20240427-080719.png

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

image-20240427-081744.png
19

Fill information for “Name

In this example, user used Name = “Custom Name

Click Next

20

Fill Configure context information

image-20240427-082000.png

Click Create to create this custom field

21

Associate field to screen by tick on your project screen

image-20240427-083308.png

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

image-20240427-082244.png
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

image-20240427-082548.png

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

image-20240427-082244.png
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

image-20240427-083810.png

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

image-20240427-084053.png
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

image-20240427-085609.png
35

Click Manage Apps, in Table Grid section choose Grids 

36

In Grid Fields, click Add Grid button to add new grid

image-20240424-044608.png
37

Fill information for “Name” and “Description” 

In this example, user used

  • Name = “Licenses

  • Description = “Licenses use case

image-20240427-090738.png
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

image-20240427-090847.png

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:

Code Block
{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

image-20240427-092800.png

image-20240427-091044.png

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:

Code Block
{issue:assignee:full_name}

image-20240427-093428.png

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

image-20240427-094223.png

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

Type “price” as Key

image-20240424-093702.png

In Dynamic options, chooseyour databasefor Data Source and type the SQL query:

Code Block
languagejs
SELECT jlicense, jprice FROM licenses WHERE jcompanysize = '{customfield:10532}' AND jlicensetype = '{customfield:10533}'

In this case:
{customfield:10532} is the custom field placeholder for calling the “Company Size” field value

{customfield:10533} is the custom field placeholder for calling the “License Type” field value

For more information: https://tablegrid.atlassian.net/wiki/spaces/TGNG/pages/1933710/Jira+values+placeholders#Custom-Field-values

image-20240427-094837.png

In Mapping:

  • for label, select “jlicense”

  • for price, select “jprice”

Click (blue star) 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

image-20240427-095056.png

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

Type “percent” as Key

image-20240427-100029.png

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)

image-20240427-100116.png

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

image-20240427-100858.png

In Formula expression, type:

Code Block
languagejs
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”

image-20240427-101119.png

Click Add to save the information 

50

Click Save to save the grid configuration

image-20240427-101534.png

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

image-20240424-084100.png

Add Grid on Project screen

  1. Click Issues tab, in Screens section choose Screens

  2. Click Configure on your project

image-20240424-085725.png
  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

    image-20240427-103044.png

Use License Grid on Issue view

  1. In Company Name, type your company name

  2. In Company Size, select one option:

    image-20240427-104853.png

    Click image-20240427-104940.png to save

  3. In License Type, select one option:

    image-20240427-105049.png

    Click image-20240427-104940.png to save

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

    image-20240427-105454.png
  5. Select Discount

    image-20240427-105730.png

  6. The Price value is calculated automatically

    image-20240427-105941.png

    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 

image-20240427-103905.png

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. 

image-20240427-104251.png