How to systematically exchange fields in screens using the database
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Summary
When administering large Jira instances, the same custom field may be used on several screens. Should it be necessary to replace this custom field with another, it may be very time consuming to remove the previous field from all screens and add the new one onto them.
To address this need, this article explains how a field can be systematically exchanged for another in Jira screens using the database.
Where a workaround involves running SQL scripts directly on your database, we strongly recommend you do this first on a non-production, staging or test environment before running the scripts on your production database. We also strongly recommend you take a backup of your data before making any modifications via SQL scripts.
Solution
The field-screen associations are stored in the 'fieldscreenlayoutitem' database table in the 'fieldidentifier' column. In this field, the data corresponding to the field identifier is stored in the format of 'customfield_12345', where 12345 is the actual ID of the custom field. The field ID can be found in the 'id' column of the 'customfield' table.
Reference:Jira Database Schema
With that information in mind, we can draft the following procedure to systematically replace the fields in all screens where it is used in Jira.
Important notes
This procedure will replace the field in all screens where it is used; It will not migrate the data set in the old field into the new.
Before proceeding with the steps below, we strongly recommend testing it in a non-production environment and creating a native database backup in case the changes need to be rolled back.
Detailed steps
Stop the Jira service on all nodes (if multiple).
Take the native database backup at this time.
Identify and take note of the custom field IDs for the fields you want to swap using the SQL query below:
ℹ️ Replace <CustomFieldName1> and <CustomFieldName2> with the actual field names.
1 2 3SELECT id, cfname FROM customfield WHERE cfname IN ('CustomFieldName1', 'CustomFieldName2');With the IDs obtained in the previous query, use the following update statement to swap the fields used in the Jira screens.
ℹ️ Replace only the <ID> with the ID number obtained. Example: 'customfield_12345'.
1 2 3UPDATE fieldscreenlayoutitem SET fieldidentifier = 'customfield_ID' --ID from the field you want to add to the screens. WHERE fieldidentifier = 'customfield_ID'; --ID from the field you want to remove to the screens.Commit the changes if necessary.
Start Jira again, one node at a time.
Verify if the field has been replaced correctly in the expected screens.
Perform a full Jira re-index.
Was this helpful?