SugarCRM Changing Custom Field Data Types

In a previous article, I detailed a bug in SugarCRM concerning the 'datetime' data type in custom fields.  Since then I've had to modify the 'datetimecombo' data type in custom fields in other instances of Sugar several times.  'datetimecombo' is how MySQL describes the Sugar 'datetime' data type in the database.

Changing the field data type is fairly straight forward, and can be used to change any custom field to a new data type.

When you first create a custom field in Sugar, you're asked for a data type for the field.  Once you save the new custom field in Sugar, you'll find you cannot go back and change the data type for that custom field in Sugar's Studio.  The problem is compounded, if asked to do that by a client, after data has begun to populate in the database.  You don't want to lose any data in the database.

The way to change custom field data types is in the database.

1. Bring up the database with your favorite database viewing tool.

I use SQL Workbench, or phpMyAdmin.

2. Go to the custom table that contains the field you want to change.

When you create a new field in Sugar, the field is created in the [module name]_cstm table.  For example, to change a 'datetime' data type field in the Bugs module, I would go to the "bugs_cstm" table.

3. Scan down the fields in the table until you find the [field]_c that needs to be changed.  If you look at the schema of the table, you'll see the offending the field's data type.  Every field in the bugs_cstm table has the field name with a _c suffix, which tells Sugar its dealing with a custom field.

4. Run the below MySQL query to change to an appropriate MySQL data type.  In the case of my bugs_cstm example:

"ALTER TABLE [dbname].bugs_cstm MODIFY date_identified_c date;"

This will change the data type for field 'date_identified_c' in the bugs_cstm table to 'date' from 'datetimecombo'.

A couple of thoughts here.

The above is a specific example of modifying data type datetime field to a date field.  The above query can be used to change to other data types, also, for example, bool to varchar.

In the case of datetime fields, you will not lose your date data with the above ALTER command.  MySQL is smart enough to truncate the time from the field and save the existing date.

Change only to a MySQL correct data type in the format, lowercase, that MySQL uses to record its data types in the database.  If you go to a varchar, don't forget to put the length of the field in also, usually the 'len' field.  If in doubt, find a field with your new data type and copy that in to the field


We're not quite done yet.  The data for that field is now correct in the database, but if you look in Studio you'll see that Sugar still thinks the field is a datatime field.

We need one more database modification.  Sugar saves the schema for every custom field in Sugar in a separate table, 'fields_meta_data'

5. Go to the 'fields_meta_data' table.

6. Find the name of the field you want to change in the table.

7. The field in the table you want to change is 'type' and maybe 'len' if your changing varchar lengths.

8. Alter the type field to the new data type with the below SQL query.

"UPDATE [dbname].fields_meta_data SET type = 'date' WHERE name = 'date_identified_c';"

9. Final step is to go to the Admin->Repair->Quick Repair and Rebuild in Sugar and do a rebuild, so Sugar can adjust all its auto-generated files and Studio field data to the new data type.

You're done.