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 MySQL, Postgres, SQL 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: Connecting an external data source
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  | |
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  |
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
 User click Add button to save the column information |
5 | With the second driving table column, user choose String as Column Type
 User click Add button to save the column information |
6 | With the third driving table column, user choose Number as Column Type
 User click Add button to save the column information |
7 | With the fourth driving table column, user choose String as Column Type
 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.  |
9 | After finish filling the driving table information, user click Save.  |
10 | The system will show the Edit Driving Table page, user click the Click  User can fill the data like this example via:  |
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: Driving Tables  |
11 | User navigate to Jira Administration - > Manage Apps → Grids Click Add Grid button to create new grid  |
12 | User fill Name and Description in Grid Fields In this example, writer used “Invoice” for Name and Description  |
13 | With the first driving table column, user choose Single Select List as Column Type
 In Dynamic options, choose for Data Source and type SQL query In this example, writer used
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  Click Click Add button to save the column information |
14 |
 |
15 | In Dynamic options, choose for Data Source and type SQL query In this example, writer used
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  Click Click Add button to save the column information |
16 | With the third column, user choose Single Select List as Column Type
 In Dynamic options, choose for Data Source and type SQL query In this example, writer used
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  Click Click Add button to save the column information |
17 | With the fourth column, user choose Single Select List as Column Type
In Dynamic options, choose for Data Source and type SQL query In this example, writer used
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  Click Click Add button to save the column information |
18 | With the fifth column, user choose Number as Column Type
 Click Add button to save the column information |
19 | With the sixth column, user choose Formula as Column Type
 In Formula expression, type: return $(jprice) * $(jquantity);  Click Add button to save the column information |
20 | User click  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
In the main bar, click Projects → click Create project
Choose a project type → Click Next button to process the next step
The system display a pop-up message describes the issue types and workflow of the software development method.
Click Select button to process next step
Type Name and Key of the project
In this example, writer typed “Invoice” for Name and “IN3” for Key
Click Submit button to create new project
Your project can be found in the list of projects.
Grid configuration file
2/ To go faster you can import this grid configuration =
Add grid on project screens
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
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
User choose a project in Projects section
Writer choose Invoice project to process next step
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
Click Create button to save the issue.
Final result on issue view
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
Tip: Export Invoice grid using Better PDF Exporter
You can export the Invoice grid above to a PDF file by using the app Better PDF Exporter.
Install Better PDF Exporter
This app can be used to build custom PDF template to export Jira issues.
It is integrated with Table Grid Next Generation to be able to export grid data in issue in the PDF export.
You can install it in: https://marketplace.atlassian.com/apps/5167/better-pdf-exporter-for-jira-pdf-view?hosting=datacenter&tab=overview
Read instruction to install here: https://www.midori-global.com/products/better-pdf-exporter-for-jira/data-center/installation
Export a Jira issue with Table grid field
Open Jira Issue that you want to export
Click on “Export” and select “PDF”
Open the PDF file that you have exported
Export Issue List with Table grid fields
Open Issue List
Click on “Columns” and select the columns you want to view in the PDF file. Remember to select your Table grid field.
Click on “Export” and select “Issue List (PDF)”
Open the PDF file that you have exported
