Accessing Audit tables in Reports #4011

New issue

Open

opened 2026-02-20 16:12:35 -05:00 by deekerman · 3 comments

deekerman commented

2026-02-20 16:12:35 -05:00

Owner

Originally created by @connorshea on GitHub (Sep 26, 2019).

Issue

I'd like to be able to use the information from audit tables in the Reports module. The idea would be that I'd be able to create a report, e.g. where the Lead 'was edited on November 2, 2018', or something like that. Or getting the time the status of the Lead changed to 'Sold'. Stuff like that.

Context

We do some dumb stuff and want to have the audit tables usable in some Reports. KReports is able to use the audit tables, but I'm not sure how exactly.

I've been trying to figure out how to add this functionality, and I'm pretty lost at this point with regards to how the audit tables work. The module has a 'polymorphic' relationship, I guess? But it's not really a module and it doesn't exist in the $beanFiles array (it's commented out, and has been for a long time).

I tried uncommenting that line to add Audit to the $beanFiles array, then adding public variables on the Audit class for each column in audit tables, and then defining vardefs for the fields. Now the audit fields show up in the Reports module tree, but the Change Log view fails with a database error, so that's great. I'm guessing implementing anything like this is going to be a huge pain.

Originally created by @connorshea on GitHub (Sep 26, 2019). #### Issue I'd like to be able to use the information from audit tables in the Reports module. The idea would be that I'd be able to create a report, e.g. where the Lead 'was edited on November 2, 2018', or something like that. Or getting the time the status of the Lead changed to 'Sold'. Stuff like that. #### Context We do some dumb stuff and want to have the audit tables usable in some Reports. KReports is able to use the audit tables, but I'm not sure how exactly. I've been trying to figure out how to add this functionality, and I'm pretty lost at this point with regards to how the audit tables work. The module has a 'polymorphic' relationship, I guess? But it's not _really_ a module and it doesn't exist in the `$beanFiles` array ([it's commented out](https://github.com/salesagility/SuiteCRM/blob/ef1b41965c056937894dd3018d3b149c9c25a9a9/include/modules.php#L201), and has been for a long time). I tried uncommenting that line to add Audit to the `$beanFiles` array, then adding public variables on the Audit class for each column in audit tables, and then defining vardefs for the fields. Now the audit fields show up in the Reports module tree, but the Change Log view fails with a database error, so that's great. I'm guessing implementing anything like this is going to be a huge pain.

deekerman added the

Type: Bug

Type:Question

labels

2026-02-20 16:12:35 -05:00

deekerman commented

2026-02-20 16:12:36 -05:00

Author

Owner

@connorshea commented on GitHub (Sep 26, 2019):

The vardefs.php looks like this, FWIW:

vardefs.php

<?php

if (!defined('sugarEntry') || !sugarEntry) {
    die('Not A Valid Entry Point');
}

$dictionary['Audit'] = array(
    'fields' => array(
        'parent_id' => array(
            'required' => true,
            'name' => 'parent_id',
            'type' => 'char',
            'length' => 36,
            'reportable' => true
        ),
        'id' => array(
            'required' => true,
            'name' => 'id',
            'type' => 'char',
            'len' => 36,
            'reportable' => true
        ),
        'field_name' => array(
            'required' => true,
            'name' => 'field_name',
            'type' => 'varchar',
            'len' => 100,
            'reportable' => true
        ),
        'date_created' => array(
            'required' => true,
            'name' => 'date_created',
            'type' => 'datetime',
            'reportable' => true
        ),
        'data_type' => array(
            'required' => true,
            'name' => 'data_type',
            'type' => 'varchar',
            'len' => 100,
            'reportable' => true
        ),
        'created_by' => array(
            'name' => 'created_by',
            'rname' => 'user_name',
            'id_name' => 'modified_user_id',
            'vname' => 'LBL_CREATED_BY',
            'type' => 'user_name',
            'table' => 'users',
            'isnull' => 'false',
            'dbType' => 'id'
        ),
        'before_value_text' => array(
            'name' => 'before_value_text',
            'type' => 'text',
            'reportable' => true
        ),
        'before_value_string' => array(
            'name' => 'before_value_string',
            'type' => 'varchar',
            'len' => 255,
            'reportable' => true
        ),
        'after_value_text' => array(
            'name' => 'after_value_text',
            'type' => 'text',
            'reportable' => true
        ),
        'after_value_string' => array(
            'name' => 'after_value_string',
            'type' => 'varchar',
            'len' => 255,
            'reportable' => true
        )
    )
);

@connorshea commented on GitHub (Sep 26, 2019): The `vardefs.php` looks like this, FWIW: <details> <summary>vardefs.php</summary> ```php <?php if (!defined('sugarEntry') || !sugarEntry) { die('Not A Valid Entry Point'); } $dictionary['Audit'] = array( 'fields' => array( 'parent_id' => array( 'required' => true, 'name' => 'parent_id', 'type' => 'char', 'length' => 36, 'reportable' => true ), 'id' => array( 'required' => true, 'name' => 'id', 'type' => 'char', 'len' => 36, 'reportable' => true ), 'field_name' => array( 'required' => true, 'name' => 'field_name', 'type' => 'varchar', 'len' => 100, 'reportable' => true ), 'date_created' => array( 'required' => true, 'name' => 'date_created', 'type' => 'datetime', 'reportable' => true ), 'data_type' => array( 'required' => true, 'name' => 'data_type', 'type' => 'varchar', 'len' => 100, 'reportable' => true ), 'created_by' => array( 'name' => 'created_by', 'rname' => 'user_name', 'id_name' => 'modified_user_id', 'vname' => 'LBL_CREATED_BY', 'type' => 'user_name', 'table' => 'users', 'isnull' => 'false', 'dbType' => 'id' ), 'before_value_text' => array( 'name' => 'before_value_text', 'type' => 'text', 'reportable' => true ), 'before_value_string' => array( 'name' => 'before_value_string', 'type' => 'varchar', 'len' => 255, 'reportable' => true ), 'after_value_text' => array( 'name' => 'after_value_text', 'type' => 'text', 'reportable' => true ), 'after_value_string' => array( 'name' => 'after_value_string', 'type' => 'varchar', 'len' => 255, 'reportable' => true ) ) ); ``` </details>

deekerman commented

2026-02-20 16:12:36 -05:00

Author

Owner

@Mac-Rae commented on GitHub (Sep 27, 2019):

Hey @connorshea, I believe you are able to update the vardefs in the target module to change from this

$dictionary['AOR_Report'] = array(
    'table'=>'aor_reports',
    'duplicate_merge'=>true,
    'fields'=>array(

To this (Added 'audited'=>true,)

$dictionary['AOR_Report'] = array(
    'table'=>'aor_reports',
    'audited'=>true,
    'duplicate_merge'=>true,
    'fields'=>array(

Afterwords running an R&R should make it create the table in the database

Audit table for AOP_Case_Updatess already exists. skipping...
Audit table for AOR_Reports already exists. skipping...
AOR_Field not Audit Enabled...

@Mac-Rae commented on GitHub (Sep 27, 2019): Hey @connorshea, I believe you are able to update the vardefs in the target module to change from this ```php $dictionary['AOR_Report'] = array( 'table'=>'aor_reports', 'duplicate_merge'=>true, 'fields'=>array( ``` To this (Added 'audited'=>true,) ```php $dictionary['AOR_Report'] = array( 'table'=>'aor_reports', 'audited'=>true, 'duplicate_merge'=>true, 'fields'=>array( ``` Afterwords running an R&R should make it create the table in the database ``` Audit table for AOP_Case_Updatess already exists. skipping... Audit table for AOR_Reports already exists. skipping... AOR_Field not Audit Enabled... ```

deekerman commented

2026-02-20 16:12:36 -05:00

Author

Owner

@connorshea commented on GitHub (Sep 27, 2019):

@Mac-Rae I think you misunderstood what I'm wanting to do :) I don't want the reports to have audit tables (I think they have them already anyway), I want to be able to make Reports that use the data from the audit tables.

@connorshea commented on GitHub (Sep 27, 2019): @Mac-Rae I think you misunderstood what I'm wanting to do :) I don't want the reports to have audit tables (I think they have them already anyway), I want to be able to make Reports that use the data from the audit tables.