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.

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

Thanks a lot for the detailed steps. I'm afraid, however, that I still can't figure out how to do it (I warned you I'm pretty new to this thing) primarily because I think the aforementioned method applies to an on-site SugarCRM as opposed to the on-demand that we have.

As a On-demand user, I have to go along with web logic hooks. The parameters I must enter have been explained in some detail on this page by SugarCRM but still I fail to understand how your instructions apply exactly to my case :/

Could I do the same thing, may be, with Workflows?

Can you double-check that the folders and filename listed matches what is in your zip file? (Namely: custom/modules/Leads/LeadsLogicHooks.php) It could be a copy-paste error if you didn't name your file exactly the same as I named mine.

Thanks a lot it works!!! The help you lent was just unbelievably accurate!

Hi Alan, ist there a way to do this in Sugar CE 6.5 as well? I tried with same coding, but its not working .. nothing happens & no informations in Status_log field. I need to get a report about change state in Leadstate - for example, when does the leadstate changed from "new" to "in Process". I can see in audit log of the Lead, but a need a report about every lead of the month esp.

Thanks for your help.

 Alan Beam

Hi! It's been a while, but I glanced over this and I don't see anything that jumps out to me as incompatible with CE. You'll want to make sure that the acceptable_sugar_versions regex will match with your version of Sugar. You might check your server logs, as well, in case a PHP error is being thrown during the upload/install and it isn't being relayed to the screen. Let me know how these suggestions go!

-Alan

Thanks alan, i ment your example with the logic hook here. i don´t have a record view layout? How can i check if this works?

"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.

1. Create or edit the file custom/modules/Leads/logic_hooks.php:
   
   1. <?php  
   2. $hook_version = 1;  
   3. $hook_array = Array();  
   4. $hook_array['before_save'] = Array();  
   5. $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.
   
   
2. Create the file custom/modules/Leads/LeadsLogicHooks.php:
   
   1. <?php   
   2. if (!defined('sugarEntry') || !sugarEntry) die('Not A Valid Entry Point');    
   3. class LeadsLogicHooks {  
   4.      function before_save_method($bean, $event, $arguments) {  
   5.          if($bean->fetched_row['status'] != $bean->status) {  
   6.               $bean->status_log_c .= $bean->status . "," . date("Y-m-d H:i:s") . ";";  
   7.          }  
   8.      }  
   9. }  
3. 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!"

Ah, gotcha. In Sugar 7, the Edit and Detail Views are combined into a single Record View. This should still work in CE, you would just need to add the above field into the Edit and Detail Views. Let me know if you have any other questions!

-Alan

Hi alan,

i did exactky what you wrote. I put the field "status_log" into the Edit & Detail View, but when i change the state nothing happens, the field is still empty?

do you have any idea?

Thanks

daniela

Hi Daniela!

We are holding a webinar for Sugar CE users and I thought that you might be interested. During the webinar, we’re going to discuss SugarCRM peculiarities, the risks associated with its usage and the ways to mitigate those risks, and also the secrets of choosing the right CRM for your company. The webinar is based on many years of our practical experience in SugarCRM development and support. I hope to see you among our attendees!

Hi Daniela!

We are holding a webinar for Sugar CE users and I thought that you might be interested. During the webinar, we’re going to discuss SugarCRM peculiarities, the risks associated with its usage and the ways to mitigate those risks, and also the secrets of choosing the right CRM for your company. The webinar is based on many years of our practical experience in SugarCRM development and support. I hope to see you among our attendees!

Alan Beam

Hi Alan

Happy New Year to you !!!

I am amazed with your in-depth knowledge of Sugar CRM.

So, was hoping if you could help me out. The issue here is :

I have a team of 15 people working on Sugar on-demand Enterprise (6.7.13) for handling Cases.

So, is it possible to extract their daily wok log at the end of the day from sugar, I have tried a lot but unable to do. So, if possible, please help me with that.

What I am looking for :

A work log which can be exported to Excel and it should contain all the details of each ticket handled by each user.

Please let me know if it is possible ??

Thanks for looking.

Regards

Bhavya Bhushan

Alan Beam

Hi Alan

Happy New Year to you !!!

I am amazed with your in-depth knowledge of Sugar CRM.

So, was hoping if you could help me out. The issue here is :

I have a team of 15 people working on Sugar on-demand Enterprise (6.7.13) for handling Cases.

So, is it possible to extract their daily wok log at the end of the day from sugar, I have tried a lot but unable to do. So, if possible, please help me with that.

What I am looking for :

A work log which can be exported to Excel and it should contain all the details of each ticket handled by each user.

Please let me know if it is possible ??

Thanks for looking.

Regards

Bhavya Bhushan

I created a package you can install that should be able to help you accomplish this. You can download the zip file from the Github Repository. Here's the gist of what I did:

• Created a custom module with a "Log Date" field and Many-To-One relationships to Cases and Users.
• Added a logic hook on the Cases module to see if a Log record already exists for the current day, user, and case.
• If it doesn't, create a new log entry.

You should be able to install this and then, after some data has populated, run a report on the Cases Log module to get you the data you need. (Note that you can also export Report results in CSV format and open that file in Excel.)

I didn't test this thoroughly, so feel free to let me know if you run into any issues or if you have any questions!

-Alan