Sql Query for Fields Configuaration

[Harsha Vardhan Doddi]

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!

[Martin Bayer _MoroSystems_ s_r_o__]

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'

[Harsha Vardhan Doddi]

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

[Martin Bayer _MoroSystems_ s_r_o__]

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

[Harsha Vardhan Doddi]

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

[Martin Bayer _MoroSystems_ s_r_o__]

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? :)

[Harsha Vardhan Doddi]

@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

[Martin Bayer _MoroSystems_ s_r_o__]

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)

[Harsha Vardhan Doddi]

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

[Martin Bayer _MoroSystems_ s_r_o__]

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