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

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”: With “Label”, choose “Sandwich_name” With “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 “Save” to save the grid information
32	Click “Data Receiver” in “Configuration” section
33	Type “Data Source” choose “sandwich_menu” and type “SQL query”: select * from sandwich_menu
34	Click “Get data”
35	Click “Configuration”
36	Click ”Save” to save the information
37	With the following steps, user can create issue with Sandwich Grid.