In this use case you will learn how to build a Sandwich order management system using Table Grid Next Generation app.

You will also learn how to use external datasource in TGNG app and how to use external data in a select list in a grid column configuration:

Contents

Introduction

The Sandwich order management is using an external datasource to collect the sandwich list that our restaurant will propose to customers. Our restaurant will use a Jira project to manager the sandwich orders from customers: each issue in the Jira project will be an order.

In order to use the data from an external datasource, you will have to use an external database system such as: MySQL, Postgres, SQL Server, etc. In this example, we will use MySQL as our external database system.

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.

Post-condition

User can use the grid to update related information for the list of items
User can create issues using the Sandwich grid
User can download/upload the Configuration for personal usage
User can add more columns to support the order management such as: total, amount,..

Conditions

Required fields are marked with a red asterisk *
User can use formulas in Table Grid Next Generation as a separate column or as an aggregation option for other column types. User can input formulas inside the embedded JavaScript Editor and get results on the grid view. The grid can process formula logic and display formula results. Error types in the formula column type

#error - syntax error

#type - operation error (operation could not be executed using different column types)

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;

Each grid must contain at least 1 column, or else it will show the pop-up message “table-config-column-config-required”

Use case Flow Diagram

1. Create database in MySQL

To create a database in MySQL, we expect that you are connected to MySQL using a terminal in Linux / Mac or a batch command in Windows.

Here is the database that we will create using SQL language.
The database is called SANDWICH.

CREATE DATABASE SANDWICH;
use SANDWICH;

Here is the table that we will create in order to store our sandwiches data.
The table is called sandwich_menu

CREATE TABLE sandwich_menu(
sandwich_id SERIAL PRIMARY KEY,
sandwich_name VARCHAR(45),
sandwich_price DECIMAL(5,2)
);

2. Insert data in MySQL table

After creating the database and the table, we can now insert data into the recently created table.

INSERT INTO sandwich_menu
VALUES (1,'Lemon pepper chicken',1.95),
(2,'Chicken tikka with minted mayo',1.95),
(3,'Chicken and bacon',1.95),
(4,'Cajun chicken',1.95),
(5,'Chinese chicken',1.95),
(6,'BBQ chicken',1.95),
(7,'Mexican chicken',1.95),
(8,'Tuna mayo',1.65),
(9,'Tuna crunch',1.85),
(10,'Egg mayo',1.5),
(11,'Egg mayo & crispy bacon',1.95),
(12,'Prawn & marie rose sauce',2.2),
(13,'Cheese',1.3),
(14,'Cheese mix',1.6),
(15,'Ham',1.8),
(16,'Beef',1.8),
(17,'Corned beef',1.8),
(18,'Turkey',1.8);

3. Configure a grid using Table Grid Next Generation app

Basic flow

	Action
1	Click “Add Data Source” in “Data Source”
2	Fill all required information in “Data Source” As we will refer to the datasource name later in this usecase, the name we are using is now Name = Sandwich
3	Click “Test Connection” to examine the connection between database and Jira
4	Click “Save” to save information
5	Click “Grids” in “Table Grid” section on the left side of the screen
6	Create “Add Grid”
7	Fill information for “Name” and “Description” Name = Sandwich Order Description = add row to order sandwiches
8	In “Configuration” - “Column”, click “Add new column” to create new column
9	In “Add Column”, choose Sequence in “Column type”
10	Type “jmenu“ as Identifier and type sandwich_menu as column Title
11	Click ”Add” to save the information
12	In “Configuration”, click “Add new column” to create new column
13	In “Add Column”, choose Single Select List in “Column type”
14	Type “jname” as Identifier and Type the sandwich_name as column Title
15	In “Option attributes”, click “+” and type “price”
16	In Dynamic options, choose “sandwich” for “Data Source” and type the “SQL query”: SELECT * FROM sandwich_menu; In “Mapping”: For “label”, choose “Sandwich_name” For “price”, choose “Sandwich_price”
17	Click “Add” to save the information
18	In “Configuration”, click “Add new Column” to create new column
19	In “Add Column”, choose Formula in “Column type”
20	Type the “sandwich_price” for “Table” and write “Identifier”
21	In “Formula expression”, type: return $(jname).price
22	Click “Add” to save the information
23	In “Configuration”, click “Add new Column” to create new column
24	In “Add Column”, choose Interger in “Column type”
25	Type “jamount” as Identifier and Type the Amount as column Title
26	Click “Add” to save the information
27	In “Configuration”, click “Add new Column” to create new column
28	In “Add Column”, choose “Formula” in “Column type”
29	Type “jtotal” as Identifier and Type the “Total” as column Title
30	In “Formula Expression”, type: return $(jamount) * $(jprice)
31	Click “Add” button to save the column configuration
32	Click “Save” button to save the grid configuration
33	Then using Jira Software Product…
34	Click “Create project” Within the chosen Software Project, type Jira Project = Sandwich Order, project key = SDWORDER
35	In “Issues”, click “Screens” and click “Configure” with the chosen screen. In “Configure Screen”, go to the bottom of page and type the name og your grid field “Sandwich Order”.
36	Click “Add” button to add the Sandwich grid field on all needed screens.
37	With the following steps, user can create issue with Sandwich Grid in “Sandwich Order” Jira Project