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-incrementWith this:
if ($focus->name==''){ //Check for new record
$focus->name = $last+1; //This is the auto-increment
}
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!