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

Sql Query for Fields Configuaration

Hi 

I want to pull out custom fields that are in a particular field configuration. if they are hidden, shown etc. This is be done with a SQL query. I have a query to pull the custom fields and values. but this got me stuck. I dont know where to start. Can anyone please help me out!

1 answer

hi @Harsha Vardhan Doddi I think you need to use fieldlayou and fieldlayoutitem tables. Maybe something like...

select fl."name", fli.* from fieldlayout fl join fieldlayoutitem fli on (fl.id = fli.fieldlayout) where fli.fieldidentifier = 'customfield_10318'

@Martin Bayer _MoroSystems_ s_r_o__ , thanks for the reply. The runs great, however, under the fieldidentifier column, there are values customfield_13326, customfield_13325, customfield_12624 and many like this. where do I get these from? 

It is "string format" of custom field's ID. So "customfield_" is prefix and number part is ID of the field in database.

You can easily find it by hovering Edit on the custom field and ID is displayed in the target URL.

If you need more information, don't hesitate to ask me :)

@Martin Bayer _MoroSystems_ s_r_o__ , I checked the ID and looked up in the custom field table, and they are matching, however, how do I get this data, as this is containing string and number and ID is just number. Any query? You've been really helpful here!

Hi @Harsha Vardhan Doddi sorry for late response. As I descibed, String "customfield_" is just prefix for string ID of custom field. So if your custom field's ID is 11111, then you have to use query

...fli.fieldidentifier = 'customfield_11111'

 I could prepare query which will take name of the custom field as the parameter but it is not safe because Jira allows you to create multiple fields with the same name.

Is it clear or do I understand your question wrong? :)

@Martin Bayer _MoroSystems_ s_r_o__ , thanks for the reply here. Appreciate it. However, can we pull out the field configurations and custom fields in them and layout too in one query instead of passing parameters to the query

hi @Harsha Vardhan Doddi following SQL query will return

  • custom field name
  • field configuration name
  • hidden
  • required

select cf.cfname as "custom field name", fl."name" as "field configuration name", fli.ishidden as "hidden", fli.isrequired as "required" from fieldlayout fl
join fieldlayoutitem fli on (fl.id = fli.fieldlayout)
join customfield cf on ('customfield_'||fl.id = fli.fieldidentifier)

@Martin Bayer _MoroSystems_ s_r_o__ , thanks for this. Can combine the one you gave and this one to retrieve values too?

select distinct c.cfname as 'Custom Field'
  ,(right(CUSTOMFIELDTYPEKEY, charindex(':', reverse(CUSTOMFIELDTYPEKEY)) -1 )) as 'Field Type'
  ,'Custom Field Value' = ISNULL(STUFF((SELECT ', ' + CAST(CO.customvalue AS varchar(50)) FROM CustomfieldOption CO WHERE CO.customfield = C.ID FOR XML PATH('') ),1,1,'') , '') 
from customfield C
 left join customfieldoption CO on C.ID = CO.customfield
 order by 3 desc;

 Appreciate your assistance. 

hi @Harsha Vardhan Doddi you mean custom field options in case it is single/multi select CF? Or value for custom field + issue?

Suggest an answer

Log in or Sign up to answer
TAGS
Community showcase
Posted in Jira Software

Presenting the "Best of 2020" Jira Software roundup!

Catch up with Atlassian Product Managers in our 2020 Demo Den round-up! From Advanced Roadmaps to Code in Jira to Next-Gen Workflows, check out the videos below to help up-level your work in the new ...

7,184 views 8 28
Join discussion

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