Adding Many to Many Relationships to the Search Box in SuiteCRM and SugarCRM CE 6.5.x

Out the box SuiteCRM and SugarCRM CE do not allow searching from related modules with a many to many relationship. But this can be overcome by adding a custom relate field that represents your relationship then using a custom sub-query in SearchFields.php.

Step one add the relate field:

Create a file for the module you want to add the non-db related field in the custom extension vardefs:

custom/Extension/modules/Contacts/Ext/Vardefs/anyname.php


$dictionary["Contact"]["fields"]["par_med_cond"] = array (
'name' => 'par_med_cond',
'type' => 'relate',
'source' => 'non-db',
'module' => 'FRP_FundraisingProgram',
'vname' => 'LBL_CONTACTS_PAR_MED_COND',
'studio' => 'visible',
'id_name' => 'par_med_cond_id',
);

$dictionary["Contact"]["fields"]["par_med_cond_id"] = array (
'name' => 'par_med_cond_id',
'type' => 'id',
'source' => 'non-db',
'module' => 'FRP_FundraisingProgram',
'vname' => 'LBL_CONTACTS_PAR_MED_COND',
'studio' => 'visible',
);

$dictionary["Contact"]["fields"]["frp_fundraisingprogram_contacts_1frp_fundraisingprogram_ida"] = array (
'name' => 'frp_fundraisingprogram_contacts_1frp_fundraisingprogram_ida',
'type' => 'link',
'source' => 'non-db',
'reportable' => false,
'module' => 'FRP_FundraisingProgram',
'side' => 'right',
'vname' => 'LBL_CONTACTS_PAR_MED_COND_ID_TITLE',
);

Step 2 Step two add your field to the searchdefs

custom/modules/Contacts/metadata/searchdefs.php

$searchdefs ['Contacts'] =
array (
'layout' =>
array (
'basic_search' =>
array (
'search_name' =>
array (
'name' => 'search_name',
'label' => 'LBL_NAME',
'type' => 'name',
'default' => true,
'width' => '10%',
),
'par_med_cond' =>
array (
'type' => 'relate',
'studio' => 'visible',
'default' => true,
'width' => '10%',
'label' => 'LBL_CONTACTS_PAR_MED_COND',
'id' => 'PAR_MED_COND_ID',
'link' => true,
'name' => 'par_med_cond',
),

Step 3 Step two add your sub-query to the SearchFields.php

custom/modules/Contacts/metadata/SearchFields.php

See the par_med_cond definition

$searchFields['Contacts'] = array (
'first_name' =>
array (
'query_type' => 'default',
),
'last_name' =>
array (
'query_type' => 'default',
),
'search_name' =>
array (
'query_type' => 'default',
'db_field' =>
array (
0 => 'first_name',
1 => 'last_name',
),
'force_unifiedsearch' => true,
),
'account_name' =>
array (
'query_type' => 'default',
'db_field' =>
array (
0 => 'accounts.name',
),
),
'lead_source' =>
array (
'query_type' => 'default',
'operator' => '=',
'options' => 'lead_source_dom',
'template_var' => 'LEAD_SOURCE_OPTIONS',
),
'do_not_call' =>
array (
'query_type' => 'default',
'input_type' => 'checkbox',
'operator' => '=',
),
'phone' =>
array (
'query_type' => 'default',
'db_field' =>
array (
0 => 'phone_mobile',
1 => 'phone_work',
2 => 'phone_other',
3 => 'phone_fax',
4 => 'assistant_phone',
),
),
'par_med_cond' =>
array (
'query_type' => 'default',
'operator' => 'subquery',
'subquery' => 'SELECT spsmc.frp_fundraisingprogram_contacts_1contacts_idb FROM frp_fundraisingprogram_contacts_1_c spsmc LEFT JOIN frp_fundraisingprogram smc ON spsmc.frp_fundraisingprogram_contacts_1frp_fundraisingprogram_ida = smc.id WHERE smc.deleted =0 AND spsmc.deleted =0 AND smc.name LIKE',
'db_field' =>
array (
0 => 'id',
),
),
'email' =>
array (
'query_type' => 'default',
'operator' => 'subquery',
'subquery' => 'SELECT eabr.bean_id FROM email_addr_bean_rel eabr JOIN email_addresses ea ON (ea.id = eabr.email_address_id) WHERE eabr.deleted=0 AND ea.email_address LIKE',
'db_field' =>
array (
0 => 'id',
),
),

The custom relate field representing our relationship should now be available to add to the search layouts via studio and and return the correct search results. However this will not work if you need to add the field to the popupview search.

Searching on relationships is one of the big shortcomings in Sugar. Once you do get it to work for the main search results, it is likely to break if you use the pagination or Mass Update on those search results.

This is similar to what we are seeing with the popupview search. The back end doesn’t use a unified method of searching. It’s a different stack of calls depending on where you are. Very frustrating. What this means is that you can’t always get something on the list view/searches to work across to other areas like the popup view.

I struggled with this for a while then is was suggested to me to customise the view.popup.php for the module and jam in the condition if being searched on in $popupMeta['whereStatement'] within display().

Adding the many to many search to the Popup View

custom/modules/Contacts/views/view.popup.php

Inside the view.popup.php for Contacts there was an updatePopupMeta() function. Inside this function I added the following:

if(!empty($_REQUEST['mode']) && $_REQUEST['mode']=='MultiSelect_fp')
{

$popupMeta['whereStatement'] .= (!empty($popupMeta['whereStatement']) ? ' AND' : '')." ((contacts.id IN (select frp_fundraisingprogram_contacts_1contacts_idb from (SELECT spsmc.frp_fundraisingprogram_contacts_1contacts_idb FROM frp_fundraisingprogram_contacts_1_c spsmc LEFT JOIN frp_fundraisingprogram smc ON spsmc.frp_fundraisingprogram_contacts_1frp_fundraisingprogram_ida = smc.id WHERE smc.deleted =0 AND spsmc.deleted =0 AND smc.name LIKE '".$_REQUEST['metadata']."') par_med_cond_derived))) AND contacts.deleted=0 ";
}

This adds the required sub-query onto the where statement in the popup filter if mode equals ‘MultiSelect_fp’. I also had to customise the open popup JavaScript to pass in the required search parameter for the popups initial filtering: $_REQUEST['metadata']

Andy
About

Software Developer from Falkirk, Central Scotland.

Posted in SugarCRM development and customisation, SugarCRM development development hints and tips

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>