Creating Invoices and Managing Quotes

 

The Creating Invoices and Managing Quotes usecase helps users create cascading lists in a grid using column value placeholders.

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

Introduction

The Creating Invoices and Managing Quotes use case helps users create cascading lists in a grid using column value placeholders. Users can use external database systems such as  MySQLPostgresSQL Server, etc., or Driving Table to create cascading lists.

This usecase 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 usecase for their organization departments, such as Sales, Marketing, IT, and Human Resources, to control the performance of each team and also to analyse further strategies for the team.

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.

  • An existing data source configured using TGNG app: https://tablegrid.atlassian.net/wiki/spaces/TGNG/pages/1934148

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

  • 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

Basic Flow

Steps

Action

 

In Grids, click Driving Table section

When the page Driving Table shows, click Add Driving Table button to add new driving table

image-20231229-100229.png

 

2

Type for Name and Description

For this example, writer write “Laptop_sale_management_1” for both of it

User need to delete all of the default columns in Table Grid by click the button

image-20231229-100453.png

 

3

In Columns, user will create 4 new driving table columns which will be described in the next step

4

With the first driving table column, writer choose String as Column Type

  • pcategory” for Identifier

  • Category” for Title

image-20240112-091228.png

User click Add button to save the column information

5

With the second driving table column, user choose String as Column Type

  • pmanufacturer” for Identifier

  • Manufacturer” for Title

image-20240112-091459.png

User click Add button to save the column information

6

With the third driving table column, user choose Number as Column Type

  • pprice” for Identifier

  • Price” for Title

image-20231229-101512.png

User click Add button to save the column information

7

With the fourth driving table column, user choose String as Column Type

  • pmodel” for Identifier

  • Model” for Title

image-20240112-091540.png

User click Add button to save the column information

8

Click Data Mirror, then choose Data Source and type for Database table name

In this example, the selected Data Source is “Product” and the Database table name is “invoice_11”. After user fill information, the Mapping section will appear to validate the column information.

User click Create table and Validate button, there are pop-ups show when the table is created successfully.

image-20240115-025130.png

 

9

After finish filling the driving table information, user click Save.

image-20231229-101810.png

10

The system will show the Edit Driving Table page, user click the button to add rows and fill data.

Click button to save the driving table configuration

image-20231229-102354.png

User can fill the data like this example via:

image-20231229-101917.png

11

Navigate to MySQL Workbench, user can see the table with updated data.

User can make changes to the table by (step 10). For more information visit here: https://tablegrid.atlassian.net/wiki/spaces/TGNG/pages/1933553

image-20240115-030102.png

11

User navigate to Jira Administration - > Manage AppsGrids

Click Add Grid button to create new grid

image-20240102-094414.png

12

User fill Name and Description in Grid Fields

In this example, writer used “Invoice” for Name and Description

image-20240102-094648.png

13

With the first driving table column, user choose Single Select List as Column Type

  • jtype” for Identifier

  • Product Type” for Title

image-20240102-094905.png

In Dynamic options, choose for Data Source and type SQL query

In this example, writer used

  • Products” for Data Source

  • SELECT distinct * FROM tablegrid.invoice_11;

for SQL query

Click Get data button to connect the database. When the query is corrected, there will be pop-up displays the result.

User should choose a column for Mapping

In Mapping, writer choose pcategory for label

image-20240115-030204.png

Click to save the dynamic options

Click Add button to save the column information

14

  • With the second column, user choose Single Select List as Column Type

  • jman” for Identifier

  • Manufacturer” for Title

image-20240102-095830.png

 

15

In Dynamic options, choose for Data Source and type SQL query

In this example, writer used

  • Products” for Data Source

SELECT pmanufacturer FROM tablegrid.invoice_11 where pcategory = '{column:jtype}';

for SQL query

Click Get data button to connect the database. When the query is corrected, there will be pop-up displays the result.

User should choose a column for Mapping

In Mapping, writer choose pmanufacturer for label

image-20240115-030408.png

Click to save the dynamic options

Click Add button to save the column information

16

With the third column, user choose Single Select List as Column Type

  • jmodel” for Identifier

  • Model” for Title

image-20240102-100511.png

In Dynamic options, choose for Data Source and type SQL query

In this example, writer used

  • Products” for Data Source

  • select pmodel from tablegrid.invoice_11 where pmanufacturer = '{column:jman}' and pcategory='{column:jtype}'

for SQL query

Click Get data button to connect the database. When the query is corrected, there will be pop-up displays the result.

User should choose a column for Mapping

In Mapping, writer choose pmodel for label

image-20240102-100540.png

Click to save the dynamic options

Click Add button to save the column information

17

With the fourth column, user choose Single Select List as Column Type

  • jprice” for Identifier

  • Price” for Title

image-20240102-100511.png

In Dynamic options, choose for Data Source and type SQL query

In this example, writer used

  • Products” for Data Source

select pprice from tablegrid.invoice_11 where pmodel= '{column:jmodel}'

for SQL query

Click Get data button to connect the database. When the query is corrected, there will be pop-up displays the result.

User should choose a column for Mapping

In Mapping, writer choose pprice for label

image-20240115-030550.png

Click to save the dynamic options

Click Add button to save the column information

18

With the fifth column, user choose Number as Column Type

  • jquantity” for Identifier

  • Quantity” for Title

image-20240103-094820.png

Click Add button to save the column information

19

With the sixth column, user choose Formula as Column Type

  • jtotal” for Identifier

  • Total” for Title

image-20240103-094902.png

In Formula expression, type:

return $(jprice) * $(jquantity);
image-20240103-094948.png

Click Add button to save the column information

20

User click button to add row and if the query is correct, the result will be shown below

image-20240103-095626.png

Click Save button to save the column configuration

User can upload this configuration for practice:

Exceptional Flow

Users clicks Cancel at Driving Table

Continued from step #2 in the Basic Flow:

Step

Description

3

The system will automatically come back to the Driving Table page

Use case stops. 

 

Users delete all columns

Continued from step #9 in the Basic Flow:

Step

Description

10

The system shows a pop-up message: “Grid name is required. Please set the name of your Grid., table-config-column-config-required

Use case stops. 

 

Users choose invalid Datasource

Continued from step #8 in the Basic Flow:

Step

Description

9

The system shows a pop-up message:

Table wasn't created

Maybe table with this name already exists in the database

Use case stops. 

 

Users choose invalid/existing Database table name

Continued from step #8 in the Basic Flow:

Step

Description

9

The system shows a pop-up message: “Bad Table name /Only contains alphanumeric characters and underscores

10

User click Cancel button

Use case stops. 

Users type wrong SQL query 

Continued from step #13 in the Basic Flow:

Step

Description

14

An “Errors” message appears 

Use case stops. 

 

Users click “Cancel” while saving the config

Continued from step #17 in the Basic Flow:

Step

Description

18

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

Use case stops. 

 

Use the Invoice grid in a Jira Project

Create a new project

  1. In the main bar, click Projects → click Create project

image-20240103-053015.png
  1. Choose a project type → Click Next button to process the next step

image-20240103-053118.png
  1. The system display a pop-up message describes the issue types and workflow of the software development method.

image-20240103-053215.png

Click Select button to process next step

  1. Type Name and Key of the project

In this example, writer typed “Invoice” for Name and “IN3” for Key

image-20240103-073839.png

Click Submit button to create new project

Your project can be found in the list of projects.

image-20240103-074300.png

 

Grid configuration file

2/ To go faster you can import this grid configuration =

Add grid on project screens

  1. In Issues, click Screens and click Configure with the chosen screen.

In the example, writer configured the Invoice Kanban Issue Screen

Please visit here for more detailed information: How to define a screen for a custom field

image-20240103-052532.png
  1. In Field Tab, choose grid to apply to your project by clicking button

Click Add button to save the information

In the example, writer added Invoice 1 grid in the Field Tab

image-20240103-052729.png
  1. User choose a project in Projects section

Writer choose Invoice project to process next step

image-20240103-092529.png
  1. In the selected project, user click Create button to create new Issue

Then select Issue Type, and select a name for Summary

In this example, writer use “Invoice” for Summary

image-20240103-100142.png

Click Create button to save the issue.

Final result on issue view

  1. The final result will look like this

User can continue to edit the config in the issue by clicking the button and add more rows in the issue by clicking the button

image-20240115-024919.png