Versions Compared

Old Version 4

changes.mady.by.user Dung Nguyen Tuan

Saved on

compared with

New Version 5

changes.mady.by.user Hung Techlead

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: draw.io diagram "Untitled Diagram-1702978587960.drawio" edited
Info

The Creating Invoices and Managing Quotes use case helps users create an invoice using a cascading lists in a grid.

You will also learn how to use several features in TGNG app such as:

Table of Contents

Introduction

The Creating Invoices and Managing Quotes use case helps users create cascading lists in a grid using 

Column values placeholders. Users can use external database systems such as  MySQL, Postgres, SQL Server, etc., to create cascading lists.

This use case is based an example of a laptop reseller who used Jira to manage the sale, and each Jira ticket will be a sale management/invoice ticket. Users can apply the methodology of this use case for their organization departments, such as Sales, Marketing, IT, and Human Resources, to control the performance of each team and also to analyze further strategies for the team.

image-20241113-084814.png

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.

  • An existing data source configured using TGNG app: Data sources

Post-condition

  • User can use the grid to update related information for the list of items

  • User can create issues using the Invoice 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 Cloud 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:

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

  • The database table name must be distinguished. If not, the system shows a pop-up message:

    “Table wasn't created

    Maybe table with this name already exists in the database”.

  • The database table name can only contains alphanumeric characters and underscores.

Use case Flow Diagram

Drawio
mVer2
zoom1
simple0
inComment0
custContentId247202265
pageId247300097
lbox1
diagramDisplayNameUntitled Diagram-1702978587960.drawio
contentVer2
revision3
baseUrlhttps://tablegrid.atlassian.net/wiki
diagramNameUntitled Diagram-1702978587960.drawio
pCenter1
width538.5500000000002
links
tbstyle
height1271

Configure Grid “Invoice”

Info

You can upload this grid configuration for practice:

View file
nameInvoice.json

Basic Flow

Actions

1

Click Add Data Source in Data Source

image-20241113-040303.png
2

Fill all required information in Data Source

image-20241113-040602.png
3

Click Test Connection to examine the connection between Database and Jira

4

Click Save to save information of Data Source 

5

Click Add Grid in Grids section

image-20241113-041018.png
6

Select Grid fields (to populate data manually)

Click Confirm

image-20241007-024125.png
7

Fill information for Name = “Invoice”

8

In Scopes, select your Projects and Issue Types on which you want to display the grid

image-20241113-041401.png
Info

Select “Is available in all new projects” to ensure that all new created projects will be added automatically to Scopes of the current grid.

9

In Configuration - Column, click image-20240516-020049.png to create new column

10

In Add Column, choose Single Select List in Column type

User fill information for Identifier and Title

Type “jtype“ as Identifier and type “Product Type” as Title

Select “Cell can be edited” to select Product Type on Issue Screen

image-20241113-042417.png

In Dynamic options, choose Data Source and type SQL query

In this example, writer used:

  • “Laptop Sale Management” for Data Source

  • SQL query:

    Code Block
    languagejs
    SELECT distinct * FROM laptop_sale_management_1
Info

Data in table laptop_sale_management_1

image-20241113-073850.png

Click Get data button to connect the database. When the query is corrected, the result will be displayed.

In Mapping, choose pcategory for label (because this column represents for Product Type in Database)

image-20241113-042736.png

Click (blue star) to save the dynamic options

Click Add button to save the column information

11

In Configuration - Column, click image-20240516-020049.png to create new column

12

In Add Column, choose Single Select List in Column type

User fill information for Identifier and Title

Type “jman“ as Identifier and type “Manufacturer” as Title

Select “Cell can be edited” to select Manufacturer on Issue Screen

image-20241113-050554.png

In Dynamic options, choose Data Source and type SQL query

In this example, writer used:

  • “Laptop Sale Management” for Data Source

  • SQL query:

    Code Block
    languagejs
    SELECT pmanufacturer FROM laptop_sale_management_1 where pcategory = '{column:jtype}';

In Mapping, type “pmanufacturer” for label (because this column represents for Manufacturer in Database)

image-20241113-050829.png

Click (blue star) to save the dynamic options

Click Add button to save the column information

13

In Configuration - Column, click image-20240516-020049.png to create new column

14

In Add Column, choose Single Select List in Column type

User fill information for Identifier and Title

Type “jmodel“ as Identifier and type “Model” as Title

Select “Cell can be edited” to select Model on Issue Screen

image-20241113-052121.png

In Dynamic options, choose Data Source and type SQL query

In this example, writer used:

  • “Laptop Sale Management” for Data Source

  • SQL query:

    Code Block
    languagejs
    SELECT pmodel FROM laptop_sale_management_1 where pmanufacturer = '{column:jman}' and pcategory='{column:jtype}'

In Mapping, type “pmodel” for label (because this column represents for Model in Database)

image-20241113-071405.png

Click (blue star) to save the dynamic options

Click Add button to save the column information

15

In Configuration - Column, click image-20240516-020049.png to create new column

16

In Add Column, choose Single Select List in Column type

User fill information for Identifier and Title

Type “jprice“ as Identifier and type “Price” as Title

Select “Cell can be edited” to select Price on Issue Screen

image-20241113-071622.png

In Dynamic options, choose Data Source and type SQL query

In this example, writer used:

  • “Laptop Sale Management” for Data Source

  • SQL query:

    Code Block
    languagejs
    SELECT pprice FROM laptop_sale_management_1 where pcategory = '{column:jtype}' and pmanufacturer = '{column:jman}' and pmodel = '{column:jmodel}'

In Mapping, type “pprice” for label (because this column represents for Price in Database)

image-20241113-072102.png

Click (blue star) to save the dynamic options

Click Add button to save the column information

17

In Configuration - Column, click image-20240516-020049.png to create new column

18

In Add Column, choose Number in Column type

User fill information for Identifier and Title

Type “jquantity“ as Identifier and type “Quantity” as Title

Select “Cell can be edited” to type Quantity on Issue Screen

image-20240103-094820.png

Click Add button to save the column information

19

In Configuration - Column, click image-20240516-020049.png to create new column

20

In Add Column, choose Formula in Column type

User fill information for Identifier and Title

Type “jtotal“ as Identifier and type “Total” as Title

image-20241113-072403.png

In Formula expression, type:

Code Block
languagejs
return Number($(jprice).label) * Number($(jquantity));

In Aggregation operation, select “sum” to display sum of the Total column in the grid

image-20241113-072506.png

Click Add button to save the column information

21image-20241113-082125.png

Click Save button to save the gird configuration

Exceptional Flow

Users can’t connect to database

Continued from step #3 in the Basic Flow:

Step

Description

4a

An “Errors” message appears 

Use case stops.

Users type wrong SQL query 

Continued from step #11 in the Basic Flow:

Step

Description

12a

An “Errors” message appears 

Use case stops. 

Users click “Cancel” while saving the config

Continued from step #21 in the Basic Flow:

Step

Description

22a

The system will automatically come back to the “Grids” page

Use case stops. 

Use the Invoice grid in a Jira Project

  1. Open the issue in Projects and Issue Type that you added to Scopes of the grid.

image-20241113-082812.png

  1. Click on Apps button,and then selectGrid → The grid will appear on Issue Screen.

image-20241113-083038.png

  1. Click on image-20241113-083626.pngto edit the grid.

image-20241113-083336.png

  1. Click on image-20241113-083553.png to add row to the grid.

image-20241113-083742.png

  1. Double-click on each cell to select value.

image-20241113-084029.pngimage-20241113-084139.png

  1. Enter Quantity to calculate Total

image-20241113-084337.png

  1. Click on image-20241113-084400.png to save the grid data.

  2. The final result will look like this

image-20241113-084814.png

User can continue to edit the grid in the issue by clicking the (blue star) button and add more rows in the grid by clicking the (blue star) button

Conclusion

Creating Invoices and Managing Quotes use case simplifies invoice creation through cascading lists in a grid, reducing errors and enhancing efficiency. This feature streamlines invoicing and quote management, enabling faster processing and improved customer service.

We hope you can apply this approach to your own processes, enhancing efficiency and control in real-world situations.