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!
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'
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 :)
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? :)
hi @Harsha Vardhan Doddi following SQL query will return
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.
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 ...
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