Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in

Next challenges

Recent achievements

  • Global
  • Personal

Recognition

  • Give kudos
  • Received
  • Given

Leaderboard

  • Global

Trophy case

Kudos (beta program)

Kudos logo

You've been invited into the Kudos (beta program) private group. Chat with others in the program, or give feedback to Atlassian.

View group

It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

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

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 Leader Dec 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%'

@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 Leader Dec 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

Suggest an answer

Log in or Sign up to answer
TAGS

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you