Change field type with data to text
2013 10 Dec

Change Field Type with data to Text

Sometimes, our client can change his/her requirements and that change might involve changing the type of a field from any type to TEXT. Now, Drupal 7 does not support changing the field types if there is existing data.

First thing which comes to our mind is to create a new field and delete the old one, but we have data in the fields and we cannot afford to lose them. So, what to do now?

There might be many ways to do this, but the thing which I found satisfactory and used it, is to change the settings of the field in the drupal installation's database. But before doing this, do keep a dump of your database so that you can revert back to status quo. And do check your queries not twice but three or four times before running them since you will be changing the database of your drupal installation.

For this you have to run around 8 queries

Query 1 and 2: -

Change the type of column in the field's tables.

ALTER TABLE field_data_FIELD_NAME MODIFY FIELD_NAME_value varchar(SIZE);

ALTER TABLE field_revision_FIELD_NAME MODIFY FIELD_NAME_value varchar(SIZE);

Here, SIZE can be 10, 20, 255 etc.

FIELD_NAME is the machine of the field.

Query 3 & 4: -

Add the FIELD_NAME_format column in the field's tables. (Run these two queries only if the columns does not exist.)

ALTER TABLE field_data_FIELD_NAME ADD FIELD_NAME_format VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL;

ALTER TABLE field_revision_FIELD_NAME ADD FIELD_NAME_format VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL;

Query 5 & 6: -

Update type and module column of field_config table. 

UPDATE field_config SET type = 'text' WHERE field_name = 'FIELD_NAME';

UPDATE field_config SET module = 'text' WHERE field_name = 'FIELD_NAME';

Query 7: -

Update data column of field_config_instance table. In this since we will be working with data in serialized form, you have to make sure that you are attentive to minute details.

UPDATE field_config_instance SET data = 'a:7:{s:5:"label";s:LENGTH_OF_LABEL:"LABEL OF FIELD";s:6:"widget";a:5:{s:6:"weight";s:1:"2";s:4:"type";s:14:"text_textfield";s:6:"module";s:4:"text";s:6:"active";i:1;s:8:"settings";a:1:{s:4:"size";s:2:"SIZE";}}s:8:"settings";a:2:{s:15:"text_processing";s:1:"0";s:18:"user_register_form";b:0;}s:7:"display";a:1:{s:7:"default";a:5:{s:5:"label";s:5:"above";s:4:"type";s:12:"text_default";s:8:"settings";a:0:{}s:6:"module";s:4:"text";s:6:"weight";i:WEIGHT;}}s:8:"required";i:REQUIRED;s:11:"description";s:LENGTH_OF_THE_FOLLOWING_STRING:"DESCRIPTION";s:13:"default_value";N;}' WHERE field_name = 'FIELD_NAME';

REQUIRED = 0 or 1.

WEIGHT = Weight of the field in content type.

DESCRIPTION = Description, if any.

In the table field_config_instance, there is one more column FIELD_ID, keep in mind the value in this field for field_name = 'FIELD_NAME'.

Query 8: -

Update the data column of field_config table.

UPDATE field_config SET data = 'a:7:{s:12:"translatable";s:1:"0";s:12:"entity_types";a:0:{}s:8:"settings";a:1:{s:10:"max_length";s:2:"MAX_LENGTH_OF_FIELD";}s:7:"storage";a:5:{s:4:"type";s:17:"field_sql_storage";s:8:"settings";a:0:{}s:6:"module";s:17:"field_sql_storage";s:6:"active";s:1:"1";s:7:"details";a:1:{s:3:"sql";a:2:{s:18:"FIELD_LOAD_CURRENT";a:1:{s:LENGTH_OF_THE_FOLLOWING_STRING:"field_data_FIELD_NAME";a:2:{s:LENGTH_OF_THE_FOLLOWING_STRING:"value";s:LENGTH_OF_THE_FOLLOWING_STRING:"FIELD_NAME_value";s:6:"format";s:LENGTH_OF_THE_FOLLOWING_STRING:"FIELD_NAME_format";}}s:19:"FIELD_LOAD_REVISION";a:1:{s:LENGTH_OF_THE_FOLLOWING_STRING:"field_revision_FIELD_NAME";a:2:{s:5:"value";s:LENGTH_OF_THE_FOLLOWING_STRING:"FIELD_NAME_value";s:6:"format";s:LENGTH_OF_THE_FOLLOWING_STRING:"FIELD_NAME_format";}}}}}s:12:"foreign keys";a:1:{s:6:"format";a:2:{s:5:"table";s:13:"filter_format";s:7:"columns";a:1:{s:6:"format";s:6:"format";}}}s:7:"indexes";a:1:{s:6:"format";a:1:{i:0;s:6:"format";}}s:2:"id";s:4:"FIELD_ID";}' WHERE field_name = 'FIELD_NAME';
 After running these queries the type of field will be changed from any other type to TEXT.

If you are not sure that changing the data values would work, then the best way is to create the field in any other drupal instance, copy the data from there, and run the queries using that data. But do keep in mind to change the FIELD_ID.

Latest Blogs

Healthcare mobile app development

How Healthcare Mobile Apps is Booming the Healthcare Sector

No one can deny that mobile apps have revolutionized all sectors, whether eCommerce, education, or mobile healthcare apps. There is hardly any sector untouched by the blessings of mobile apps.

Read More

Mobile UX design

Top Do's & Dont's for a Perfect Mobile User Experience Design

In any situation, the first impression is considered the last impression. The same is the case in the world of mobile applications.

Read More

Mobile app development trends

15 Effective Mobile App Development Trends That Will Boost

The mobile app development industry is continuously evolving with great speed. There is indeed tough competition in this industry.

Read More

Why it is Best to Use ReactJS for Web Development

Why it is Best to Use ReactJS for Web Development

Front-end and frameworks are inevitable and essential parts of web development. As the technologies improve day by day, many new frameworks and libraries are coming to get better results.

Read More