How to use a field from another table in list view

[thanateros]

OS: Linux, Fedora Core 4
SugarCRM Open Source Version 4.5.1b
Apache 2.0
PHP 5.0.4
MySQL: 4.1.20

I have experience with PHP, Object-Relational-Mapping in general (java, ruby ActiveRecord), and SugarCRM (administered a web server for my company serving SugarCRM for 2 years now) and have always found the answers to what I need in these forums, until now.

I received a request to include the 'industry' field from the 'accounts' table as a column in the Contacts module's list view. This has turned out to be more difficult that I thought.

After 1 week of scouring the Forums, Wiki, Web and reading what feels like hundreds of posts, I have to break down and make my first post to this forum.

I found several handfulls of the same type of question asked (a good majority of them never having any comments beyond the initial post) and found the few that had answers vaguely explained (the poster usually gives up settling for their own hack in the module code).

I don't want to settle for a hack; I also don't want my changes to be overwritten by upgrading, or having to manually merge changes with my edited pages.

I figure it should be trivial to include the needed field in such a way as to make it part of the list of fields in Studio for the Contacts module, ListView that show up as columns to choose from (or, more generically, as the list of fields on the left-hand side for the other 2 views).

Accounts table is linked to Contacts via a join table called 'accounts_contacts', so this won't be as simple as two directly-related tables. Formulating various join queries is also trivial in SQL; however I don't want to resort to a 'hack'; i'm sure there has to be a way to do this in the framework provided.

I became positive when I noticed the Contacts module vardefs.php had the following code:

         'account_name' =>
                array (
                        'name' => 'account_name',
                        'rname' => 'name',
                        'id_name' => 'account_id',
                        'vname' => 'LBL_ACCOUNT_NAME',
                        'join_name'=>'accounts',
                        'type' => 'relate',
                        'link' => 'accounts',
                        'table' => 'accounts',
                        'isnull' => 'true',
                        'module' => 'Accounts',
                        'dbType' => 'varchar',
                        'len' => '255',
                        'source' => 'non-db',
                        'unified_search' => true,
                ), 
  

The vardefs.php explanation on the Sugar Wiki site made all of this obvious, and it is formulated in the format that I figure my 'accounts_industry' variable will be, save I thought 'join_name' should point to the join table, not 'accounts', until I looked further and saw the following:

         'accounts' =>
                array (
                        'name' => 'accounts',
                        'type' => 'link',
                        'relationship' => 'accounts_contacts',
                        'link_type' => 'one',
                        'source' => 'non-db',
                        'vname' => 'LBL_ACCOUNT',
                ), 
  

This was still up in the fields declarations, not in the relationships declarations at the bottom as it seemed the vardefs.php explanation on the Wiki site led me to believe, but hey, I KNOW that the existing account_name declaration obviously works, so I figured I would copy the 'account_name' declaration, change 'account_name' to 'account_industry', change 'LBL_ACCOUNT_NAME' to 'LBL_ACCOUNT_INDUSTRY' (also making appropriate variable definition in the language file), and change 'rname'=>'name' to 'rname'=>'industry' so that it looked like this:

         'account_name' =>
                array (
                        'name' => 'account_name',
                        'rname' => 'name',
                        'id_name' => 'account_id',
                        'vname' => 'LBL_ACCOUNT_NAME',
                        'join_name'=>'accounts',
                        'type' => 'relate',
                        'link' => 'accounts',
                        'table' => 'accounts',
                        'isnull' => 'true',
                        'module' => 'Accounts',
                        'dbType' => 'varchar',
                        'len' => '255',
                        'source' => 'non-db',
                        'unified_search' => true,
                ),
        'account_industry' =>
                array (
                        'name' => 'account_industry',
                        'rname' => 'industry',
                        'id_name' => 'account_id',
                        'vname' => 'LBL_ACCOUNT_INDUSTRY',
                        'join_name'=>'accounts',
                        'type' => 'relate',
                        'link' => 'accounts',
                        'table' => 'accounts',
                        'isnull' => 'true',
                        'module' => 'Accounts',
                        'dbType' => 'varchar',
                        'len' => '255',
                        'source' => 'non-db',
                        'unified_search' => true,
                ), 
  

With that, I figured my vardef.php fiddling was done.
Next, I moved on to Contact.php and added var $account_industry in the "// These are for related fields" section.

Next, I repaired relationships (and, for good measure, restarted the webserver).

Now, In Studio, I can see my field listed on the left-hand side in Edit- and DetailViews (and if I add it to either view the label shows up fine but never populates with data and I know that contact is linked to an account that has its 'industry' field filled). It doesn't show up in ListView either.

Do I need to explicitly add a XTemplate or Smarty variable mapping somewhere, or is my prior code and/or understanding faulty in some way?

Any help is greatly appreciated.

[kpit]

Check out the Developer wiki lots of info there about vardefs with examples. Check here. but if you want it to be upgrade safe you should add it to vardefs.ext.php for the module you want to define it. More information can be found in the vardefs here.

[andydreisch]

thanateros, it sounds like you're on the right track. I've asked the Dev-ers to chime in.

I'm curious though what the business requirement is that results in the need for Industry to be exposed at the Contact level. Just curious.

Thanks,

Andy

[thanateros]

Kpit, andydreisch :

Thanks for your help.

Kpit:: thanks, I didn't know about the vardefs.ext.php. As for the vardefs explanation, the link you provided is the document I was referencing. I will scour the developer documentation with a finer-toothed comb and see if I catch anything I missed. What I was really wanting to know, though, was if what I changed looked ok, and, if no one notices any problems or grand conceptual errors, then why it doesn't seem to be working. If anyone gets the time, that is; I know we are all busy with our projects.

andydreisch: thanks for putting the call out; I appreciate it. As for why the the industry field in the contacts list view -- I have no idea. When the higher-ups in the sales department get back from vacation, I will ask them. I'm not in sales; I actually design microwave SCADA communications solutions for Utilities and Public Infrastructure type stuff; they found out I can also do all of this (web server admin, web site design, programming for the web, database administration, etc.. I used to be a Systems Administrator for BellSouth) so they asked me to set up and administer Sugar for them. And I was doing good till that request I posted about started kicking my ass

Thanks again, I'll keep looking on the developer wiki. If I find out where I made a wrong turn and get it working, I will post my solution here and to the developer wiki.

Everyone have a Happy New Year!

[ruchida]

Hi

Since you need to retrieve an industry data that is not in the contacts table, you need to manually fill it out by modifying create_list_query() and/or get_list_view_data(). These two functions fill out additional related fields and format them properly.

[pblag]

Hi All!

I developed functionality like this many times. It's simple.
You did well but you missed few things.
For populating data you have to add this field to

var $additional_column_fields = Array('bug_id', 'assigned_user_name', 'account_name','interest_areas_c','account_type',' interest_areas_c', 'account_id', 'opportunity_id', 'case_id', 'task_id', 'note_id', 'meeting_id', 'call_id', 'email_id'

and sure you have to update mysql query
for example
if you need field from table contacts_cstm you have to write code like this
$query .= " LEFT JOIN contacts_cstm ON contacts.id=contacts_cstm.id ";

And main thing you have to add your field to select fields range

just update function create_list_query

$query = "SELECT ";
$query .= " $this->table_name.*,
accounts.name as account_name,
accounts.industry as industry (this code will be ok in your case)

In the ListView.html you have to write "CONTACT.INDUSTRY"

Sure you have to create record in vardefs.php file for your field.

if you can't to do it yet you can email me and i'll do it for you.

Thank you,
Petro Blagodir.
blagodir@gmail.com

[thanateros]

Happy New Year everyone!

I trust all hangovers have been recovered from

Ruchida, pblag :: Thank you, those sound like solid leads and I will implement them as soon as I can. I will get back this thread and let everyone know what helped me when I get a chance to get back to modifying sugar.

Thanks again!!!!!

thanateros

[chuangk]

Hi All!

I developed functionality like this many times. It's simple.
You did well but you missed few things.
For populating data you have to add this field to

var $additional_column_fields = Array('bug_id', 'assigned_user_name', 'account_name','interest_areas_c','account_type',' interest_areas_c', 'account_id', 'opportunity_id', 'case_id', 'task_id', 'note_id', 'meeting_id', 'call_id', 'email_id'

and sure you have to update mysql query
for example
if you need field from table contacts_cstm you have to write code like this
$query .= " LEFT JOIN contacts_cstm ON contacts.id=contacts_cstm.id ";

And main thing you have to add your field to select fields range

just update function create_list_query

$query = "SELECT ";
$query .= " $this->table_name.*,
accounts.name as account_name,
accounts.industry as industry (this code will be ok in your case)

In the ListView.html you have to write "CONTACT.INDUSTRY"

Sure you have to create record in vardefs.php file for your field.

if you can't to do it yet you can email me and i'll do it for you.

Thank you,
Petro Blagodir.
blagodir@gmail.com

Kudos to Petro at Blagodir Software for giving me the code snippets to do the same in 5.0 Beta1. Thank you!

[tpine]

If you're using 4.5.1*, try overriding the create_new_list_query() in your Contacts/Contact.php. Be careful about the $filter argument for SubPanelLlistViews. Thanks.

[krakoss2]

And for sugar 5.0.0b where is i must to modify the query in the custom module.

Thanks