Jun21

Written by:David Aldridge
6/21/2011 4:17 PM 

Sometimes it is necesarry to be able to search a related field in another modules data from the search field for a module.

To implement this you need to know the underlying database structure and edit two files in the module.

First you have to edit the metadata/searchdefs.php file to add an entry for the field that you wish to search.  You can add it to the basic search or advanced search section.  In my example here I wanted to add the ability to search line items in a quote:

array('name'=>'line_items', 'label'=>'Line Items', 'type'=>'name', 'default'=>true),

Then you need to edit the metadata/SearchFields.php file to add the query:

'line_items'=> array('query_type'=>'default', 'operator'=>'innerjoin','innerjoin'=>'INNER JOIN aos_products_quotes ON (aos_products_quotes.parent_id=aos_quotes.id AND aos_products_quotes.description LIKE', 'db_field'=>array('id',),),

There are two choices of operator here - innerjoin and subquery.  In almost all cases you would use innerjoin as mysql does not handle subqueries well.

Finally go into studio and open up either the basic or advanced search layout (which every you added the field to in searchdef.php), and then save and deploy.  Your cross module search should now be active.

Tags:

2 comment(s) so far...

Re: SugarCRM Cross Module Search Fields

I wonder if you can help me.

I have an custom field in the accounts module named category_c which is a multiselect.

I am trying to use your example to filter contacts using the category_c field from accounts.

Everything appears to work but the search returns nothing (with or without wildcards).

I guess it's something to do with my sql statement but I can't see what.

My entry in Contacts/metadata/SearchFields.php looks like this:

'category'=> array('query_type'=>'default', 'operator'=>'innerjoin','innerjoin'=>'INNER JOIN accounts_cstm ON accounts_contacts.account_id = accounts_cstm.id_c AND accounts_cstm.category_c Like ', 'db_field' => array('id',),),

Thanks in advance

By John Grove on  4/16/2012 1:11 PM

Re: SugarCRM Cross Module Search Fields

John, comparing your example to David's. It appears you are missing an open parenthesis ( after the ON. Not sure if that is causing your issues.
Of course you posted 2 months ago too. LOL

By Guest on  6/21/2012 8:02 AM

Your name:
Your email:
(Optional) Email used only to show Gravatar.
Your website:
Title:
Comment:
Security Code
Enter the code shown above in the box below
Add Comment  Cancel 
You must be logged in and have permission to create or edit a blog.