How To create Auto-Generated field in Sugar CRM

**anandece1** · 2010-04-29, 12:33 PM

Hi All,
Im using Sugarcrm 5.5 version. I need a auto-generated field in a custom module . Initially i tried to make the respective field as a auto increment type in the MYSQL database , but the structure is not getting save as already there is a auto increment field in the table. I need a no to be generated everytime when we create the respective module, it should want to display in the edit view. Some random number generation would be preferable . Can any one give me an exact solution to do so .

**kenneth.thorman** · 2010-04-29, 01:56 PM

Something like the below should work.

Generate the module in MB as you want

Override the Save function in the generated class file to select max(columnename) +1

Please note I have not tested this code, but it should work (with few or minor modifications).

However this does not give you the id in edit view since this does not generate the id until you are saving.

file module/CM_CourseID/CM_CourseID.php

PHP Code:

 class CM_CourseID extends CM_CourseID_sugar {

    

    function CM_CourseID(){    

        parent::CM_CourseID_sugar();

    }

    

 
   /*

This function is added here to override the default save functionality

*/

    function save($check_notify = FALSE){

        global $sugar_config, $mod_strings;

        

        if(!empty($_REQUEST['myintcolumn'])){

            $site_url=$sugar_config['site_url'];

            $db =&PearDatabase::getInstance();

 
            $courseid=$_REQUEST['name'];

          $query = "SELECT max(myintcolumn)+1 nextid FROM cm_courseid'";

            $result = $db->query($query);

            $row=$db->fetchByAssoc($result);

            $count=$row['nextid '];

            if(isset($row['nextid ']) ){

                echo "<html><body><script>alert('Put debugging text here); history.go(-1);</script></body></html>";

                exit;

            }

        }

        parent::save($check_notify);

 
    }

    

}

Regards
Kenneth Thorman

**kenneth.thorman** · 2010-04-30, 12:44 PM

There is another option as well using the logic hook before_save. But rather then writing the code again I searched the forums with the search terms

"autoincreament"

one of the results are

http://www.sugarcrm.com/forums/showthread.php?t=27290

There is a good explanation as well

Regards
Kenneth Thorman

**anandece1** · 2010-05-06, 08:06 AM

But i can't able to make the custom field as an auto increment type, as already there is a field named 'id' is auto incremented .... In all the modules , the field 'id' is auto incremented .....

**kenneth.thorman** · 2010-05-06, 11:21 AM

Most of the time it does not make sense to have more than one auto increment field in a single table in a database. If you want to achieve this you will have to do this manually like mentioned above.

If you search Google from something like "multiple auto increment column in same table mysql" then you will find that there are limitations to what MySql can handle in the different storage engines. InnoDB has reservations about more than one autoinc field.

so to simulate this you can use the following code

Code:

CREATE TABLE `sequences` (
  `id` double default NULL,
  `name` varchar(64) NOT NULL,
  PRIMARY KEY  (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

PHP Code:

     $sql = "SELECT count(*) as cnt 

        FROM sequences

        WHERE name='{$sequence_name}'";  

    $res = $GLOBALS['db']->query($sql);

    $row = $GLOBALS['db']->fetchByAssoc($res);

    

    if($row['cnt'] != 1){

        $sql = "INSERT INTO sequences (id, name)

            VALUES (0, '{$sequence_name}')";

        $res = $GLOBALS['db']->query($sql);

    }

    

    $sql = "UPDATE sequences SET id=LAST_INSERT_ID(id+1) WHERE name = '{$sequence_name}'";

    $res = $GLOBALS['db']->query($sql);

    

    $sql = "SELECT LAST_INSERT_ID() as LAST_INSERT_ID";

    $res = $GLOBALS['db']->query($sql);

    $row = $GLOBALS['db']->fetchByAssoc($res);

Regards
Kenneth Thorman

**kenneth.thorman** · 2010-05-06, 11:22 AM

Hi again

Just to summarize I do not think you can get SugarCrm to generate 2 autoincrement columns for you.

You will have to do the coding yourself

Regards
Kenneth Thorman