Build a Sandwich Order Management System
- Hung Techlead
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:
Introduction
The Sandwich order management is using an external data source 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 App
User created a related Jira project and issue
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 using the Sandwich grid on issue
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
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)
);
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); 
Configure Sandwich Order Grid
To go faster you can import this grid configuration =
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 information |
| 5 | Click Grids in Table Grid section on the left side of the screen |
| 6 | Click Add Grid |
| 7 | Fill information for “Name”
|
| 8 | In “Scopes”, select your Projects and Issue Types on which you want to display the grid |
| 9 | In Configuration - Column, click |
| 10 | In Add Column, choose Sequence in Column type User fill information for Identifier and Title Type “jmenu“ as Identifier and type “Sandwich menu” as Title Click Add to save the information |
| 11 | In Configuration - Column, click |
| 12 | In Add Column, choose Single Select List in Column type User fill information for Identifier and Title Type “jname“ as Identifier and type “Sandwich name” as Title In Option attributes, click + and type “price” In Dynamic options, choose “Sandwich” for Data Source and type the SQL query: In Mapping:
Click to save the dynamic options Click Add to save the information |
| 13 | In Configuration - Column, click |
| 14 | In Add Column, choose Formula in Column type User fill information for Identifier and Title Type “jprice“ as Identifier and type “Sandwich price” as Title In Formula expression, type: Click Add to save the information |
| 15 | In Configuration - Column, click |
| 16 | In Add Column, choose Integer in Column type User fill information for Identifier and Title Type “jamount“ as Identifier and type “Amount” as Title Click Add to save the information |
| 17 | In Configuration - Column, click |
| 18 | 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: Click Add to save the information |
| 19 | Click Save button to save the grid configuration |
Exceptional Flow
Users type the wrong information about the data source
Continued from step #3 in the Basic Flow:
Step | Description |
4 | An “Errors” message appears |
Use case stops. | |
Users clicks “Cancel” at “Data Source”
Continued from step #3 in the Basic Flow:
Step | Description |
4 | The system will automatically come back to the “Data Source” page |
Use case stops. | |
Users choose the wrong data source/type wrong SQL Query “Data Source”
Continued from step #12 in the Basic Flow:
Step | Description |
13 | An “Errors” message appears |
Use case stops. | |
Users type wrong the formula
Continued from step #14 in the Basic Flow:
Step | Description |
15 | An “Errors” message appears |
Use case stops. | |
Users click “Cancel” while saving the table
Continued from step #19 in the Basic Flow:
Step | Description |
20 | The system will automatically come back to the “Grids” page |
Use case stops. | |
Use the Sandwich Order grid in a Jira Project
Create a Sandwich order on Create issue screen in the projects that you selected in Scopes of the grid
Click on the Table Grid icon on the issue screen
And you will see your grid on the issue screen
Click
to edit the grid
Click
to add row
Select Sandwich name and fill Amount for it
Click
to save changes
This is the final result on the issue screen