Forums

Articles
Create
cancel
Showing results for 
Search instead for 
Did you mean: 

Where can I find the values for a custom field in JIRA DB

Vishnu C December 16, 2019

I created a custom field with JIRA dark feature(Multi Select with auto suggest option). I'm trying to find the list of values added to this custom field from JIRA DB. I have the below query but the results are showing the text value as null and the string value as 11100 and 11101. 

select * from JIRA.customfieldvalue where customfield = 11600;

ID         ISSUE CUSTOMFIELD        PARENTKEY           STRINGVALUE 

189534 27704 11600                    1575867073700     11100
189535 27704 11600                    1575867073700     11101
189536 27705 11600                     1575867252062    11101

 

1 answer

0 votes
Tom Lister
Community Champion
December 23, 2019

Hi @Vishnu C 

You'll need to link the string value to the id in customfieldoption to get the user friendly version.

e.g.

SELECT cf.id, issue , cfkey, customfieldtypekey, cfname, cfv.stringvalue, cfo.customvalue
FROM public.customfield cf, public.customfieldvalue cfv , public.customfieldoption cfo
where cf.id = cfv.customfield
and cast(cfv.stringvalue as integer) = cfo.id
and customfieldtypekey like '%select%'
Vishnu C December 29, 2019

@Tom Lister 

Thank you what is the purpose of Issue ID in customfieldvalue table. My custom field id is 11600 when I query this id in CFV table it has 3 entries for string value and 1 of them is repeated with same Issue ID and Parentkey. Can you please let me know what is purpose of Issue and ParentKey in this table. Reason why Im asking is we are planning to load values for this Cutomfield from outside service so want to make sure what fields and tables I need to take care when I make an entryimage.png

Tom Lister
Community Champion
December 30, 2019

Hi @Vishnu C 

The customfieldvalue table is the actual option(s) selected and associated with an issue, so Issue ID is the link to jiraissue. Not sure about parent key wothput a bit of research.

The available values for a select  list are the customvalueoption entries.

As for updating values, I personally avoid SQL updates as experience has shown there can be unexpected problems. There are a lot of potential collateral updates by the API's and I no longer feel like the risk is worth the possible time saving. 

I recommend using Groovy scripting. e.g.https://community.atlassian.com/t5/Marketplace-Apps-Integrations/add-options-to-select-list-using-groovy/qaq-p/100966

There are some free Groovy plugins out there.

Tom

Like Sri Kanth likes this

Suggest an answer

Log in or Sign up to answer