Build a Sandwich Order Management system
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 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”
|
| 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”
|
| 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”: In “Mapping”:
|
| 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: |
| 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: |
| 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
|
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 choose the wrong data source/type wrong SQL Query “Data Source”
Continued from step #15 in the Basic Flow:
Step | Description |
16 | An “Errors” message appears |
Use case stops. | |
Users clicks “Cancel” at “Data Source”
Continued from step #16 in the Basic Flow:
Step | Description |
17 | The system will automatically come back to the “Data Source” page |
Use case stops. | |
Users type wrong SQL query
Continued from step #21 in the Basic Flow:
Step | Description |
22 | An “Errors” message appears |
Use case stops. | |
Users click “Cancel” while saving the table
Continued from step #24 in the Basic Flow:
Step | Description |
25 | The system will automatically come back to the “Grids” page |
Use case stops. | |
4. Use the Sandwich Order grid in a Jira Project
Create a Jira project
1/ Click “Create project”
Within the chosen Software Project, type Jira Project = Sandwich Order, project key = SDWORDER
Grid configuration file
To go faster you can import this grid configuration =
Add grid on project screens
2/ In “Issues”, click “Screens” and click “Configure” with the chosen screen.
In “Configure Screen”, clicking “+” button in “Configure Screen” to add grid.
Please visit here for more detailed information: How to define a screen for a custom field
Create a sandwich order (issue)
3/ Then create issue with the Sandwich