I trying to retrieve values for a customfield in the backend Jira db. I was able to query the customfield in the customfieldvalue table, but the values are returned as integers within the STRINGVALUE field. My thought is this another lookup / ID value, but I can't seem to find the table that holds the string values for this customfield. Any help is greatly appreciated!
Yep, this is one of many reasons I recommend doing reporting properly (with apps built for it, or asking Jira for data over the REST API) - the database is the worst possible way to report on Jira data.
But, if you insist on doing it the hard way
The table you are looking for depends on the type of field it is. My guess is that it is a list field of some sort (select list, multi-select list, radio button, checkboxes, those sort of labelled items). If it is a list field, then these contain "options" which you'll find in a table called Options (from memory - the name definitely has the word "option" in it)
The numeric string id you've found in customfieldvalue is the key to the options table - customfieldvalue.stringvalue = options.id
You'll be able to get the human display name out of the options row.
(Note - some other fields do this too, like user-pickers, but you'd read a different table for them. And cascading-select lists are a right botheration to read because you have to check the options table twice)
I have the ID and cfname for the customfield and I can find these in the customfieldvalue table, but I cannot find a way to look up the STRINGVALUE for this specific CUSTOMFIELD.
I tried querying the customfieldoption table for the ID of this customfield, but no records are returned. Makes me believe these "sprint" values are in a whole different table.
Oh, sprints, yeah, they're not options, they're sprint objects. I've never looked for them, but I do believe most of the sprint data is in the active objects tables that Jira Software creates. Tables are prefixed with AO_60DB71 (crib sheet for which app creates which tables is at https://confluence.atlassian.com/jirakb/list-of-jira-server-ao-table-names-and-vendors-973498988.html - I'm not going to pretend I could remember the table keys!)
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