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

[Eric Blair]

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!

[Higashi]

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;

[Eric Blair]

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

[Joe Pitt]

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

[Joe Pitt]

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.

[Eric Blair]

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

[Joe Pitt]

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.

[Eric Blair]

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?

[Joe Pitt]

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.

[Eric Blair]

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.