How to add a Autoincrement field for Account and Contacts

Did you try a Quick Repair and Rebuild and Rebuild Relationships.
Did you put field in the EditView? I typically only put the field i the Detail and List View since it is auto generated.

Not sure what else might be going on. Mine seems to be working.

Some kind of issue when you do not use a prefix.

First few records I had a prefix and it worked great.
Took away the prefix and started giving the same number.
Also. Prefix is stored somehow initially. When I tried to put the prefix back it still didnt’ work.
Went back to original AUTO prefix it added the increment again.

thanks for all the testing, well i encountered the same issue as well. i initially started off by removed the prefix and it didn’t work also when i changed it back it didn’t work either. So i tried the default and it worked and so i started experimenting around and it is like you described, once you choose the initial prefix you cannot modify it otherwise it simply won’t work - very weird.

Oh well i can live with this for the time being though i am really surprised this is not a part of the fundamentals in suitecrm or sugar for that matter.

Thanks for being such a great help.

another note is that if you remove the numbering and try to shrink it down to eg JOB-1 , JOB-2, it also does not work.

Glad to help.
Not idea, but as a work around to use a number, you could initially establish a prefix of a number 000

Will…

Is this still a workable solution in the newer versions of Suite? I am on the hosted plan… can I GET to those directories to create the necessary files?

Thanks!

David

You are a lifesaver!!! :cheer:

This was making me nuts and I seriously can’t see paying $400 for something that essentially does what you did.

David

I unzipped the file you dropped, but I can’t figure out where to drop it or how to incorporate it into my SuiteCRM.

Please kindly give step by step approach on how I’m to go about this. Thanks

1- Download the Module
2- Log in as Admin
3- As Admin go to the Admin dashboard
4- In the Admin Dashboard go to Developer Tools then Module Loader
5- In Module Loader use the Upload button and search for the DTG-Autoincrement you download (is a zip file)
6- Upload the module and then click install
7- Wait install finish
8-Go back to Admin Dashboard and go to System > Repair > Quick Repair and Rebuild (wait is done)
9- Then go to Developer Tools > Studio (and go to where you want to put a AutoIncrement)

For Sample lets go to Accounts

Studio > Accounts > Fields > Add Field
In the Dropbox called Data Type you will find AutoIncrement option select and is DONE!! :slight_smile: YEEIII

TIP inside Autoincrements option you will find a label call Fortmat this is the format our Autoincrement will have for sample

{TESTCO}{-}{y}{-}{0000}{-}{ACCOUNT} will be = TESTCO-Y-0001-ACCOUNT

is you want something like ACCOUNT-0001 you will have to setup like this {ACCOUNT}{}{}{}{0000}{}{}

4 Likes

Little addendum:
While setting up the new auto-increment field, the “startin number” refers only to the auto-increment-part of your field format.

e.g.

For the format definition {TESTCO}{-}{y}{-}{0000}{-}{ACCOUNT}
The “Starting Number” would be set to “0001” (or some other number with 4 digits)

For the format {321}{}{}{}{000000}{}{}
The “Starting number” would be set to “000001” (or some other number with 6 digits)

1 Like

I love this little addon. seems to work wonderfully.

I’m trying to figure out the easiest way to retroactively supply my account records with an external id that should work as an autoincrement number for new records.

I have roughly 10’000 account records.
I want to add an external ID field. I want all accounts to have a unique id and I want any newly created accounts to be fitted with a new unique ID by the auto-increment field module (let’s say 100’001 to 110’000 e.g.).
-> How do I best go about filling this new (custom) field with a unique keys retro-actively?
Once I have the fields filled, I’d have the field settings setup so that the autoincrement-field action starts with a higher number than the highest I retroactively assigned to any account, in this case, e.g. 110’001 or larger.

Hope that’s understandable.

I tried doing this via export/import, but was unsuccessful, both by explicitly writing into the auto-increment fields upon import, and by writing to some other field in the same module while not including the auto-increment field in the import (hoping to evoke a “bofore-safe” or “after-save” logic hook that might add the ID to all empty fields via the auto-increment module).

Do I have to do this via direct database query? I’m not experienced with this, was hoping it wouldn’t be necessary. Would appreciate any help!

thanks
John

update: Not working wonderfully after all. Seeing similar problems as reported before. Somehow the first format entered is saved in some obscure place and wrecks the auto-increment after any changes.
even when adding a new field with a different name I’m seeing the same behavior! Something is not working right.

Tried resetting permissions
Tried lots and lots of quick repairs and permissions repairs…

Guess I’ll have to make my own logic hook after all! (which I’m very inexperienced at doing). Bummer!

When uninstalling the module it asks whether to remove tables or not. Which tables?

Hey all,
As I can’t imagine this wouldn’t be something that many people are interested in, I figured I’d post my solution for adding an auto-increment field to any given module. The following examples apply to the accounts module. Here’s how I did it:

1. Add new field via studio
I added an integer field via studio called “public_id” (which is then called public_id_c by SuiteCRM after saving)

You’ll want to turn off inline-editing for this field!

2. add logic_hook.php (or other name) to /custom/Extension/modules/[module]/Ext/LogicHooks/

-> create folder “LogicHooks” if missing)

Here’s my code:


<?php

$hook_version = 1; 

$hook_array = Array(); 

$hook_array['before_save'] = Array();

$hook_array['before_save'][] = Array(1, 'Auto-increment für public ID Firma', 'custom/modules/Accounts/accounts_public_id_calc.php','Accounts_Public_ID','Autoincrement');

?>

3. Add autoincrement script to folder /custom/module/[module]/

I named mine “accounts_public_id_calc.php”. Important is that it matches what was define under step two above!

Here’s my script that gets fired each time I save an account record.


<?php

// 20.1.16 JD: calculate autoincrement public id field (accounts and contacts)
// 

if(!defined('sugarEntry') || !sugarEntry) die('Not A Valid Entry Point');


class Accounts_Public_ID 

{

function Autoincrement($bean, $event)

{
	
	$public_id_bean = $bean->public_id_c; 		// fetch public_id of this record (bean)
	
	if (!$public_id_bean) { 					// if public-id is empty (empty string), set a new one, else do nothing
		
		$start_number = 311000001; 				// if no public_id has been set in database for any account, use this lowest number
		
		// query database, find largest public_id set for any account
		$query =  "SELECT MAX(`public_id_c`) FROM accounts_cstm"; 
	    $results = $bean->db->query($query, true);
	    $row = $bean->db->fetchByAssoc($results);
	    $public_id_max = $row['MAX(`public_id_c`)'];
	    
	    
	    if ($public_id_max > 311999998) { 		// if ID numbers become too large set id to "0" (obvious that something is wrong!)
	    
	    	$next_id = 000000000;  				// "0" creates database failure for some reason!
	    	
	    	$GLOBALS['log']->error('All account-id numbers used up (311000001 to 311999999)! Please define new numbering scheme');	// suitecrm.log entry if numbers are used up.
	    
	    
		} else if ($public_id_max) { 			// if public_id has been set (not an empty string) for at least one record, set the public id for this record to the max number + 1
	    
	    	$next_id = $public_id_max + 1;
	    	
	    } else {
	    	
	    	$next_id = $start_number;			// if all public_ids (for all account records) are empty, restart at start-number
	    	
	    }
	    
	    $bean->public_id_c = $next_id;			// write next_id to public_id_c field
	    
	}

}

}

?>

Please let me know what I could do better! Total PHP newbie here… First draft really. Not a lot of checks and balances… Use at your own risk!

Also if anybody knows how to easily create an alert for the user (in this case, when the numbers run out for the public id field), please let me know!

Also, in case s.o. is interested, this was my workflow for retroactively adding my public_ids to my new accounts field in the order the accounts were originally entered. I.e. the public_id numbers reflect how old or new the account records are.

Suggestion before starting: You might want to run the database pruning job before doing this, so you’re not wasting your public-ids on deleted records! (see Admin/Schedulers).

1a. make a copy of table accounts to “accounts_temp”
1b. make a copy of table accounts_cstm to “accounts_backup”

  1. Add new column “public_id_c” in accounts_temp
    (in my case integer field with same definitions as the field I created using studio, which can be found in table accounts_cstm)

  2. run query to fill new ids ordered by date entered - using contacts_temp only:
    In my example the ids start counting upwards from 312000001 (you can change that to your desired starting number)

SELECT @i:=312000001;
UPDATE `accounts_temp` SET `public_id_c` = ( SELECT @i := @i + 1 ) ORDER BY `date_entered` ASC;
  1. run query to copy new ids from accounts_temp to accounts_cstm.

UPDATE accounts_cstm as t1 JOIN accounts_temp as t2 ON t1.id_c = t2.id
SET t1.public_id_c = t2.public_id_c;

  1. check to see whether it looks good. delete accounts_temp and accounts_cstm_backup when you feel confident it’s the way you like!
1 Like

Hello mates,

Before I apologize for posting anything on this subject so old but I prefer this to open a new topic to ask about the same …

Will, When is a module that I built from scratch … Where should I put this new .php file, which directory?

Thanks in advance for your reply.

I’m using the dtg_autoincrement addon and it works as long as you don’t change the initial format.

The problem is I can’t get autoincrement to work using auto created records via Workflow or using php code with the save() function

Hi Will, I am 7.6.4 and just tried this method to add simple autoincrement field to a custom module. The field gets the autoincement by property so it appears to take however the field itself in my module simply remains blank. Does your method still apply to 7.6.4? I have found some other versions using logic hooks but yours is much simpler but needs to work. Any thoughts appreciated.

Hi,

Wonder if this can display month as well? I want something like YYYYMM-0000. Will the following do? {}{}{YM}{}-{0000}{}{}

Thanks

After doing this (which is correctly adding the auto increment options in studio) I am unable to make any changes to the field in Studio, I just get a pop up saying an error has occurred.
Thoughts?