How to check if all fields on your JIRA screens are visible in the fieldconfig

rootUncategorized

jira-migration1-1

Today I had to create a project configuration which on an instance with > 500 custom fields.  I had to create multiple screens and on each screen multiple fields.

Given that managing the visibility of fields in JIRA is a bit of a dark art, I thought that another – less error prone approach – would make my life a bit easier.

So I decided to create a very simple query which checks that each field on a screen is visible in the corresponding field configuration.

Here is the query – hopefully useful for someone

select fss.NAME,  cf.cfname
from fieldscreen fss
inner join fieldscreentab fst on fst.FIELDSCREEN = fss.id
inner join fieldscreenlayoutitem fsl on fsl.FIELDSCREENTAB = fst.ID
inner join fieldlayoutitem fli on fli.FIELDIDENTIFIER = fsl.FIELDIDENTIFIER
inner join fieldlayout fl on fli.FIELDLAYOUT = fl.ID
left join customfield cf on cf.ID = cast(SUBSTRING(fsl.FIELDIDENTIFIER,13,20) as integer)
where fss.NAME like 'PTR_%'
and fl.NAME like 'PTR_%'
and fli.ISHIDDEN = 'True'

 

Used on line
Table
Used for
3fieldscreenThis table lists all the screens of the configuration
4fieldscreentabeach screen can have multiple tabls
5fieldscreenlayoutitemthis is the actual table containing an entry for each screen object
6fieldlayoutitemthe fieldlayoutitem are the entries in the field configuration
9,10Limit the query to the screens of interest. As we are using a prefix type of scheme it is easy to select the right screens
11Show all entries which are marked as hidden in the field configuration