Info

In this use case, you will learn how to create internal shopping lists in a grid. Using the Driving Table feature to create a product list and the formula column to calculate the total of the purchase.

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

Data Mirror

How to add option attributes to a list

How to fill in a list with dynamic options

Table of Contents

style	none

Introduction

You can create a stand-alone list of products using the Driving Table. Then use it in a grid as a dynamic list of available products.

The dynamic list is based on a Driving Table, maintained in the Table Grid Next Generation admin. You can save the table in the database using the Data Mirror.

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.

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,..

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:
Code Block
SELECT column1, column2, ... FROM table_name;

Flow chart diagram

Drawio

mVer	2
zoom	1
simple	0
inComment	0
custContentId	100335708
pageId	99975291
lbox	1
diagramDisplayName	Untitled Diagram-1713868081051.drawio
contentVer	4
revision	4
baseUrl	https://tablegrid.atlassian.net/wiki
diagramName	Untitled Diagram-1713868081051.drawio
pCenter	1
width	511.00000000000006
links
tbstyle
height	2191

Configure Shopping List 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 = “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

Note
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, chooseyour databasefor Data Source and type the SQL query:

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

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

Code Block
return $(jprice)*$(jquantity)

Click Add to save the information

25

Click Save to save the grid configuration

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 #24 in the Basic Flow:

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

Use Shopping List 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 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

Create an issue with Shopping List

Open your project
Click on Navigation bar to create an issue
Click to add row and fill the information

Product: You can select the product from the dynamic list which is configured as a Driving Table.

Price: When you select a product from the drop-down list, the price is added automatically to the grid. We’ve used a JavaScript-based formula column type to get the price.

Quantity: You can type any number.

Total: The total quote for each product is calculated automatically using the formula column.

You can update the list of products and change prices in the stand-alone table used to store product-related information. This does not require changing your grid configuration.

Version	Old Version 1	New Version Current
Changes made by	Hung Techlead	Hung Techlead
Saved on	May 02, 2024	May 02, 2024

Page Comparison

Versions Compared

Key

Introduction

Prerequisites

Post-condition

Conditions

Flow chart diagram

Configure Shopping List Grid

Basic Flow

Exceptional Flow

Use Shopping List Grid in a Jira project

Create a Jira project

Add grid on project screen

Create an issue with Shopping List