adding two account fields to contact search

[elo25]

Hello,

I need to search a contact by account type and industry.
So I added those fields to vardefs.php, then I added them to SearchFields.php, searchdefs.php, Contacts.php and even to field_arrays.php (In Contact's directory).

If I go to the Contact's search, I can see the new fields there, but if i insert something to these fields, the search returns no values. So i changed ListViewData.php in include\ListView directory, I added echo $main_query; - so now i can see the query and I can see the query is incorrect.
It looks like this:

SELECT contacts.id , CONCAT(IFNULL(contacts.first_name,''),' ',IFNULL(contacts.last_name,'')) as name, contacts.first_name , contacts.last_name , contacts.salutation , accounts.name account_name, jtl0.account_id account_id, contacts.title , contacts.phone_work , jt1.user_name assigned_user_name , jt1.created_by assigned_user_name_owner , 'Users' assigned_user_name_mod ,

accounts.account_type account_type, jtl2.account_id account_id ,
accounts.industry industry, jtl3.account_id account_id,

contacts.assigned_user_id

FROM contacts

LEFT JOIN accounts_contacts jtl0 ON contacts.id=jtl0.contact_id AND jtl0.deleted=0 LEFT JOIN accounts accounts ON accounts.id=jtl0.account_id AND accounts.deleted=0 AND accounts.deleted=0 LEFT JOIN users jt1 ON jt1.id= contacts.assigned_user_id AND jt1.deleted=0 AND jt1.deleted=0 where ((accounts.account_type like 'Gov%' ) AND ( accounts.industry like 'Fin%')) AND contacts.deleted=0 ORDER BY name ASC


So I can see that this query is incorrect, but I just can't figure out, what I did wrong. Could someone help me?

[Jax.Smith]

the jtl0 attached to the each query is causing the problem. I had something similar to jtl0 that cause me problem with query. you need join_name => 'contacts' in vardefs. Check this post.
http://www.sugarcrm.com/forums/showt...664#post178664

[elo25]

the jtl0 attached to the each query is causing the problem. I had something similar to jtl0 that cause me problem with query. you need join_name => 'contacts' in vardefs. Check this post.
http://www.sugarcrm.com/forums/showt...664#post178664

Thanks for your response!

I did what you suggested, I added join_name => 'accounts';(I added accounts) to vardefs.php.

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

and I added it here:

 'account_id' =>
        array (
            'name' => 'account_id',
            'rname' => 'id',
            'id_name' => 'account_id',
            'vname' => 'LBL_ACCOUNT_ID',
            'join_name'=>'accounts',
            'type' => 'relate',
            'table' => 'accounts',
            'isnull' => 'true',
            'module' => 'Accounts',
            'dbType' => 'id',
            'reportable'=>false,
            'source' => 'non-db',
            'massupdate' => false,
            'duplicate_merge'=> 'disabled',
            'hideacl'=>true,
        ), 
  

But the query looks like this:

SELECT contacts.id , CONCAT(IFNULL(contacts.first_name,''),' ',IFNULL(contacts.last_name,'')) as name, contacts.first_name , contacts.last_name , contacts.salutation ,

accounts.name account_name, jtl0.account_id account_id,

contacts.title , contacts.phone_work , jt1.user_name assigned_user_name , jt1.created_by assigned_user_name_owner , 'Users' assigned_user_name_mod ,

accounts.account_type account_type, jtl2.account_id account_id ,
accounts.industry industry, jtl3.account_id account_id,

contacts.assigned_user_id
FROM contacts
LEFT JOIN accounts_contacts jtl0 ON contacts.id=jtl0.contact_id AND jtl0.deleted=0
LEFT JOIN accounts accounts ON accounts.id=jtl0.account_id AND accounts.deleted=0 AND accounts.deleted=0
LEFT JOIN users jt1 ON jt1.id= contacts.assigned_user_id AND jt1.deleted=0 AND jt1.deleted=0 where ((accounts.account_type like 'Gov%' ) AND ( accounts.industry like 'Fin%')) AND contacts.deleted=0
ORDER BY name ASC.

As you can see, this query consist 3 account_id's:
1) jtl0.account_id account_id
2) jtl2.account_id account_id
3) jtl3.account_id account_id

Where are these coming from? It seems like those are account_id's from accounts_contacts, but why are they there and how could I get rid of them? Does anybody have a solution to it? Or have I done something wrong...

[elo25]

Oh, I finally did it! I mean now I can search contacts and filter contacts with account type and industry.
What I did>
I created a custom controller for Contacts module and inside this controller I implemented the function action_view and did like is told here>
http://www.sugarcrm.com/forums/showthread.php?t=43503

[big_al]

elo25, could you share your complete solution? Been trying to follow your instructions but can't get it working. Thanks!