HOWTO: Add a search field that searches another module

bsoremsugar —  April 19, 2011 — 34 Comments

I came across this interesting issue in the forums the other, where the poster was trying to add a search field that would search the for Calls based upon the associated Account’s billing address postal code. This is a pretty straightforward upgrade-safe customization that should work fine on most recent versions of Sugar ( although I’ve only tested it on the latest 6.2.0beta ).

There are two parts to this customization. The first part requires us to add the search field definition in the custom/modules/Calls/metadata/SearchFields.php.

The key here is the subquery we’ve defined, which looks for all the accounts with the matching postalcode given in the search input. The actual postal code we are searching for is appended to the query specified with an ‘%’ appended to the search term. In the end, the where clause addition that gets generated looks something like this if we were searching for postal code 40323:

Now we just need to add this to the search form. We can do this by copying the modules/Calls/metadata/searchdefs.php file to the custom/modules/Calls/metadata/searchdefs.php and adding an entry like this to under either the ‘basic_search’ or ‘advanced_search’ array keys in whichever position you wish to have the input field listed.

Now the search field will be listed in the search form and be available for all your users to use.

34 responses to HOWTO: Add a search field that searches another module

  1. 

    This is exactly what I was looking for, but it does not appear to work for relate fields.

  2. 

    Thanks — I have a couple of questions …

     I updated the SearchFields.php and searchdefs.php  appropriately – I think. 

     Now I have a new form field on the Advance Search in the Opportunities module.  Nothing is happening though.

     My goal: To search the accounts module for zip codes to get the account ids to match in the relations table (accounts_opportunities) to get the associated opportunity_ids …

     So, I have SQL that get the desired results.

     I am :

     a) not sure about the correct entry in the SearchDefs.php for my SQL currently using ‘subquery’ (is join_sql it?)

     b) not sure if I need to make entries in vardefs.php –  am not making new fields…

     c) not seeing any entries in logs (at DEBG level) when I make a search in the accounts.billing_address_postalcode field in the Advanced Search form

     d) get no results when I do a search (like a NULL operation).

    So,  I can post code if that will help (did not wan to clutter this message in case you can get what you need and just say ” Ah, you need to ….”)

     My goal is to add a field to take in a numeric (Zip Code or partial) entry search and a result set that is the Opportunities where the related Accounts have the Zip Code.

    The SQL that (from a mysql prompt) provides accurate results is:

    SELECT opportunities.id from opportunities opportunities INNER JOIN accounts_opportunities on accounts_opportunities.opportunity_id=opportunities.id INNER JOIN accounts on
    accounts.id=accounts_opportunities.account_id  WHERE accounts.deleted=0
    AND billing_address_postalcode LIKE ’92%’;

     I hope I provided enough starter information — let me know if you need any specific details.

    Thanks in advance

  3. 

    Any thoughts or feedback?

  4. 

    checking back to see if there are  Any thoughts or feedback?
     
    address in another fashion/forum?
    let me know

  5. 

    I thought I would try again to see if there are any other thoughts regarding this issue.   My next step is to rewind all and walk through the process anew (new install, restore site in dev, make updates and test).

  6. 

    Still digging into this …

     A couple of things for now:

    1) I still not not see any indication that my query is being run (looking in the logs)

      1.a) Should this be a subquery?  Or a operater=’innerjoin’?  
     
      1.b) How do you figure the difference or better can you (for me) describe the difference and when to use one?

    2) I have tried both but am not sure if the db_field should be:

        ‘db_field’ => array(
            ‘id’,
            ),

     
    or

       ‘db_field’ => array(
            ‘accounts.billing_address_postalcode’,
            ),

     I am looking for details on how db_field relates to the query.  Can you help?

  7. 

    @9032379a9e4ea45514585e1c3d124b48:disqus , could you show us the code you are using so we can try and debug this?

    Thanks!

  8. 

    Thanks John,

    In custom/modules/Opprotunities/metadata/SearchField  I have placed at the end of the file:

    $searchFields['Opportunites']['account_postcode'] = array(
        ‘query_type’ => ‘default’,
        ‘operator’ => ‘subquery’,
        ‘subquery’ => ‘SELECT accounts_opportunities.opportunity_id from accounts_opportunities INNER JOIN accounts on accounts.id=accounts_opportunities.account_id WHERE accounts.deleted=0 AND accounts.billing_address_postalcode LIKE ‘,
        ‘db_field’ => array(
            ‘id’,
            ),

    I have updated the searchdefs.php and the SearchFields.php in the custom/modules/Opportunities/metadata/ dir.

    A rebuild has the field available on the Advanced Search panel.

    An entry in the field and a search results in no action. I am not seeing
    any entries in logs or errors (at FATAL level) when I make a search in
    the accounts.billing_address_postalcode field in the Advanced Search
    form. The web server spins when an entry and submit is made the count
    goes to 0 then back to a full result set.

    I am  still digging into this …

    .A couple of things for now:

    1) I still not not see any indication that my query is being run (looking in the logs)

    . 1.a) Should this be a subquery?. Or a operater=’innerjoin’? .

    .

    . 1.b) How do you figure the difference or better can you (for me) describe the difference and when to use one?

    2) I have tried both but am not sure if the db_field should be:

    … ‘db_field’ => array(

    ……. ‘id’,

    ……. ),

    .

    or

    .. ‘db_field’ => array(

    ……. ‘accounts.billing_address_postalcode’,

    ……. ),

    .I am looking for details on how db_field relates to the query.. Can you help?

    Also, here is some additional detail:

    http://www.sugarcrm.com/forums/f6/opportunies-advanced-search-accounts-post-code-zip-code-78761/#post275051

    I appreciate your time on this — let me know what else I can provide.

  9. 

    Good Day, 

     I am checking in to see if the details I provided gave you what you needed.  Can you let me know or if I need to (and what) provide more information?

     Or if you have some starter ideas that I can work on, I would appreciate it.

     I hope your Friday is a good one

    Thanks

  10. 

    I would like to do something similar as far as pulling information from a different module.   We use the subject line in “notes” to enter the name of products our customer purchased.  For example John Doe would have a note stating “Wall Decor” in the subject.

    I want to be able to search note subjects from the accounts page so we can search “Wall Decor” and pull up a list of accounts with notes that match.   

    Alternatively – we could search notes because it shows related accounts however I would like it to also show email addresses related to that account  (not contact email as we do not use the contacts module).

    Any suggestions?

  11. 

    This doesn’t seem to work for SugarCE 6.5.0, Can somebody revise the doc.

  12. 

    Hello there, great post!
    Was able to create a search field looking for accounts based on the opportunities attached to them and their probability (for example looking for all accounts having a 100% opportunity).

    SearchFileds.php looks like:

    $searchFields['Accounts']['opportunity_val'] = array(
        ‘query_type’ => ‘default’,
        ‘operator’ => ‘subquery’,
        ‘subquery’ => ‘SELECT accounts_opportunities.account_id FROM accounts_opportunities INNER JOIN opportunities ON opportunities.id=accounts_opportunities.opportunity_id WHERE opportunities.deleted=0 AND opportunities.probability=’,
        ‘db_field’ => array(
            ‘id’,
            ),
        );  

    Now I would like to simply have a checkbox and search for ’100′ value when it is checked, but couldn’t figure out a way to do it..?

  13. 

    Thanks for the post.

    Above solution is based on subquery and LIKE condition, but what about searches based on equal ID?

    Example:

    - ModuleA has one-to-many relationships with Contacts (“Associated Contacts”)

    - ModuleA record has “Associated Contacts” subpanel in detailview

    - ModuleA: Search panel has (new) related search field “Associated Contact”. New search field introduced by me.

    - I want to list ModuleA records that have selected “Associated Contact” as one of the “Associated Contacts”

    Thanks,

    Igor

  14. 

    Hi,

    Thanks for the informations, it works great!

    However, i’d like to use a dropdown from accounts on the contacts searchdefs, I tried adding a bit of arguments in searchdefs of contacts but nothing seems to wordk. Could you give me advice ?

    Thanks.

    • 

      Hello, I would also appreciate the answer for this question. In my case it is a custom dropdown field in the accounts module: “employees_c”. Does this make any difference, is it possible with a custom field?

      Thank you, Timi

  15. 

    Hi,
    Thanks for the information.

    But i identify the one change i.e. searchdefs.php should be in custom/modules/Calls/metadata/ directory NOT in custom/modules/Calls/ directory.

    After apply the above change, then i am able to get the desired output.

  16. 

    Hi, thank you for the guide! I’m trying to adapt your code, I’ve wrote the query, but the search field doesn’t work yet. Maybe I’ve wrote something wrong in the other parameters. Can you explain to me what they do exactly? (I’m sorry, I’m really noob at SugarCRM).

    Here’s my code:

    $searchFields['Cproj_order_management01']['offernumber_c'] = array(
    ‘query_type’ => ‘default’,
    ‘operator’ => ‘subquery’,
    ‘subquery’ => ‘SELECT cproj_order_management01.name, cproj_order_management01.om_status, cproj_order_management01.assigned_user_id
    FROM cproj_order_management01, opportunities, cproj_order_management01_opportunities_c, opportunities_cstm
    WHERE cproj_order_management01.deleted=0 AND
    opportunities.deleted=0 AND
    cproj_order_management01_opportunities_c.cproj_orde4d96ement01_ida=cproj_order_management01.id AND
    cproj_order_management01_opportunities_c.cproj_order_management01_opportunitiesopportunities_idb=opportunities.id AND opportunities.id=opportunities_cstm.id_c AND opportunities_cstm.offernumber_c LIKE ‘,

    ‘db_field’ => array(
    ‘id’,
    ),
    );

    And this is what I added in searchdefs.php :

    ‘offernumber_c’ => array (
    ‘name’ => ‘offernumber_c’,
    ‘label’ => ‘Proposal code’,
    ‘studio’ => ‘visible’,
    ‘type’ => ‘varchar’,
    ‘default’ => false,
    ‘width’ => ’10%’,
    ),

    Thank you so much!

  17. 

    Hi,
    is it possible to do this for global search?
    Thanks in advance
    Kourosh

Trackbacks and Pingbacks:

  1. Add a search field that searches another module - SugarCRM Forums - November 15, 2011

    [...] [...]

  2. Searches across modules - SugarCRM Forums - January 27, 2012

    [...] [...]

  3. Cross Module Search - Enum fields - February 8, 2012

    [...] [...]

  4. Related Module - Search by id (not by name/text value) - October 23, 2012

    [...] [...]

  5. SugarCRM Tips | SugarCRM Tips Tricks | SugarCRM List View Tutorial | CRM Solution Comparison - November 24, 2012

    [...] Advanced Search and save it. Don’t be intimidated by the Advanced Search – it is one of SugarCRM’s most powerful and productive features. Once you understand it, you will use it each and every [...]

  6. special function in the searchdefs.php - July 3, 2013

    [...] [...]

  7. Add a search field that searches another module « Educational Info - October 9, 2013

    [...] http://developers.sugarcrm.com/wordpress/2011/04/19/howto-add-a-search-field-that-searches-another-m… Share this:TwitterFacebookGoogleLike this:Like Loading… [...]

  8. Add a search field that searches another module | Mohan's Blog - October 9, 2013

    [...] http://developers.sugarcrm.com/wordpress/2011/04/19/howto-add-a-search-field-that-searches-another-m… Share this:TwitterFacebookGoogleLike this:Like Loading… This entry was posted in Uncategorized. Bookmark the permalink. [...]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s