Best way to auto increment a fields

SuiteCRM Forum - English Language SuiteCRM General Discussion
1

Hey everyone, quick question cause all the topic about this are pretty old.

What the best way to auto increment a fields ? Im gonna import about 4K line wih already a value in it (integer only so from 0 to 4k)

For now I have used the pretty easy method from jrivas in this link :
https://suitecrm.com/suitecrm/forum/suitecrm-7-0-discussion/11105-creating-an-autoincrement-field

I have just created a int fields in account module, put it has auto_increment in accounts_cstm in phpmyadmin and now it work.

I have also tried to put for exemple ‘53’ in that fields to test if it work when I will import my old database, to see if it “continue” or reset everything and it seems to work, it start at 54.

Just wanted to be sure this methode is a good one, cause it seem that there is a lot of possible methode.

Other topic about this :
https://suitecrm.com/suitecrm/forum/suitecrm-7-0-discussion/1841-how-to-add-a-autoincrement-field-for-account-and-contact

1 Like
2

Hi,
If you are not satisfied with these methods. Other option can be to

1: Do the import normally
2: Put the code into LOGIC Hook of the module to Get “MAX” value from the table for that columns by mysql query

LOCK the table

LOCK TABLES table_name  READ;

and Get Max

SELECT MAX(column_name) FROM table_name WHERE deleted=0 LIMIT 1;

and unlock the table for data write

UNLOCK TABLES;

3: Assign the Max value to the Column

All this code should be done in BEFORE save logic hook

That’s it

3

Thanks for your answer !

Its not rly that the method I use dont satisfied me, its just that I dont know if its a good way to proceed… It look maybe too simple ahah

Cause there is no info about this in the documentation, thats why im asking here :stuck_out_tongue:

4

Quick questions… why would I need to LOCK the table?

5

function addJobNumber($bean, $event, $arguments){

    if (empty($bean->project_number_c)){
        $max_job = $GLOBALS['db']->getOne('SELECT MAX(project_number_c) FROM project_cstm');
        $bean->project_number_c = $max_job + 1; 
    }  
}
1 Like
6

Not a bad way to do it. In a before save hook that make it. Will save this in case I need it !

7

You don’t need any development to do that.

You can use a workflow with calculated field like, for a Quote number field:
Q-{now(Y)}-{now(m)}-{GlobalCounter(quote_number;4)}
This would populate a Quote number field with Q-2019-11-0012

You can initialize the counter with the following in config_override.php

$sugar_config['SweeterCalc']['GlobalCounter']['quote_number'] = 11;
2 Likes
8

We need to lock the table because any other instance of the CODE should not insert the next MAX record same time as multiple CRM users will be accessing it same time.

9

Using DBManager how do you lock the database?

10 
$GLOBALS['db']->execute('LOCK TABLE READ');
11 
$GLOBALS['db']->query('LOCK TABLE table_name WRITE')
2 Likes
12

Can you please elaborate a little?
What is GlobalCounter?
What is quote_number? (field in quote module?)
Wouldn’t it make more sense to use GlobalCounterPerModule instead?

13

We are using GlobalCounter and never tried GlobalCounterPerModule.
It wouldn’t change much and I don’t know exactly how you can change that in $sugar_config if you want to define an initial value .
quote_number is the name of the counter (you can chose what you want but this has to be the same in the workflow and in the initialization of $sugar_config[‘SweeterCalc’][‘GlobalCounter’].
The field in the Quote module is the one you select in the workflow.