(Data Mirror) Bulk Updating Customer Region via Data Mirror ODBC
This document describes a practical use case in which an organization uses Jira to manage customer - related issues and leverages Data Mirror (ODBC) to perform a bulk update of the customer_region field in thousands of issues.
The use case demonstrates how Data Mirror enables fast, centralized, and accurate updates of synchronized data in Jira grids - saving time and reducing human error compared to manual edits.
You will also learn how to use several features in TGNG app such as:
Introduction
Organizations often maintain master data such as customer information directly inside Jira issues using Table Grid Next Generation. With Data Mirror, these fields can be synchronized with an external database through ODBC, allowing SQL operations to reflect changes in real-time back to Jira grids.
In this example, the update is triggered by a government administrative change in France, merging three regions (Alsace, Lorraine, Champagne-Ardenne) into a new region called Grand Est. Regional administrative changes in France require updating customer_region values across thousands of Jira issues. Rather than editing each issue manually, the organization can execute a single SQL UPDATE statement to apply the changes consistently and instantly.
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
A successful ODBC connection to the customer information database.
Post-condition
User can download/upload the Configuration for personal usage.
Updates made in the grid (within Jira issues) will automatically reflect back in the external database.
Any changes in the external database will automatically appear in the Jira grid.
All customer records with a customer_region value of Alsace, Lorraine, or Champagne-Ardenne are updated to Grand Est
Conditions
Required fields are marked with a red asterisk *
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
Connect to “Products” data source
Basic Flow
Actions | |
|---|---|
| 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 information of Data Source |
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. | |
Configure “Customer Information” grid
You can upload this grid configuration for practice:
Basic Flow
Actions | |
|---|---|
| 1 | Click Add Grid in Grids section  |
| 2 | Select Grid fields (to populate data manually) Click Confirm  |
| 3 | Fill information for Name = “Customer Information” |
| 4 | In Scopes, select your Projects and Issue Types on which you want to display the grid  Select “Is available in all new projects” to ensure that all new created projects will be added automatically to Scopes of the current grid. |
| 5 | In Configuration - Column, click |
| 6 | In Add Column, choose String in Column type User fill information for Identifier and Title Type “customer_id“ as Identifier and type “Customer ID” as Title  Click Add button to save the column information |
| 7 | In Configuration - Column, click |
| 8 | In Add Column, choose String in Column type User fill information for Identifier and Title Type “customer_name“ as Identifier and type “Name” as Title  Click Add button to save the column information |
| 9 | In Configuration - Column, click |
| 10 | In Add Column, choose String in Column type User fill information for Identifier and Title Type “customer_email“ as Identifier and type “Email” as Title  Click Add button to save the column information |
| 11 | In Configuration - Column, click |
| 12 | In Add Column, choose String in Column type User fill information for Identifier and Title Type “customer_region“ as Identifier and type “Region” as Title  Click Add button to save the column information |
| 13 | In Configuration - Column, click |
| 14 | In Add Column, choose String in Column type User fill information for Identifier and Title Type “status“ as Identifier and type “Status” as Title  Click Add button to save the column information |
| 15 | Click Data Mirror tab  |
| 16 | Select “Products” in Data Source field Check Treat Data Source value as primary during sync option to ensure the data in the grid and the data source are synchronized bidirectionally Type “customer_information” as Database table name In Mapping section: map gird columns to database table columns  Click Create table to create a corresponding table in the connected database |
| 17 | Click Save to save the grid configuration  |
Exceptional Flow
Users click “Cancel” while saving the config
Continued from step #16 in the Basic Flow:
Step | Description |
17a | The system will automatically come back to the “Grids” page |
Use case stops. | |
Bulk Updating Customer Region via Data Mirror ODBC
Use SQL To Update Customer Region In Database
Open the connected database via Data Mirror
Database: tablegrid (the database of Products data source)
Table: customer_information (the table created by Data Mirror feature)
Column:
row_id: created automatically when user add row to grid on Issue screen
environment: Issue ID of Jira issue that contains Customer Information grid
customer_email: mapped to Email column
customer_id: mapped to Customer ID column
customer_name: mapped to Nam column
status: mapped to Status column
customer_region: mapped to Region column
Use SQL to update all customer_region values as “Alsace” or “Lorraine” or “Champagne-Ardenne” to “Grand Est”
Type SQL and run the script in database:
USE `tablegrid`;
UPDATE customer_information
SET customer_region = 'Grand Est'
WHERE customer_region IN ('Alsace', 'Lorraine', 'Champagne-Ardenne');
View result in database
All customer_region values as “Alsace” or “Lorraine” or “Champagne-Ardenne” have been updated to “Grand Est”
View The Changes On Issue Screen
Before bulk updating:
After bulk updating:
Conclusion
This use case illustrates how organizations can maintain agility and compliance by leveraging Bidirectional Synchronization with Table Grid Next Generation. Bulk updates, such as reflecting administrative changes like regional mergers, can be implemented in seconds using SQL, avoiding manual updates of thousands of issues.
Feel free to explore how Data Mirror can simplify other data management scenarios in your Jira environment!