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