How do i retrieve the initial value of a custom field on an issue, from jira DB?

Hello,

I'm working in the Jira MySQL database. I need to retrieve the initial setting vallue of a custom field that is a dropdown. I can get the setting using thechange group tables, but only after someone has changed the field value. I need the value when the issue is created and someone sets it then.

For example:

I create an issue and select a value from the custom field drop-down. I save the issue. The issue show the setting of the field.

I run a query on the custom field tables to retrieve the setting and it is not set, it's value is NULL.

I have tried other tables like th propertystring and the node tables, but no go.

Anyone have any ideas on this?

Thank you!

6 answers

0 vote
Joe Pitt Community Champion Oct 14, 2013

If it always the same field you can use a post function to copy it to another field that isn't shown on the screen. Just make sure you put the post function after the create happens.

Thanks for your quick reply.

That might work. But why can't the initial value of the field just be available? it would seem the correct way for this to work. How does the interface know how to get the value and display it? It must be somewhere in the database.

0 vote
Joe Pitt Community Champion Oct 14, 2013

The value is replaced in the database. Many applications don't keep the previous value of fields. If the value is never updated the old value isn't copied to the history so it isn't there for you. Just the way it works. That is the trade off with a COTS product.

Thanks again. The problem is not the history, I can get that easily from the changegroup tables. I need the initial setting. If the initial setting is 'Open', for example, then I want to get that value: Open.

The table: customfieldoption, has the options and their ID's for a particular drop-down custom field, somehow that option ID should be traceable back to the Issue so we can say in this issue, this is the setting on this CF, at initial state. If the interface can get the value then it must be gettable in the DB.

Or, how does the jql in the interface know how to search by the CF value on issues, if jql uses the Db then it must be there somewhere.

No?

0 vote
Joe Pitt Community Champion Oct 14, 2013

Sounds like your logic will have to see if there is a value in the change history. If so get the first value in the history, if not go and get the value from the actual field.

yes, and there's the problem, how to get the value from the actual field.

0 vote
Joe Pitt Community Champion Oct 14, 2013

I've never written code to go against JIRA, but I would think the API would make it available. you can search on any custom field through the UI

Anyone else have any insights into querying for CF values in the database?

Default value for multiselect is:

select defaultvelue from customfield where cfname = 'yournameforcustomfield';

If it's NULL, than first value from field value list is taken:

select * from customfieldoption where CUSTOMFIELD=(select id from customfield where cfname = 'yournameforcustomfield') and SEQUENCE=0;

Suggest an answer

Log in or Join to answer
Community showcase
Sarah Schuster
Posted Jan 29, 2018 in Jira

What are common themes you've seen across successful & failed Jira Software implementations?

Hey everyone! My name is Sarah Schuster, and I'm a Customer Success Manager in Atlassian specializing in Jira Software Cloud. Over the next few weeks I will be posting discussion topics (8 total) to ...

3,304 views 14 20
Join discussion

Atlassian User Groups

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

Find a group

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

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you
Atlassian Team Tour

Join us on the Team Tour

We're bringing product updates and pro tips on teamwork to ten cities around the world.

Save your spot