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

Jira data model to find custom fields

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:

'Acceptance Criteria'
'COD'
'Customer Request Type'
'Dependency'
'Epic Benefit'
'Epic Link'
'Epic Net Present Value'
'EPIC Non incremental benefits'
'Estimated Cost of Epic'
'Funding UID'
'Job Size'
'Label'
'Priority'
'Project Name'
'Release Value'
'Risk Reduction/ Opportunity Enablement'
'Status Category'
'Sub-Task'
'Theme/Capability'
'Time Criticality'
'UID Benefit'
'UID Cost'
'UID Net Present Value'
'User Value'
'WSJF Benefit hypothesis'
'Go-live Date'
'ETE Date'
'QA Date'

 

Kindly assist on how I can find these custom fields in the CUSTOMFIELDOPTION table.

2 answers

2 accepted

0 votes
Answer accepted

Try this query (is in postgreSQL but you can adapt it):

select cfo.customvalue from customfieldoption cfo, customfield cf
where cf.cfname = 'customfieldName' and cf.id=cfo.customfield

0 votes
Answer accepted

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:

 

Select

[cfo].customvalue As [APIApplication],

Convert(Int,[cfv].[Issue]) As [IssueID],

[cf].cfname As [Description]

From [customfield] As [cf]

Inner Join [customfieldvalue] As [cfv]

On

[cf].[ID] = [cfv].CustomField

inner join customfieldoption As [cfo]

On

[cfo].[CUSTOMFIELD] = cf.[ID]

and

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.

@Matebese_ S_ _Sithembiso_  Do you need custom fields of a issue with their values or only custom fields with all posible values (for example all options of a list custom field)?

Yes, it's clear what you're doing, as I said, you'll need to join jiraissue and customfieldvalue, and then customfield option on each result you get.

@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)

Like Manuel Galván likes this

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