Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 28 Current »

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

(info) 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”

image-20231222-091742.png

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

image-20231222-092519.png

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

image-20231222-092619.png

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

image-20231222-092519.png

Create a sandwich order (issue)

3/ Then create issue with the Sandwich

image-20231222-092630.png

  • No labels