Results 1 to 10 of 10

Thread: Howto Change Custom Field Data Type

  1. #1
    andreasw is offline Sugar Community Member
    Join Date
    Mar 2005
    Posts
    130

    Default Howto Change Custom Field Data Type

    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
    Andreas Wilm

    Capsid - Agentur für LifeScience
    http://www.capsid.com/

    ***
    sugar os 4.5.1d
    mysql-5.0.18
    php-5.1.2
    SuSE Linux 10.1

  2. #2
    paikmoses's Avatar
    paikmoses is offline Sugar Community Member
    Join Date
    Jan 2006
    Posts
    541

    Default Re: Howto Change Custom Field Data Type

    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:
    Code:
    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,

  3. #3
    andreasw is offline Sugar Community Member
    Join Date
    Mar 2005
    Posts
    130

    Default Re: Howto Change Custom Field Data Type

    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
    Andreas Wilm

    Capsid - Agentur für LifeScience
    http://www.capsid.com/

    ***
    sugar os 4.5.1d
    mysql-5.0.18
    php-5.1.2
    SuSE Linux 10.1

  4. #4
    Markku's Avatar
    Markku is offline Sugar Community Member
    Join Date
    Nov 2004
    Location
    Helsinki
    Posts
    910

    Default Re: Howto Change Custom Field Data Type

    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.

  5. #5
    andreasw is offline Sugar Community Member
    Join Date
    Mar 2005
    Posts
    130

    Default Re: Howto Change Custom Field Data Type

    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
    Last edited by andreasw; 2007-02-05 at 10:16 AM.
    Andreas Wilm

    Capsid - Agentur für LifeScience
    http://www.capsid.com/

    ***
    sugar os 4.5.1d
    mysql-5.0.18
    php-5.1.2
    SuSE Linux 10.1

  6. #6
    andreasw is offline Sugar Community Member
    Join Date
    Mar 2005
    Posts
    130

    Default Re: Howto Change Custom Field Data Type

    Can somebody reproduce this?

    Quote Originally Posted by andreasw
    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
    Last edited by andreasw; 2007-02-05 at 11:08 AM.
    Andreas Wilm

    Capsid - Agentur für LifeScience
    http://www.capsid.com/

    ***
    sugar os 4.5.1d
    mysql-5.0.18
    php-5.1.2
    SuSE Linux 10.1

  7. #7
    andreasw is offline Sugar Community Member
    Join Date
    Mar 2005
    Posts
    130

    Default Re: Howto Change Custom Field Data Type

    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
    Andreas Wilm

    Capsid - Agentur für LifeScience
    http://www.capsid.com/

    ***
    sugar os 4.5.1d
    mysql-5.0.18
    php-5.1.2
    SuSE Linux 10.1

  8. #8
    helgithor is offline Member
    Join Date
    Nov 2010
    Posts
    7

    Smile Re: Howto Change Custom Field Data Type

    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

  9. #9
    rafael.q.g@hotmail.com's Avatar
    rafael.q.g@hotmail.com is offline Sugar Community Member
    Join Date
    Jun 2011
    Location
    Florianópolis - Brazil
    Posts
    782

    Default Re: Howto Change Custom Field Data Type

    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.
    Rafael Queiroz Gonçalves
    Advanced OMG UML Certified Professional
    Sun Certified Enterprise Architect for the Java Platform
    Sun Certified Programmer for the Java 2 Platform
    IBM Certified Advanced Application Developer - Lotus Notes and Domino
    IBM Certified Application Developer - IBM WebSphere Portlet Factory
    Computer Science Mastering / UFSC - PPGCC

  10. #10
    chiner is offline Junior Member
    Join Date
    Jan 2009
    Location
    Kansas City, MO
    Posts
    3

    Default Re: Howto Change Custom Field Data Type

    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.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Saving and updating custom fields data
    By jones70 in forum Developer Help
    Replies: 2
    Last Post: 2011-10-05, 06:10 AM
  2. Custom field data not being saved
    By smueller72 in forum Help
    Replies: 5
    Last Post: 2006-12-21, 04:14 AM
  3. Asterisk Patch 1.1.0 Crash on logon
    By skyracer in forum Help
    Replies: 6
    Last Post: 2006-07-08, 06:30 AM
  4. Replies: 0
    Last Post: 2006-06-10, 04:09 PM
  5. Replies: 0
    Last Post: 2005-08-12, 08:13 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •