Howto Change Custom Field Data Type

[andreasw]

Hi all,

I'd like to change to data type of one of our custom fields.
It's stored as varchar in Mysql, but Sugar thinks it should be Int.
We are essentially storing Ints, but sometime they got a trailing zero (which is essential in our case), but Sugar trims that Zero when saving an entry.
I cannot change to data type via the studio interface and found no php-file with data type definitions for custom fields.

So where and how to change it?

Thanks,
Andreas

[paikmoses]

Hi Andreas,

You can change the datatypes through phpMyAdmin. Simply go to that table and make the suitable changes.

If there's any problem, try to run this query:

ALTER TABLE `leads_cstm` CHANGE `website_c` `website_c` TEXT;

I did this to change the custom field datatype in Leads module.

Hope this helps.

Regards,

[andreasw]

Hi paikmoses,

sorry, forgot to mention that the mysql data type is already set to varchar(255)!
So the internally used type must be stored somewhere else.

Andreas

[Markku]

Hi andreasw,

I just succesfully created new custom field (type int) in Accounts module and later changed datatype of it to varchar using paikmoses' approach. Not sure why it does not work for you, maybe you can check that you modified correct table and field?

If you have test installation in hand, you can also use it just to verify if changing datatype of custom fields works or not.

[andreasw]

Hi Markku,

there is no problem changing the data type in Mysql.
But Sugar uses another datatype than that defined in Mysql!

Try this:
1. Create int datatype in Sugar.
2. Enter some valid data using Sugar.
3. Change the datatype to varchar/text in Mysql using paikmoses' approach.
4. Now try to add a string instead of an int in Sugar. What happens?

In my case I'm not allowed to enter a string (error message: undefined "your-custom_field-name")

Additionally, when I use the repair custom field function (Admin/Studio) Sugar insists to change the column type varchar (as defined in Mysql) to int (simulation mode).

So how come, Sugar wants to treat this column type as an int although it is defined as an varchar?

Andreas

[andreasw]

Can somebody reproduce this?

Try this:
1. Create int datatype in Sugar.
2. Enter some valid data using Sugar.
3. Change the datatype to varchar/text in Mysql using paikmoses' approach.
4. Now try to add a string instead of an int in Sugar. What happens?

In my case I'm not allowed to enter a string (error message: undefined "your-custom_field-name")

Additionally, when I use the repair custom field function (Admin/Studio) Sugar insists to change the column type varchar (as defined in Mysql) to int (simulation mode).

Anyone from the Sugarteam?


Andreas

[andreasw]

Finally found the answer in the sugar wiki.
Simply changing the data type of the mysql field doesn't work. You also have to change the corresponding data_type value in fields_meta_data

Andreas

[helgithor]

I want to thank you andreasw and paikmoses for this input. Your solution helped me solve an issue I've been tackeling for quite a while.

After a module builder update installation my custom module didn't show in the system and relationships broke without any quick solution to be repaired. The goal of the update was to change field type of 6 fields. I ended up by restore from backup and trying again manually.

Two notes:
1. After reading this thread I had to search for the settings in Sugar to change field type (mysql change is not enough) and I didn't find it in metadata files (like directed above) but instead I found it in vardefs.php under the corresponding module.
2. I changed the end of paikmoses's command from TEXT to varchar(30) // needed 30 character input

To summarize the solution for future readers (and maybe some of them newbies or amateurs like me):
I needed to change 6 fields from 10 character int to 30 character varchar

1. Install phpMyAdmin if not available. Google for installation instructions that correspond with your OS.

2. Run Paikmoses's command on the sugar database via phpMyAdmin
ALTER TABLE 'table_name' CHANGE 'field_name' 'field_name' FIELDTYPE; // field name is repeated
Example: ALTER TABLE `leads_cstm` CHANGE `website_c` `website_c` varchar(30);

3. Edit vardefs.php for the corresponding module. It is found in /modules/module_name/vardefs.php
Change TYPE to the type you prefer. Example from my case: 'type' => 'int', became 'type' => 'varchar'
Change LEN to the length you prefer. Example from my case: 'len' => '10' became 'len' => '30'
Check other similar fields to see if the code for your field is generally the same.

This worked 100% for me. I hope this summary helps someone on the way to solution

[rafael.q.g@hotmail.com]

To change the sugar field type in a safe way you have to:
1. On file /custom/extantions/modules/module_name/Ext/Vardefs/some_file.php
2. add a code like: $dictionary['module_name']['fields']['field_name']['type']='new_type';
3. Run repair/rebuild, and execute the code to synchronize vardefs with database.
4. Now it should be working.

[chiner]

I had an issue back in July and needed to change a custom field type from text to integer. I found a similar post on the forum from cwill on 2008-12-29.

mvngti posted this:
deleting a custom field does not actually delete the column from the database unless you repair the database. So you can delete the custom field and "IMMEDIATELY* add it again with *EXACTLY* the same name.

No data will be lost and the field will be changed without editing any files directly.

I used this method to change my custom field from text to integer and it worked perfectly without touching any code.