Help getting custom built user-related field to show properly in report

[brendanwalsh]

I have custom written a drop down that references the user table using a similar route to that shown in http://www.sugarcrm.com/wiki/index.p...users_dropdown

This leads to there being the id of a user sitting on the relevant _cstm table. When reporting on this field the uuid is brought back (as you would expect but not useful).

Is there any route that anyone knows to get at the report code in order to either ensure it runs a "get_user" type function to interpret that field prior to reporting it, or simply to allow me to alter the SQL based on that field being in place.

I actually have a similar requirement on a custom built drop-down that references contact entries.

This is all running on Sugar 4.5.1.

Thanks

Brendan

[jli]

in order to have any related fields be reportable, you have to ensure that there are true Sugar relationships set up properly.
this way, when you want to create your report, you would add the related modules in the first step of your report building process.

you should be able to use the assigned to and team fields as examples in creating these types of relationships.

[brendanwalsh]

Thanks - if you looked at the wiki item that I referred to, what would be the major difference you are recommending (I don't want to create a full blown link with databases .... the assigned to is exactly the type of field I am looking to reflect).

I'm happy to start digging if you would point me at the place to start the hunt.

Cheers

Brendan

[jli]

i believe starting in the 4.5 version, teams and assigned to became popup select fields instead of drop downs.
if you start with any module with assigned to (ie. Accounts), you'll see the assigned_user_link entry in the vardefs.php file. this entry ties the assigned_user_id vardef entry to the accounts_assigned_user relationship (towards the end of the vardefs file). this relationship is what allows you to add the assigned user related module to reporting, when creating a custom report.

if you follow this, you should be able to create other similar relationships to the user table.

[brendanwalsh]

Thanks for your continued help. I got back to this today and managed to add entries to the

custom/modules/Opportunities/Ext/Vardefs/vardefs.ext.php file . The entries are below:

$dictionary['Opportunity']['fields']['referred_by_c'] =

array (
'name' => 'referred_by_c',
'rname' => 'name',
'id_name' => 'account_id',
'vname' => 'Referred by',
'type' => 'relate',
'table' => 'accounts',
'isnull' => 'true',
'dbType' => 'id',
'reportable'=>true,
'audited'=>true,
'comment' => 'Referred to account',
'duplicate_merge'=>'disabled'

);


$dictionary['Opportunity']['fields']['referred_by_link'] =
array (
'name' => 'referred_by_link',
'type' => 'link',
'relationship' => 'opportunities_referred_by_c',
'vname' => 'Referred by',
'link_type' => 'one',
'module'=>'Accounts',
'bean_name'=>'Account',
'source'=>'non-db',
'duplicate_merge'=>'enabled',
'rname' => 'name',
'id_name' => 'referred_by_c',
'table' => 'Accounts',
);

$dictionary['Opportunity']['relationships']['opportunities_referred_by_c']=array
('lhs_module'=> 'Accounts', 'lhs_table'=> 'accounts', 'lhs_key' => 'id',
'rhs_module'=> 'Opportunities', 'rhs_table'=> 'opportunities_cstm', 'rhs_key' => 'referred_by_c',
'relationship_type'=>'one-to-many')

This brought up my entry in the 'related' list when running reports. Better still I now see the accounts fields to link to. The problem is when I run the query it is not using the opportunities_cstm table to make the join and so it is failing ... here is the SQL...

SELECT opportunities.id primaryid ,opportunities.name opportunities_name ,l0.id l0_id ,l0.name l0_name FROM opportunities INNER JOIN accounts l0 ON l0.id= opportunities.referred_by_c AND l0.deleted=0 WHERE opportunities.deleted=0 LIMIT 0,100::MySQL error 1054: Unknown column 'opportunities.referred_by_c' in 'on clause'

I'm nearly there - I just need to force it to use opportunities_cstm and id_c to make the join.

Any ideas?

Brendan

[jli]

i would just manually add the column to the main opportunities table.
relationships aren't typically a part of the custom tables, simply because you can't add custom relationships via the wizard.