Certain fields can be safely upgraded, such as Version and Select lists to their multiple values counterpart. You can change the “customfieldtypekey” in the “customfield” table to whatever you need it to be. The table below lists the keys for commonly changed fields.

Custom Field Type Type Key
Single Version com.atlassian.jira.plugin.system.customfieldtypes:version
Multi Version com.atlassian.jira.plugin.system.customfieldtypes:multiversion
Single Select com.atlassian.jira.plugin.system.customfieldtypes:select
Multi Select com.atlassian.jira.plugin.system.customfieldtypes:multiselect
Multi User com.atlassian.jira.plugin.system.customfieldtypes:multiuserpicker

When moving back from a multi select list a select list, you have to make sure that only one item is selected for each multi select list.

When moving from multi-select to multi-user, you have to ensure that each select-list value is a username (userbase.username value).

For select lists, you also need to update the “customfieldsearcherkey” field to use an appropriate searcher:

  • For multi-selects, it is “com.atlassian.jira.plugin.system.customfieldtypes:multiselectsearcher”
  • For select lists, use “com.atlassian.jira.plugin.system.customfieldtypes:selectsearcher”
  • For multi-user pickers, use “com.atlassian.jira.plugin.system.customfieldtypes:userpickersearcher”

Examples

For example if you want to update all the version custom fields to become multiple version custom fields, you can use the SQL below.

UPDATE customfield
    SET customfieldtypekey = 'com.atlassian.jira.plugin.system.customfieldtypes:multiversion'
WHERE customfieldtypekey = 'com.atlassian.jira.plugin.system.customfieldtypes:version'

Or if you wanted to convert multi-select-list custom field to a multi-user custom field, first check that all custom field values map to users:

select * from customfieldvalue where id=
   (select id from customfield where cfname=’multisel3′) and
    stringvalue not in (select username from userbase);
Empty set (0.02 sec)

Then you can change the custom field type:

UPDATE customfield
    SET CUSTOMFIELDTYPEKEY='com.atlassian.jira.plugin.system.customfieldtypes:multiuserpicker',
        CUSTOMFIELDSEARCHERKEY='com.atlassian.jira.plugin.system.customfieldtypes:userpickersearcher'
  where cfname='MyMultiSelect';

Or if you wanted to convert text-field custom field to a free-text-field(unlimited text) custom field, first assign the value from stringvalue field to textvalue:

UPDATE customfieldvalue SET textvalue=stringvalue WHERE customfield=(SELECT ID FROM customfield WHERE
customfieldtypekey='com.atlassian.jira.plugin.system.customfieldtypes:textfield' AND cfname='Text Field');

Then, change the custom field type by updating the customfield table as below:

UPDATE customfield SET CUSTOMFIELDTYPEKEY='com.atlassian.jira.plugin.system.customfieldtypes:textarea', CUSTOMFIELDSEARCHERKEY='com.atlassian.jira.plugin.system.customfieldtypes:textsearcher'
where cfname='Text Field';

Restart JIRA. Then reindex (Administration -> Indexing) to update the search index.

Posted by:.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s