Best way to generate a report for audit logs of a field on SugarCRM On-Demand

Hello,

This cannot currently be done in Sugar, but we do have an idea posted about this. You'll find a few options in the comments of that thread that may be options for you. For the second suggestion you made, that can definitely be done. You could do it using a logic hook to concatenate a TextArea or TextField with the status and date, conditional on the status changing. Alternatively, you could create a custom module that has "Status" and "Time Changed" fields, then use a logic hook to create a new record in that module when the Status changes. This would let you use the Reports module to run a report on the changes rather than having to do it in Excel.

If you have any questions or would like to see an example of either (or both) of the options I suggested, let me know.

-Alan

Thank you so much for the reply Alan and also for the link. After reading it, I think for now there is no good way of implementing this without any third party (paid) module since On-demand neither offers the Advanced Reporting tool nor does its 7.6 version work with KReporter.

This means, I'm left with the choice of time-stamping with the current status. You spoke of creating a new record to maintain history since it would save me the work of having to do the Excel work. Thanks a lot for that suggestion too. But as far as my limited understanding goes, I think Excel is still a smaller price to pay compared to the number of duplicated records I'd be able to avoid.

So could you please tell me how to implement this logic hook that would allow me to time-stamp it onto a custom or standard text field along with the status? Please try to be a little elaborate as I've no experience working with logic hooks. But I'll try to catch up on them ASAP.

Thanks again for the very comforting and helpful reply again.

Thank you so much for the reply Alan and also for the link. After reading it, I think for now there is no good way of implementing this without any third party (paid) module since On-demand neither offers the Advanced Reporting tool nor does its 7.6 version work with KReporter.

This means, I'm left with the choice of time-stamping with the current status. You spoke of creating a new record to maintain history since it would save me the work of having to do the Excel work. Thanks a lot for that suggestion too. But as far as my limited understanding goes, I think Excel is still a smaller price to pay compared to the number of duplicated records I'd be able to avoid.

So could you please tell me how to implement this logic hook that would allow me to time-stamp it onto a custom or standard text field along with the status? Please try to be a little elaborate as I've no experience working with logic hooks. But I'll try to catch up on them ASAP.

Thanks again for the very comforting and helpful reply again.

Sure thing! For this example, I will be using the Leads module, tracking changes to the Status field.

1. Create a custom TextArea in Admin > Studio. For reference, I named mine "status_log_c". I also added it to the Record View layout so I could verify the logic hook was working at the end.
2. Create or edit the file custom/modules/Leads/logic_hooks.php:
   

   <?php
   $hook_version = 1;
   $hook_array = Array();
   $hook_array['before_save'] = Array();
   $hook_array['before_save'][] = Array(1, 'Store values', 'custom/modules/Leads/LeadsLogicHooks.php', 'LeadsLogicHooks', 'before_save_method');
   

   
   If the file already exists and there is already another before_save logic hook, you'll just add line 5. If the file exists, but there is not a before_save logic hook yet, add lines 4 and 5.
   
   
3. Create the file custom/modules/Leads/LeadsLogicHooks.php:
   

   <?php 
   if (!defined('sugarEntry') || !sugarEntry) die('Not A Valid Entry Point');  
   class LeadsLogicHooks {
        function before_save_method($bean, $event, $arguments) {
            if($bean->fetched_row['status'] != $bean->status) {
                 $bean->status_log_c .= $bean->status . "," . date("Y-m-d H:i:s") . ";";
            }
        }
   }
   
   
4. Run the Admin > Repair > Quick Repair and Rebuild tool.

That should do it! This will concatenate the new status and current time stamp into a text area every time the status field changes. Obviously, this can be modified to fit your purposes as needed. If you want to use the old status, reference $bean->fetched_row['status']. You can modify the delimiters (comma and semicolon used in this example), or change the module and field names as needed. Let me know if you run into any issues getting this up and running!

-Alan