Simplify the ID field in tables

How easy is to change the current logic for the ids from “fa097fc09a-ca0f98-8fca79-cafaf798af8” to a simple autoincrement?

Thank you in advance.

Why?

In some tables/modules i would like to have an easy-read-autoincrement-number so the user can easily reference to it.

Aha! This is a good reason to get an A_I value, but not a good reason to change the id field!

One option would be to look at the code and database fields for “bugs” which has an auto-increment bug number, and replicate that would be my suggestion.

Another way to handle this, is through a before_save logic hook. I had to do it that way because I needed some smarts to my numbers… as in the year prepends the auto-increment number, so I couldn’t just do a simple A_I.

Apparently there is also a plugin some folks use. But mikebeck said he has had issues with it.

https://suitecrm.com/forum/suitecrm-7-0-discussion/11105-creating-an-autoincrement-field#38095
https://suitecrm.com/forum/suitecrm-7-0-discussion/11139-how-to-display-auto-increment-data-into-custom-text-field#37950

sieberta

I had issues with that module after reaching the 99999 it just stopped counting because I set the format to be 00001, and there’s no way to correct that (plus, I don’t like the zeros), I’m just using a simple database auto increment field now, the id is an UUID format that is used often in software you can read about it here https://en.wikipedia.org/wiki/Universally_unique_identifier is a great tool, don’t change it, just try to add an auto increment field and handle that as you want.

best regards

I had the same thought!

mikebeck,

Did you just create a custom field, not show it on edit view, and go into your database table definitions to make the field and AI?

Sweet,
sieberta

yes, I just created a custom integer field in SuiteCRM studio, after that went to my command line and executed the correct command to make a field autoincrement and not null, this filed is not shown in edit view of the module only in detailview

best regards

1 Like

@mikebeck Tried to alter the name field (it is more usefull as AI field) but MySQL says “There can be only one auto column and it must be defined as a key”.
I am thinking to change the Primary key from id to id and name and make both Unique.

You can use any unique id in the SugarCRM / SuiteCRM database.

Here’s some background:
https://developer.sugarcrm.com/2008/05/13/what-are-the-impacts-of-swapping-out-guids-in-sugar/

Of course, unless you’re creating the records yourself in PHP, in some custom import function or hook, this won’t be very helpful to you.

Probably you will be better off using a different field for your ID, as previous posters mentioned. But I just wanted that it is possible to use non-guid ids on the primary keys of the database.

I will leave the id field as it is. I just want an autoincrement field. At the beginning i thought that using the id field for this purpose would be fine, but it’s not.
So i decided to use another field for autoincrement. But mysql forces the autoincrement field to be defined as a key. So i made the above thought.
I’ll set PRIMARY KEY(id, name) and ADD UNIQUE INDEX for id and name.

So here is how I did it with logic hook, just in case it becomes necessary for any projects.

My auto-number is a bit complicated, because our numbers are a two digit string representing the year (00-99 is 1900 to 1999) and (A0-Z9) is 2000 to 2269.
Also, we reserve the first 17 numbers in each year for a special year.
Also, I needed leading zeros before the date part and the auto increment part.
Also, I needed the ability to override the auto number system with custom numbers provided by the user.

This hook needs called as a before_save hook, which there is plenty of documentation around the internet how how to do in SuiteCRM and SugarCRM.

<?php
// ADS Changed: 9/12/16: Entire file my code.
// This allows people to get an auto-generated project number, if desired.
if(!defined('sugarEntry') || !sugarEntry) die('Note a Valid Entry Point');

require_once("data/BeanFactory.php");
class ProjNumbering
{

    // Determines the prefix, like B6, B7, etc. for the user.  This should work until 2269 = Z9
    function GetProjNumPrefix(){
        $year = date("Y");
        $firstThree = substr($year, 0, 3);
        $ASCIILetter = $firstThree - 135;
        $projFirstLetter = chr($ASCIILetter);
        return $projFirstLetter . substr($year,3,1);
    }

    // Gets a new project number if the user specifies *AutoNumber*
    function GetProjNumFunction(&$focus, $event)
    {
        if($focus->aa_file_c == '*AutoNumber*'){
            $projNumPref = $this->GetProjNumPrefix();
            $projectBean = BeanFactory::getBean('Project');
            //Get the highest-numbered project, but only from the current years project numbers
            $projectList = $projectBean->get_list('aa_file_c DESC',"project_cstm.aa_file_c like '".$projNumPref."%'",0,1,1);
            $lastProjNum = $projectList['list'][0] ->aa_file_c;
            $incPart = substr($lastProjNum,2,3);
            if($incPart > 17 || is_null($incPart)){
                $newIncPart = str_pad($incPart+1,3,"0",STR_PAD_LEFT);
            } else {
                $newIncPart = "018";
            }
            $focus->aa_file_c = $projNumPref . $newIncPart;
            $GLOBALS['log']->debug('Engage: New AutoAssigned Project Number = ' . $focus->aa_file_c . ' to ProjectID: ' . $focus->id);
        }
    }

}

for your simple example, it would just be (if it were for the Project module)…


<?php
if(!defined('sugarEntry') || !sugarEntry) die('Note a Valid Entry Point');

require_once("data/BeanFactory.php");
class AutoInc
{
function GetNextNumber(&$focus, $event)
{
$bean = BeanFactory::GetBean('Project');
$list = $bean->get_list('name DESC','',0,1,1,0); //Get one record from the module table, sorted by name descending.
$last = $list['list'][0]->name; //Determine what the last name was
$focus->name = $last+1 //This is the auto-increment
}
}

FYI…

1 get_list(
2 $order_by = "",
3 $where = "",
4 $row_offset = 0,
5 $limit=-1,
6 $max=-1,
7 $show_deleted = 0)

This snippit taken from SuuiteCRM for Developers by Jim Mackin.

2 Likes

thank you sieberta.
Your simple exaple for me is working fine but it has a small bug.
Everytime that i edit-save a record the number increases, I want the number to incerease only when a new record saved so i made a little modification to your code.
I changed this:

$focus->name = $last+1   //This is the auto-increment

With this:

if ($focus->name==''){    //Check for new record
	$focus->name = $last+1;      //This is the auto-increment
	}

:slight_smile:

Glad to help! Good point on the continual increment. Mine only applied if the field was ‘AutoNumber’ as we needed the ability to override the auto number.

sieberta

Hi Seberta, i am reading the thread you are writing and I really want to try to achieve the autonumber. Can I ask for your favour to let me know which are the location for me to place the code? Thanks.

You need a ‘before save’ logic hook. Google is your friend, but so am I: http://support.sugarcrm.com/Documentation/Sugar_Developer/Sugar_Developer_Guide_7.9/Architecture/Logic_Hooks/Module_Hooks/before_save/index.html

sieberta

Hi there! I am new to this place, I do not have programming or development knowledge, but I am trying to adapt my CRM, could you tell me what is the step by step to create what is above? the autoincrement number with new users as a special field … Where is it done and how is it done? Thank you!