Managing Internal Purchases by Using Driving Table and Data Mirror
- Hung Techlead
In this use case, you will learn how to create internal shopping lists in a grid. Using the Driving Table feature to create a product list and the formula column to calculate the total of the purchase.
You will also learn how to use several features in TGNG app such as:
Connecting an external data source
How to add option attributes to a list
Introduction
You can create a stand-alone list of products using the Driving Table. Then use it in a grid as a dynamic list of available products.
The dynamic list is based on a Driving Table, maintained in the Table Grid Next Generation admin. You can save the table in the database using the .
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.
Table Grid with a dynamic list of products.
Post-condition
User can use the grid to update related information for the list of products
User can create issues using the Shopping List grid
User can download/upload the Configuration for personal usage
User can add more rows to fill the shopping list such as: total, quantity,..
Conditions
Required fields are marked with a red asterisk *
The grid must contain at least one column, or else it will show the pop-up message
“Error table-config-column-config-required”.The grid can be displayed in more than one project
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;
Flow chart diagram
Configure Shopping List Grid
Basic Flow
Action | |
|---|---|
| 1 | Click Add Data Source in Data Source  |
| 2 | Fill all required information in Data Source  |
| 3 | Click Test Connection to examine the connection between database and Jira |
| 4 | Click Save to save data source |
| 5 | In Table Grid section, choose Grids |
| 6 | In Driving Table, click Add Driving Table button to add new driving table |
| 7 | Fill information for “Name” In this example, user used
|
| 8 | In Configuration - Column, click Add new column to create new column |
| 9 | In Add Column, choose String in Column type User fill information for Identifier and Title Type “code“ as Identifier and type “Code” as Title Click Add to save the information |
| 10 | In Add Column, choose String in Column type User fill information for Identifier and Title Type “name“ as Identifier and type “Name” as Title Click Add to save the information |
| 11 | In Add Column, choose Number in Column type User fill information for Identifier and Title Type “price“ as Identifier and type “Price” as Title Click Add to save the information |
| 12 | In Configuration - Data Mirror, Select the Data Source that you created in step #4 Fill the Database table name The Database table name must be unique in your database Click Create table to create table in your database |
| 13 | Click Save to save the Driving Table Config |
| 14 | Click Click |
| 15 | In Grid Fields, click Add Grid button to add new grid |
| 16 | Fill information for “Name” and “Description” In this example, user used
|
| 17 | In Configuration - Column, click Add new column to create new column |
| 18 | In Add Column, choose Single Select List in Column type User fill information for Identifier and Title Type “jproduct“ as Identifier and type “Product” as Title To display the price automatically, use the dynamic option mapping. You need to map the option label to display the product name and the option value to display the price. In Option attributes, click Type “price” as Key In Dynamic options, choose your database for Data Source and type the SQL query: SELECT name, price FROM productsClick Get data to get data from your database In Mapping:
Click to save the dynamic options Click Add to save the information |
| 19 | In Configuration - Column, click Add new column to create new column |
| 20 | In Add Column, choose Formula in Column type User fill information for Identifier and Title Type “jprice“ as Identifier and type “Price” as Title In Formula expression, type: var result;
if($(jproduct).hasOwnProperty('optionObject')){
result = $(jproduct).optionObject.price;
}else{
result = $(jproduct).price;
}
return result;Click Add to save the information |
| 21 | In Configuration - Column, click Add new column to create new column |
| 22 | In Add Column, choose Integer in Column type User fill information for Identifier and Title Type “jquantity“ as Identifier and type “Quantity” as Title Click Add to save the information |
| 23 | In Configuration - Column, click Add new column to create new column |
| 24 | In Add Column, choose Formula in Column type User fill information for Identifier and Title Type “jtotal“ as Identifier and type “Total” as Title In Formula expression, type: Click Add to save the information |
| 25 | Click Save to save the grid configuration |
Exceptional Flow
Users clicks “Cancel” at “Data Source”
Continued from step #2 in the Basic Flow:
Step | Description |
3 | The system will automatically come back to the “Data Source” page |
Use case stops. | |
Users type the wrong information about the data source
Continued from step #4 in the Basic Flow:
Step | Description |
5 | An “Errors” message appears |
Use case stops. | |
Users type wrong SQL query
Continued from step #18 in the Basic Flow:
Step | Description |
19 | An “Errors” message appears |
Use case stops. | |
Users click “Cancel” when saving the config
Continued from step #24 in the Basic Flow:
Step | Description |
25 | The system shows a pop-up message: “Exit without saving?” |
26 | User click Yes button |
Use case stops. | |
Use Shopping List Grid in a Jira project
Create a Jira project
Click Project on the Navigation bar
Click Create project
Fill required information to create project
Add grid on project screen
Click Issues tab, in Screens section choose Screens
Click Configure on your project
Select Shopping List as a custom field
Click Add to add field on project screen
Turnthe field to show it on project screen even when empty
Create an issue with Shopping List
Open your project
Click
on Navigation bar to create an issue
Click
to add row and fill the information
You can update the list of products and change prices in the stand-alone table used to store product-related information. This does not require changing your grid configuration.