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

Hello everyone,

I've tried finding out a simple way to generate a report based on tracking the dates when a particular drop-down field was changed from one value to the next. My initial research has concluded that there isn't a way to do this.

So my questions are:

1. Is it really not possible without the purchase of 3rd Party SugarCRM modules (like Sugar Outfitters)?

2. If it isn't, is it at least possible to timestamp a text field (with the status) every time the status is changed? I can later extract that and create my report using some simple Excel manipulations.

Thanks a lot.

  • 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?

  • You can still do this in On-Demand, but you'll need to package it up in a zip file with a manifest, then upload and install it in Module Loader. Here are steps to accomplish this:

    1. Create a new folder that will contain all of your files for the package.
    2. Within this new folder, create the folder structure "custom/modules/Leads/" and put LeadsLogicHooks.php inside the "Leads" folder.
    3. Within the new folder, create manifest.php at the root (the same level as the "custom" folder) and fill it with the following contents:
      <?php
      $manifest = array (
        'name' => 'Track Lead Status',
        'description' => 'Append Lead status and timestamp to status_log_c when status changes.',
        'author' => 'abeam', // Your name
        'version' => '1.0.0',
        'is_uninstallable' => true,
        'published_date' => '2015-11-10 00:00:00',
        'type' => 'module',
        'acceptable_sugar_versions' => array(
             'exact_matches' => array(),
             'regex_matches' => array(
            '[6-7]\\.[0-9]\\.[0-9][0-9]?$',
            '[6-7]\\.[0-9]\\.[0-9]\\.[0-9][0-9]?$',
          ),
        ),
        'acceptable_sugar_flavors' => array('CE', 'PRO', 'CORP', 'ENT', 'ULT'),
        'readme' => '',
        'icon' => '',
        'remove_tables' => '',
      );
      
      $installdefs = array(
        'id' => 'abeam_20151110', // Unique string that will not change even if you update the package later
        'copy' => array(
          0 => array(
            'from' => '<basepath>/custom/modules/Leads/LeadsLogicHooks.php',
            'to' => 'custom/modules/Leads/LeadsLogicHooks.php',
          ),
        ),
        'logic_hooks' => array(
          array(
            'module' => 'Leads',
            'hook' => 'before_save',
            'order' => 99,
            'description' => 'Track Lead Status',
            'file' => 'custom/modules/Leads/LeadsLogicHooks.php',
            'class' => 'LeadsLogicHooks',
            'function' => 'before_save_method',
          ),
        ),
      );
    4. Create a zip file of the "custom" folder and "manifest.php".
    5. In your On-Demand instance, go to Admin > Module Loader, upload the zip file, then click the Install button.

    You may need to run Admin > Repair > Quick Repair and Rebuild after this process completes. Do you have any other questions I can help with?

    -Alan

  • Wow. The depth of each of your replies amazes me. I'll try it and let you know.

    There's just one line that bothers me a bit. It's in the manifest.php the following line (can you tell me what this does?):

    'remove_tables' => '',

  • I'm glad it was helpful! The remove_tables config option is to determine "Whether or not to remove tables generated from an installed module... Possible values are empty or 'prompt'." If you leave it empty, any tables created by the package will be removed on uninstall. If you set the value to "prompt", the user will be prompted for whether or not to remove the tables.

    Of course, in this case, there are no tables created, so it doesn't matter what value you put there.

    -Alan

  • Oh okay. Thanks a lot for the clarification. If I want to use the existing status description field to log in the status changes. Will I just have to replace the status_log_c  with my field name i.e. "status_description" right?

  • Correct! It may have a "_c" on the end if it was created in Studio. You can find the field name in Studio > [Module] > Fields.