SugarCRM the Audit Table

What the heck is an audit table? When I first heard audit table, I, of course, thought of accounting, but in Sugar a better definition is a way to do a historical review of the changes that were made in a Sugar module. When you make any changes in a module form, and click save, Sugar updates the audit table.

Audit tables are module based. Each module may have it's own audit table, with the name of the table being "[the module name]_audit," for example, the bugs module's audit table is called "bugs_audit" and the accounts module audit table is called "accounts_audit". So every module may, or may not, have an audit table, I'll explain in a sec.

To track historical changes in Sugar, Sugar could have been designed, so that any time you make a change to a module, all the tables and data in a module are re-saved to historical tables, this would track every change in a module, and the date and time of the change. If we did this, and clicked save 10 times, we would have to save all the tables in the module along with all the data 10 times. As you might image, your database would grow exponentially, and be quickly overwhelmed with a mass of redundant data.

Fortunately, Sugar is not designed this way. The folks at Sugar reasoned that only certain key field changes in a module need to be tracked, not every field in the module.  This means the audit tables are set up differently, than other module tables.  Each row of the audit table tracks a particular field, and only makes an entry in the audit table when the designated field is changed. You might save the module 10 times, but if the audited fields are not changed, no entries will be made in the audit table.  This saves a lot of time and storage space without giving up the opportunity to track historical changes.

Given the above, what columns are needed in the audit table? Well, you want the id of the record your changing, the date and time of the change, who made the change, the name of the field (in case you're auditing more than one field in a module), the data_type of the field, the before value of the field (before the change) and the after value of the field (the new saved value). And that folks, is what you'll find in an audit table.

If I want to track when the status field of a module is changed. The audit table would enter a new row in the audit table only when you click save, and the status field was changed. Now, if you wanted to see, historically, the changes to the status field, you can track every change made to the status field over the life of the application. As a side benefit to auditing a field, you can create events based on a change to the status field, by checking the current value against the last audited value to determine if the field was changed, that can then be used to trigger an event to send out email notifications, for example.

If you look at a fresh install of Sugar, you'll discover that some modules don't have audit tables, for example, the calls, document, emails, forecasts, meetings, notes, tasks, and users modules do not have audit tables out of the box.  Some 3rd party plug-in modules, do not have audit tables, like KReporter. Some of your custom modules may not have audit tables.  Which begs the question, can we create an audit table, if one does not exist?  The quick answer is yes.

****************

Open up /modules/[Your Module]/vardefs.php.  I'll use the Documents module as an example.  So in /modules/Documents/vardefs.php at the top of the file add:

$dictionary['Document'] = array('table' => 'documents',
'unified_search' => true,
'full_text_search' => true,
'audited' => true,
'unified_search_default_enabled' => true,
'fields' => array(

 
Now, scan down to the field you want to audit and set it, like so:

'status_id' =>
array(
'name' => 'status_id',
'vname' => 'LBL_DOC_STATUS',
'audited' => true,
'type' => 'enum',
'len' => 100,
'options' => 'document_status_dom',
'reportable' => false,
),

Look at the other modules with audit tables for additional examples.  You can also use Studio, to designate fields to be audited.

After you make these changes, you'll need to do a "Quick Repair and Rebuild." This will auto-generate entries in a bunch of custom files in the module that allows Sugar to start auditing that module, and if you look in the database, you'll now see your new audit table.

****************

There's a catch though, there's always one! This will allow you to audit any fields in your modules table, or your _cstm custom field table for that module, however, it will NOT let you audit related fields, or fields that have a relationship with other modules.

The best way to do that is with a logic hook. A big thank you here to bsoremsugar for his March 4, 2013 article in Sugar forum.

Let's use an example, suppose we're setting up an audit table in the calls module to track calls by account. Calls has a relationship with the accounts module, and there's a field in the calls module, that is a related field, labeled "Account Name".  We create a before_save logic hook in the calls module. See my articles on creating logic hooks.

In the file for the logic hook, we do something like this:

<?php 
class auditCallsAccount
{     
   function auditCallsAccount($bean)
   {  
       // check for the change         
       $bean->load_relationship('accounts');
       if($bean->fetched_rel_row[‘account_name’] != $bean->account_name){
            // create an array to audit changes in the calls module’s audit table
            $auditEntry = array();
            $auditEntry[‘field_name’] = ‘account_name';
            $auditEntry[‘data_type’] = ‘relate';
            $auditEntry[‘before’] = $bean->fetched_rel_row[‘account_name’];
            $auditEntry[‘after’] = $bean->account_name;
            // save audit entry
            $bean->db->save_audit_records($bean, $auditEntry);
        }
    }
} 

The before_save hook will fire every time you save the module, the hook code will update the related fields, followed by Sugar updating the rest of the fields designated for the audit table.

Comments are closed.