I've been encountering an issue when working with SugarCRM 7.6 On Demand (aka SaaS instance) in making a custom field auto-increment on every update. Since I can't access the code directly to modify Sugar's internals to create an auto-incremented field in the Accounts module (which would involve modifying the custom/modules/.../Accounts/logic_hooks.php file), I have been trying to use Calculated Fields and Modules to try to resolve the problem.
In these examples I will define the auto-incremented field as "cust_number_c". I also have used the "after_save" and "before_save" functions for testing rather than an On Create function. After every modification I repaired & rebuilt the functions as well as saved the custom variable again (since there is an issue where occasionally custom variables don't accept changes without an additional save after committing the change).
Calculated Fields Method
While messing with Calculated Fields on the custom integer field, I realized I could only modify internal values, which would not work if I wanted to perform an
add(max(reference($accounts, "cust_number_c")),1)
against all Accounts objects.
Sugar Bean Module Method
I attempted to create a module using the SugarBean class to try to alter the Accounts module. The code for this is below:
<?php
if (!defined('sugarEntry') || !sugarEntry) die('Not A Valid Entry Point');
class Accounts_Save
{
function newAccount($bean, $event, $arguments)
{
//custom field cust_number_c is an auto-increment field in Opportunities
$start = $bean->cust_number_c;
if ($start != 0)
{ return; }
//The auto-increment seed/value in this example is stored/held in the Account module
// custom field cust_number_c, for a user with some IM Name
$opp = BeanFactory::getBean('Accounts');
//$where = "accounts.cust_number_c='some_id_name'";
//$opp_list = $opp->get_full_list("", $where);
$orderby = "accounts.cust_number_c DESC"
$opp_list = $opp->get_full_list($orderby);
if(! isset($opp_list))
{
$GLOBALS['log']->fatal("Customer Field Number not found");
}
else
{
$acc_lastindex=$opp_list[0];
$start =$acc_lastindex->cust_number_c;
$bean->cust_number_c = $start+1;
// auto-increment for next save
$acc_lastindex->cust_number_c = $start+1;
$acc_lastindex->save(FALSE);
}
}
}
?>
When executed the page would return an HTTP 500 error, which is most likely because the Accounts bean class doesn't have the custom variables associated with it.
Database Module Method
I then attempted to create a script to modify the database value for a given Account after save. I was able to get a copy of the cloud server exported to look at the DB internals to verify my queries would work. After seeding a base value in for an account, I attempted to use the following code to test the execution:
<?php
if(!defined('sugarEntry') || !sugarEntry) die('Not A Valid Entry Point');
/*
* add_code - Generic Autonumbering Hook
* ---------------------------------------------------------------
* The purpose of this hook is to generate generic codes for
* numbering entries that go into SugarCRM (e.g. new Leads, etc).
* This hook is intended to allow for greater flexibility and is
* fired after_save.
* ---------------------------------------------------------------
* Original Concept by Eli Lindner on SugarCRM.com Forums
* Revamp by Kris Tremblay - Silence IT
* August 10th, 2009
* Modified for internal use by Peter Hogan-De Paul for Akkadian Labs, LLC
* November 2, 2015
*/
class add_code
{
const CODE_PREFIX = ""; // Prefix (ie. LD represents the Leads prefix)
const CODE_SEPARATOR = ""; // Character(s) separating the prefix and the code
const MIN_CODE_LENGTH = 6; // e.g. 0001, 0002, etc; used to dictate padding
const DATE_FORMAT = "y"; // Date format string for part of the prefix (e.g. "y" = 09 in LD09-)
const CUSTOM_FIELD = "cust_number_c"; // Custom field to store the code in
const CUSTOM_TABLE = "accounts_cstm"; // Custom table where the custom field is located
function add_code($bean, $event, $arguments)
{
$db = DBManagerFactory::getInstance();
// Create complete prefix for the code (e.g. LD09-)
$prefix = "";
// Get the starting position for the SUBSTR call in the query
$prefix_len = strlen($prefix) + 1;
$query = "SELECT MAX(".self::CUSTOM_FIELD.") FROM ".self::CUSTOM_TABLE." WHERE (".self::CUSTOM_FIELD." <> '' OR ".self::CUSTOM_FIELD." IS NOT NULL) LIMIT 1;";
$result = $db->query($query, true);
$row = $db->fetchByAssoc($result);
// Increment the highest code by 1 and pad if necessary
$code = $row[self::CUSTOM_FIELD] + 1;
$code = str_pad($code, self::MIN_CODE_LENGTH, "0", STR_PAD_LEFT);
// Put it all together
$new_code = $prefix.$code;
// Update the record that was just saved
$update_query = "UPDATE ".self::CUSTOM_TABLE." SET ".self::CUSTOM_FIELD." = '$new_code'
WHERE id_c = '{$bean->id}' AND (".self::CUSTOM_FIELD." = '' or ".self::CUSTOM_FIELD." IS NULL)";
$db->query($update_query, true);
}
}
?>
This was able to execute without any errors, but did not product any result. Executing the DB queries against the test instance worked, but with no result.
This is the manifest file that was used for both of the Module methods, with the logic hook execution type being modified where necessary:
<?php
$manifest = array(
'acceptable_sugar_flavors' => array('ENT','ULT'),
'acceptable_sugar_versions' => array(
'exact_matches' => array(),
'regex_matches' => array('(.*?)\\.(.*?)\\.(.*?)$'),
),
'author' => 'Peter Hogan De Paul',
'description' => 'Provides autoincrement functionality to a custom field defined as cust_number_c in the Acccounts module',
'icon' => '',
'is_uninstallable' => true,
'name' => 'AutoIncrement Customer Number',
'published_date' => '2015-11-02 2015 12:00:00',
'type' => 'module',
'version' => '1446490552',
'key' => '12345',
'built_in_version' => '7.6.0.1',
'version' => '1.0',
'readme' => '',
'remove_tables' => 'prompt'
);
$installdefs = array(
'id' => 'custnumautoinc_00001',
'copy' => array(
0 => array(
'from' => '<basepath>/Files/custom/modules/Accounts/custnum_autoinc.php',
'to' => 'custom/modules/Accounts/custnum_autoinc.php',
),
),
'logic_hooks' => array(
array(
'module' => 'Accounts',
'hook' => 'before_save',
'order' => 99,
'description' => 'Autoincrements customer number',
'file' => 'custom/modules/Accounts/custnum_autoinc.php',
'class' => 'add_code',
'function' => 'add_code',
),
),
);
?>
Any suggestions for a better method or tweaks of the current attempts would be appreciated. Thanks in advance!
