I'm using SQL server to find custom fields for our tabular reporting and I've struggling to find a list of these in the CUSTOMFIELDOPTION table by using the following queries:
select customfield from customfieldvalue cfv
where cfv.customfield = (select id from customfield where cfname = 'Epic Link') ;
select [CUSTOMFIELD] from CUSTOMFIELDOPTION
where customfield = (select id from customfield where cfname = 'Epic Link') ;
The list of custom fields I'd like to get:
'Customer Request Type'
'Epic Net Present Value'
'EPIC Non incremental benefits'
'Estimated Cost of Epic'
'Risk Reduction/ Opportunity Enablement'
'UID Net Present Value'
'WSJF Benefit hypothesis'
Kindly assist on how I can find these custom fields in the CUSTOMFIELDOPTION table.
Ah, the joys of trying to use a Jira database for reporting (hint - don't, it's horrible)
For a list of custom fields, look at Customfield. Forget customfieldoptions, that's for holding the things you see in select/checkmark fields as options for selection.
Thanks, where else can I get the contents you see in the select / checkmark fields? Because our reporting requires them too. Here's an example of our queries:
[cfo].customvalue As [APIApplication],
Convert(Int,[cfv].[Issue]) As [IssueID],
[cf].cfname As [Description]
From [customfield] As [cf]
Inner Join [customfieldvalue] As [cfv]
[cf].[ID] = [cfv].CustomField
inner join customfieldoption As [cfo]
[cfo].[CUSTOMFIELD] = cf.[ID]
Convert(varchar,[cfo].ID) = cfv.STRINGVALUE
Where [cf].cfname = rtrim('API Application')
You didn't ask for that, you asked for a list of fields.
If you want the contents (names of options I'm guessing), you'll need to join jiraissue and customfieldvalue, and then customfield option on each result you get.
Seriously, stop using the database for this, it's horrible.
Ok thanks @Nic Brough _Adaptavist_
I can notice I wasn't clear. So I can get the customfield names in the CUSTOMFIELD table and now I'm looking for their respective contents on the CUSTOMFIELDOPTION table.
I get your point about not using the Jira database for reporting, it's very intensive.
@Manuel GalvánI need the custom fields of a Issue with the respective values from CUSTOMFIELDOPTION table.
So the SQL query example I have is like:
get the ID from CUSTOMFIELD and use that ID to get the ISSUE from CUSTOMFIELDVALUE and CUSTOMVALUE from CUSTOMFIELDOPTION
@Matebese_ S_ _Sithembiso_ this query returns all fields of an issue with option values:
select ji.pkey,ji.issuenum,cf.cfname, cfo.customvalue cfvalue
from customfieldoption cfo,
customfield cf, customfieldvalue cfv, jiraissue ji
where cf.id=cfo.customfield and
cfv.customfield=cf.id and ji.id="issueid" and
cfv.issue=ji.id and cfv.stringvalue=cast(cfo.id as varchar)
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