Creating an Auto-Incrementing Field

Overview

This article will cover the two approaches to creating an auto-incrementing field in Sugar.

Case 1: Auto-Incrementing a Field Using a Logic Hook

The benefits of having an auto-incrementing field populated from a logic hook are that it can be incremented based on additional logic. It also allows for multiple auto-incrementing fields, while a database-level auto-incrementing field is only allowed once per table. Auto-incremented fields handled in a logic hook can also be formatted as needed, such as adding leading zeros or alpha characters. You can also control the auto-increment value based on another field, for instance, if you wanted to increase a version_number based on other records related to the same parent. For this example, we will create a simple auto-incrementing field that goes up by 1 whenever the record is saved for the first time.

First, create an integer field incrementing_number_c in Studio.

Next, create a custom class and method that the logic hook will call:

./custom/modules/Accounts/Accounts_Save.php

  <?php

class Accounts_Save
{
    function auto_increment($bean, $event, $arguments)
    {
        if (!$arguments['isUpdate']) {
            $sq = new \SugarQuery();
            $sq->from(BeanFactory::newBean('Accounts'), ['team_security' => false]);
            $sq->select->fieldRaw('MAX(' . $sq->getFromAlias() . '_cstm.incrementing_number_c)', 'current_max_id');
            $current_max_id = $sq->getOne();
            $max_id = (empty($current_max_id)) ? 1 : $current_max_id + 1;
            $bean->incrementing_number_c = $max_id;
        }
    }
}

Next, register the custom logic hook via the LogicHooks Extension framework by creating a file at ./custom/Extension/modules/Accounts/Ext/LogicHooks. An example is shown below:

./custom/Extension/modules/Accounts/Ext/LogicHooks/autoIncrementOnSave.php

  <?php

$hook_array['before_save'][] = array(
    2, 
    'Custom Logic to Auto-Increment integer_field_c', 
    'custom/modules/Accounts/Accounts_Save.php',
    'Accounts_Save', 
    'auto_increment'
);

After creating these files, run a Quick Repair and Rebuild.

Case 2: Creating an Auto-Incrementing Field on the Database

Creating an auto-incrementing field at the database level has the benefit of being unique and easy to manage. One downside to having an auto-incrementing field at the database level is that it means only being allowed one such field per module, as databases only allow one auto-incrementing field per table. A custom auto-incrementing field also relies on updating a module's core table, which is less upgrade safe. For this reason, a logic hook based auto-incrementing field is recommended over a database-driven auto-incrementing field.

To create an integer field in Sugar which auto-increments at the database level, we will create a custom field via the VarDefs extension as shown below:

./custom/Extension/modules/Accounts/Ext/Vardefs/autoIncrement.php

  <?php

$dictionary['Account']['fields']['account_number_auto'] = array (
    'name' => 'account_number_auto',
    'vname' => 'LBL_NUMBER_AUTO',
    'type' => 'int',
    'readonly' => true,
    'len' => 11,
    'required' => true,
    'auto_increment' => true,
    'unified_search' => true,
    'full_text_search' => array(
        'enabled' => true, 
        'searchable' => true, 
        'boost' => 1.25
    ),
    'comment' => 'Visual unique identifier',
    'duplicate_merge' => 'disabled',
    'disable_num_format' => true,
    'studio' => array('quickcreate' => false),
    'duplicate_on_record_copy' => 'no',
);

$dictionary['Account']['indices']['account_number_auto'] = array(
    'name' =>'accountsnumk_cstm', 
    'type' =>'unique', 
    'fields'=>array('account_number_auto'),
);

Navigate to Admin > Studio > Repair > Quick Repair and Rebuild. After the repair, you will see a vardef comparison as shown below:

  /*COLUMNS*/
/*MISSING IN DATABASE - account_number_auto - ROW*/
ALTER TABLE accounts add COLUMN account_number_auto int(11) NOT NULL auto_increment;
/* INDEXES */
/*MISSING INDEX IN DATABASE - accountsnumk_cstm - unique ROW */
ALTER TABLE accounts ADD CONSTRAINT UNIQUE accountsnumk_cstm (account_number_auto);;

Alter this to be the following and execute the changes:

  ALTER TABLE accounts add COLUMN account_number_auto int(11) UNIQUE NOT NULL auto_increment;

Note: Since auto-incremented fields are populated at the database level, all existing records in the database will have this field populated as soon as the field is created on the database table. The value assigned will not be based on any logic (such as the record creation date) that can be controlled or predicted. Therefore when sorting by this field, records created after the field was added will be in order that they were created, but records created before will not.