Retrieve custom field Name and Description values defined in different languages from 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
You can fetch the translated name and description values of any custom field that you create. You can only select from the language packs that are installed in Jira. This article offers a detailed exploration of the process for retrieving information about the translated Custom field details from a database. It includes the necessary queries to effectively access and utilize this data for various auditing purpose.
Solution
This was written and tested using a PostgreSQL DB, so you may need to tweak it depending on the database you are using.
The following query has been designed to retrieve comprehensive information regarding specific custom field names. In addition to the names, this query will also present their corresponding description values, which are defined in multiple languages.
1
2
3
4
5
select CF.cfname, CF.description, PE.entity_name, PE.PROPERTY_KEY, PS.PROPERTYVALUE
from customfield CF, propertyentry PE, propertystring PS
where CF.id = PE.entity_id and PE.id = PS.id and PE.entity_name in ('CustomField')
and CF.cfname='<Custom Field name>'
order by 1, PE.PROPERTY_KEY; Please refer to the query result provided below to obtain all translated values for the sample "Test Count" custom field across all defined languages.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
select CF.cfname, CF.description, PE.entity_name, PE.PROPERTY_KEY, PS.PROPERTYVALUE
from customfield CF, propertyentry PE, propertystring PS
where CF.id = PE.entity_id and PE.id = PS.id and PE.entity_name in ('CustomField')
and CF.cfname='Test Count'
order by 1, PE.PROPERTY_KEY;
cfname | description | entity_name | property_key | propertyvalue
------------+------------------------------------------+-------------+-----------------------------------------------------------+---------------------------------------------------
Test Count | Number of Tests associated with an issue | CustomField | jira.translation.custom.fieldcustomfield_10612.desc.de_DE | Anzahl der Tests mit einem Vorgang verbunden sind
Test Count | Number of Tests associated with an issue | CustomField | jira.translation.custom.fieldcustomfield_10612.desc.es_ES | Número de Tests asociados con una tarea
Test Count | Number of Tests associated with an issue | CustomField | jira.translation.custom.fieldcustomfield_10612.desc.fr_FR | Number of Tests associated with an issue
Test Count | Number of Tests associated with an issue | CustomField | jira.translation.custom.fieldcustomfield_10612.desc.nl_NL | Anzahl der Tests mit einem Vorgang verbunden sind
Test Count | Number of Tests associated with an issue | CustomField | jira.translation.custom.fieldcustomfield_10612.name.de_DE | Test-Zähler
Test Count | Number of Tests associated with an issue | CustomField | jira.translation.custom.fieldcustomfield_10612.name.es_ES | Número de Tests
Test Count | Number of Tests associated with an issue | CustomField | jira.translation.custom.fieldcustomfield_10612.name.fr_FR | Nombre de Tests
Test Count | Number of Tests associated with an issue | CustomField | jira.translation.custom.fieldcustomfield_10612.name.nl_NL | Testanzahl
(8 rows)
To retrieve the name and description of a custom field for a specific language, you can execute the following query. For instance, the query provided below will obtain the details defined in the German language.
The German translation for the "Test Count" custom field , as specified in the Jira user interface, is:
Field Name: Test-Zähler
Description: Anzahl der Tests mit einem Vorgang verbunden sind
Similar details can be effortlessly retrieved from the database by executing the query below.
1
2
3
4
5
6
7
8
9
10
11
12
select CF.cfname, PE.entity_name, PE.PROPERTY_KEY, PS.PROPERTYVALUE
from customfield CF, propertyentry PE, propertystring PS
where CF.id = PE.entity_id and PE.id = PS.id and PE.entity_name in ('CustomField')
and CF.cfname='Test Count'
and PE.PROPERTY_KEY like '%de_DE%' order by 1, PE.PROPERTY_KEY;
cfname | entity_name | property_key | propertyvalue
------------+-------------+-----------------------------------------------------------+---------------------------------------------------
Test Count | CustomField | jira.translation.custom.fieldcustomfield_10612.desc.de_DE | Anzahl der Tests mit einem Vorgang verbunden sind
Test Count | CustomField | jira.translation.custom.fieldcustomfield_10612.name.de_DE | Test-Zähler
(2 rows) Was this helpful?