Changing Custom Field Types

Added by Mark Chaimungkalanont, last edited by Andrew Lui on Jul 17, 2008  (view change)
JIRA Documentation

Index

You generally can't shift between custom field types since the data type they store may not match.

Having said that, 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 ID=(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.

Labels:

Enter labels to add to this page:
Wait Image 
Looking for a label? Just start typing.

Comments (4)

 

  1. Nov 20, 2006

    Keith Champoux says:

    During the migration of bugs from a legacy GNATS-based system into JIRA, the per...

    During the migration of bugs from a legacy GNATS-based system into JIRA, the person performing the migration created a regular "Text Field (< 255 characters)" for capturing the GNATS id from the legacy system, rather than using the "Read-only Text Field". After the migration completed, we realized that it would have been better to have a read-only field. In addition to the list of field types above, it also seems "safe" to change a Text Field to a Read-Only Text Field by updating CUSTOMFIELD.CUSTOMFIELDTYPEKEY from a value of "com.atlassian.jira.plugin.system.customfieldtypes:textfield" to instead have a value of "com.atlassian.jira.plugin.system.customfieldtypes:readonlyfield". We made this change, and it worked fine for us. Of course, fields will only be shown in the JIRA UI if they're populated through some other means (as part of the migration process, or through direct database manipulation of the read-only field).

  2. Nov 09, 2007

    Matt Doar says:

    A Jira forum discussion at http://forums.atlassian.com/thread.jspa?messageID=257...

    A Jira forum discussion at http://forums.atlassian.com/thread.jspa?messageID=257257490&#257257490 lead to the following steps to change a Text (not TextArea) field named "My Text Field" into a Select field. This appears to work for Jira 3.6 and Jira 3.10.

    1. Stop Jira
    2. UPDATE customfield SET CUSTOMFIELDTYPEKEY='com.atlassian.jira.plugin.system.customfieldtypes:select', CUSTOMFIELDSEARCHERKEY=NULL where cfname="My Text Field"
    3. Start Jira
    4. Configure the custom field to add the options. I assume you have to add an option for every distinct value, which you can probably find with:

    select * from customfieldvalue where customfield=(select id from customfield where cfname='My Text Field');

    and then use some version of DISTINCT to identify the unique value. Text with spaces may cause a problem - does this get escaped somewhere?

    5. Edit the custom field to set the Searcher
    6. Reindex
    7. Find an issue with an existing value and check that it is now editable as a select list

    ~Matt

    1. Dec 07, 2007

      Matt Doar says:

      A specific example to get the unique values nicely sorted of a numeric field: ...

      A specific example to get the unique values nicely sorted of a numeric field:

      select distinct numbervalue from customfieldvalue where customfield=(select id from customfield where cfname='LevelOfEffort') order by numbervalue;

      Then since we are changing the type of the field from one that stores its values in the numbervalue column of customfieldvalue, we have to copy the numbervalues into the stringvalue field with some (mySql) SQL such as this:

      update customfieldvalue set stringvalue =
  case
    when numbervalue = 0 then '0.0'
    when numbervalue = 0.100000 then '0.1'
  # Make sure you cover all of the distinct values here ...
  else stringvalue
end where customfield=(select id from customfield where cfname='LevelOfEffort');

  3. Jul 16

    Franz X Hartmann says:

    After the sentence Or if you wanted to convert text-field custom field to a fr...

    After the sentence

    • Or if you wanted to convert text-field custom field to a free-text-field ...

    the command should be

    • UPDATE customfieldvalue SET textvalue=stringvalue WHERE customfield=(SELECT ID ...

    instead of

    • UPDATE customfieldvalue SET textvalue=stringvalue WHERE ID=(SELECT ID ...