SugarCRM provides an ideal platform for centralising name and address details for full CRM processes but also to remove duplication by presenting details to other applications. I work on a project that supports 3 applications that use name/address details and linking them all works really well.
An issue was that when merging records like Accounts in Sugar that there was no complete record kept of what accounts were merged only what account was saved.
Sugar CE (Community Edition aka Open Source) includes a Trackers module that does track some transactions within Sugar for users / sessions but not all.
One of the main shortcomings is that it does not track deletions of accounts. Noting that Sugar does not actually delete but disables via a deleted flag.
So the first task that I addressed was the tracking of what accounts were deleted.
The changes for this are all in the file /data/sugarbean.php starting at line 4264 or there-abouts:
4264 $query = "UPDATE $this->table_name set deleted=1 , date_modified = '$date_modified' where id='$id'";
4265 $this->db->query($query, true,"Error marking record deleted: ");
4266 $this->mark_relationships_deleted($id);
4267
4268 // Take the item off the recently viewed lists
Changing the code to include both a log entry for deletions and the tracker entry.
4264 $query = "UPDATE $this->table_name set deleted=1 , date_modified = '$date_modified' where id='$id'";
4265 # thowden 20090518: Added for logging of deletions
4266 $GLOBALS['log']->info('Deletion ' . $id . ' query : ' . $query);
4267 # end thowden
4268 $this->db->query($query, true,"Error marking record deleted: ");
4269 $this->mark_relationships_deleted($id);
4270 # thowden 20090518 added to tracker for deletions
4271 $this->track_view($current_user->id, $this->module_dir, 'deleted');
4272 # end thowden
4273 // Take the item off the recently viewed lists
The log entry could be changed to only debug, error, or warning level, but I figure that 'delete' will be one of those things we will constantly be chasing for our beloved end-users.
Hand-in-hand with deletion is the concept of restoration and for good measure this can also be tracked.
Further down the file: noting that line numbers shown here vary as they are already changed compared to the original file due to the inclusion of the code above.
4289 $query = "UPDATE $this->table_name set deleted=0 , date_modified = '$date_modified' where id='$id'";
4290 $this->db->query($query, true,"Error marking record undeleted: ");
4291
4292 // call the custom business logic
Changes to:
4295 # thowden 20090518: Added for logging of restorations
4296 $GLOBALS['log']->info('Restoring ' . $name . ' ' . $value . ' query : ' . $query);
4297 # end thowden
4298 $this->db->query($query, true,"Error marking record undeleted: ");
4299 # thowden 20090518 added to tracker for restores
4300 $this->track_view($current_user->id, $this->module_dir, 'restored');
4301 # end thowden
Now you can review in both the sugarcrm.log or whatever you call your log file and in the Tracker table in your SugarCRM database.
For the merge process details to be really useful we wanted to add a trigger in the database to run some scripts. In order for the trigger to function it needs to have a unique record that it 'knows' means that the merge transactions have completed.
So I added a new entry to the Tracker database. File to modify is
modules\MergeRecords\SaveMerge.php
Find line 118 in the file:
118 $GLOBALS['log']->debug("Merged record with id of ".$return_id);
... and add the following
118 $GLOBALS['log']->debug("Merged record with id of ".$return_id);
119 # thowden 20090518 added to tracker for mergecomplete
120 $mergesource->track_view($current_user->id, 'MergeRecords', 'mergecomplete');
121 # end thowden
This completes a merged accounts transaction set for any number of merged accounts with a mergecomplete transaction. The database trigger is linked to that entry and recognises all the transactions by the monitor_id column value which is unique for a transaction set.
To query the Tracker table try using something like the following and modify the date value to something more appropriate.
select * from tracker where convert(char(10),date_modified,112)>='20090520'
This supports merging of Accounts and Contacts in SugarCRM CE 5.2.0d and possibly other 5.x.x variants.
