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”: 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: Code Block 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: Code Block 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”: Code Block select * from sandwich_menu
34	Click “Get data”
35	Click “Configuration”
36	Click “Create project” Within the chosen Software Project, type Jira Project = Sandwich Order, project key = SDWORDER
37	Click “Issues” on Jira Administration bar→ click “Screen” → click the “+” button to add add grid on project screens
38	Click ”Save” to save the information
39	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:

Users choose the wrong data source/type wrong SQL Query “Data Source”

Continued from step #15 in the Basic Flow:

Users clicks “Cancel” at “Data Source”

Continued from step #16 in the Basic Flow:

Users type wrong SQL query 

Continued from step #21 in the Basic Flow:

Users click “Cancel” while saving the table 

Continued from step #24 in the Basic Flow:

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

2/ 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