AnsweredAssumed Answered

How to get a Joined table field in Subpanel

Question asked by Colin Colin on Mar 13, 2014
Latest reply on Mar 13, 2014 by Colin Colin
I'm trying to retrieve data from the accounts audit table to display in a subpanel using Sugar Pro 6.5.

Background:
Occasionally staff leave the business and when a user is deleted the records can be reassigned - nothing new here. However it is useful to see who an account was previously run by - this information is stored in the accounts_audit table, specifically the before_value_string field. The challenge is that the assigned_user_id links to a non existant record in the users table!

The Working Bits:
I created a new module to store past (and current) users. There's a logic hook which populates this table with any new users added to the users table.  This is all good and works fine. I further created a subpanel to show these old users as previous assignees of an account - again this works fine.

The Problem:
I can only show fields in the subpanel belonging to the module i created, despite having used a custom query function in the layoutdefs! I've checked many forum posts here as well as different blogs and followed many tutorials all to no avail! Though i could have been looking in the wrong places too.

I've got this file at custom/Extension/modules/Accounts/Ext/Layoutdefs/UserHistorySubpanel.php

$layout_defs['Accounts']['subpanel_setup']['past_users'] =
        array('order' => 49,
            'module' => 'usr_UserHistory',
            'subpanel_name' => 'ForAccounts',
            'get_subpanel_data' => 'function:get_users',
            'generate_select' => true,
            'title_key' => 'Previous Owners',
            'top_buttons' => array(),
            'function_parameters' => array(
                'import_function_file' => 'custom/modules/Accounts/customUserHistorySubpanel.php',
                'return_as_array' => 'true'
            ),
);

And the function housed at custom/modules/Accounts/customUserHistorySubpanel.php

function get_users($params) {
   $accountId = $args[0]['account_id'];
   $bean = $GLOBALS['app']->controller->bean;
   $return_array['select'] = " SELECT accounts_audit.date_created AS owner_change_date  ,accounts_audit.before_value_string,accounts_audit.after_value_string,usr_userhistory.username";
    $return_array['from'] = " usr_userhistory ";
    $return_array['join'] = " LEFT JOIN accounts_audit ON (accounts_audit.before_value_string=usr_userhistory.parentid)";
    $return_array['where'] = " WHERE accounts_audit.field_name = 'assigned_user_id' AND accounts_audit.parent_id='".$bean->id."'";
    $return_array['join_tables'] = '';
    return $return_array;
}

I've tried adding the audit table fields into the layoutdefs fields in "ForAccounts" thinking the SQL would populate the data but it appears not to do this.

There's clearly something obvious i've missed, so any help is much appreciated!

Outcomes