Shane Dowling – SugarCRM — Searches with data from indirectly related modules

Posted on Thu 17 April 2014 in Tech

Building modules in SugarCRM can cause you to create a boatload of unnecessary relationships, simply because it's convenient to do so. Maybe you'd have a relationship where A relates to B and B to C but you'd like to search for all A module records that relate to C. One option is to maybe related A to C and maintain the relationships by using hooks on save to preserve the A-C relationship. That way you could leverage Sugar relate fields and indirect searching, but that can be a messy choice that adds a lot of complexity if overused.

The alternative is to keep the relationship structure as it is and add a new field to the search and build a custom subselect statement. For this example we're adding a Drop-Down to Accounts to allow a user to search which Accounts are indirectly related to Performers via Events. Say we wish to present a drop-down on Accounts of all performers and if you search, it will return all Accounts attached to the specified Performer.

Get the data for the drop-down

In custom/modules/Accounts/getCustomDropDown.php

require_once("modules/Performers/Performers.php");

function galleryPerformers($focus, $field, $value, $view, $tabindex='0') {
 $performersBean = BeanFactory::getBean('Performers');
 $performers = $performersBean->get_full_list();
 $html = " — ";
 $selected_performer = $_REQUEST['performer_type_search']; // Or whatever dom id your drop-down has
 foreach($performers as $performer) {
 $html .= " if($selected_performer == $performer->id) { // Props to Igor who flagged this in the comments!
 $html .= "selected";
 }
 $html .=" value='{$performer->id}'>{$performer->name}";
 }
 return $html;
 }
 ```

### Present the drop-down on the accounts search

Note This has to go into modules/Accounts(I know, I know), but this had
to break updatability to work otherwise this will wipe with a quick
repair/rebuild(if you put it in custom). If you find an alternative
solution put it here.

So in modules/Accounts/metadata/vardefs.php
```php
$dictionary["Account"]["fields"]["performer_type_search"] = array(
 'name' => 'test',
 'vname' => 'test',
 'type' => 'varchar', // function to call that will return html that will be inserted
 'function' => array(
 'name' => 'galleryPerformer',
 'returns' => 'html',
 'include' => 'custom/modules/Accounts/getCustomDropDown.php'),
 'required' => false,
 'do_report' => false,
 'reportable' => false,
 'comment' => 'Performer Searching',
 'source' => 'non-db'
 );

Extend the search

Now that you have your drop-down working you need to extend the search to do what you need.

custom/modules/Accounts/metadata/SearchFields.php

'performer_search' => array(
 'query_type' => 'default',
 'operator' => 'subquery',
 'subquery' => 'SELECT accounts.id FROM accounts LEFT JOIN account_event acc_evt ON acc_evt.\`account_ida\` = accounts.id LEFT JOIN performers_event per_evt ON pvt_evt.\`event_idb\` = acc_evt.\`performers_idb\` WHERE acc_evt.deleted = 0 AND pvt_evt.deleted = 0 AND accounts.deleted = 0 AND pvt_evt.\`fair_ida\` LIKE',
 'db_field' => array('id'),
 'type' => 'enum', ),

Now this query probably looks a little weird, but basically it's an almost pseudocode type query that simply connected the two lookup tables between Accounts and Events then Events and Performers and does the where on the performers id.

Note the LIKE in the query. Even if you're querying IDs, Sugar stupidly passes in the value with a % at the end. So make sure this is LIKE statement.