How To: Global Search for Email Addresses

**allegrodan** · 2009-07-08, 05:34 PM

In reference to Thread 33218

From bug 24639:
Since the introduction of SugarCRM 5 it is impossible to search leads, contacts by email addresses in the global/unified search, except for entries that were created with older versions of sugar.

This is probably caused by moving the email addresses to the separate database table emails_email_addr_rel. But being able to find only older contacts is confusing, and it is a regression in functionality.

Here is how you can add search by email address to the global search for Contacts, Leads, and Accounts: (This change was done on a fresh 5.2.0f install)
1. Change modules/Home/UnifiedSearchAdvanced.php: NOT UPGRADE SAFE
Around line 153 add

Code:

	//allow for search by email address
                            	} elseif($field == 'email_advanced') {
									$clause = "(({$unified_search_modules[$name]['table']}.id IN (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 '{$_REQUEST['query_string']}%')))";
	//end allow for search by email address

Complete switch statement will look like this:

Code:

...
 switch($def['type']) {
                            case 'int':
                                if(is_numeric($_REQUEST['query_string']))  
                                    $clause .=  "in ('{$_REQUEST['query_string']}')";
                                else
                                    $clause .=  "in ('-1')";
                                break;
                            default:
                            	//MFH BUG 15405 - added support for seaching full names in global search
                            	if ($field == 'last_name'){
                            		if(strpos($_REQUEST['query_string'], ' ')){
                            			$string = explode(' ', $_REQUEST['query_string']);
                            			$clause = "CONCAT({$unified_search_modules[$name]['table']}.first_name, ' ', {$unified_search_modules[$name]['table']}.last_name) in ('{$string[0]} {$string[1]}', '{$string[1]} {$string[0]}')";//when search with space, we think it to be full name.
                            		} else {
                            			$clause .=  "LIKE '{$_REQUEST['query_string']}%'";
                            		}
	//allow for search by email address
                            	} elseif($field == 'email_advanced') {
									$clause = "(({$unified_search_modules[$name]['table']}.id IN (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 '{$_REQUEST['query_string']}%')))";
	//end allow for search by email address
								} else {
                            		$clause .=  "LIKE '{$_REQUEST['query_string']}%'";
                            	}
                                break;
                        }
...

2. Now all you have to do is add the any_email address field to the unified search fields: THIS IS UPGRADE SAFE, BUT IT WONT WORK WITHOUT THE ABOVE CHANGE

Step 1: In the custom/Extension/modules/Accounts/Ext/Vardefs folder, create a php file and put the following code in it:

Code:

<?php
$dictionary['Account']['fields']['id']['unified_search'] = true;
$dictionary['Account']['fields']['id']['name'] = 'email_advanced';
$dictionary['Account']['fields']['id']['vname'] = 'LBL_ANY_EMAIL';
$dictionary['Account']['fields']['id']['type'] = 'name';
$dictionary['Account']['fields']['id']['dbtype'] = 'varchar';
?>

Step 2: In the custom/Extension/modules/Contacts/Ext/Vardefs folder, create a php file and put the following code in it:

Code:

<?php
$dictionary['Contact']['fields']['id']['unified_search'] = true;
$dictionary['Contact']['fields']['id']['name'] = 'email_advanced';
$dictionary['Contact']['fields']['id']['vname'] = 'LBL_ANY_EMAIL';
$dictionary['Contact']['fields']['id']['type'] = 'name';
$dictionary['Contact']['fields']['id']['dbtype'] = 'varchar';
?>

Step 3: In the custom/Extension/modules/Leads/Ext/Vardefs folder, create a php file and put the following code in it:

Code:

<?php
$dictionary['Lead']['fields']['id']['unified_search'] = true;
$dictionary['Lead']['fields']['id']['name'] = 'email_advanced';
$dictionary['Lead']['fields']['id']['vname'] = 'LBL_ANY_EMAIL';
$dictionary['Lead']['fields']['id']['type'] = 'name';
$dictionary['Lead']['fields']['id']['dbtype'] = 'varchar';
?>

Step 4: Rebuild Extensions by clicking on Rebuild Extensions in the Admin --> Repair area.
Step 5: Clear out the unified search cache by manually removing the cache/modules/unified_search_modules.php file. Sugar will automatically rebuild this file once you do a global search.

**christianknoll** · 2009-07-08, 10:10 PM

just posted this also in antoher thread .- we just created a project on SugarForge for fulltext search in SugarCRM - http://www.sugarforge.org/projects/kinamulucene/ - this will also allow you to search for email adresses ... and for much more btw.

christian.

Originally Posted by allegrodan

In reference to Thread 33218

From bug 24639:
Since the introduction of SugarCRM 5 it is impossible to search leads, contacts by email addresses in the global/unified search, except for entries that were created with older versions of sugar.

This is probably caused by moving the email addresses to the separate database table emails_email_addr_rel. But being able to find only older contacts is confusing, and it is a regression in functionality.

Here is how you can add search by email address to the global search for Contacts, Leads, and Accounts: (This change was done on a fresh 5.2.0f install)
1. Change modules/Home/UnifiedSearchAdvanced.php: NOT UPGRADE SAFE
Around line 153 add

Code:

	//allow for search by email address
                            	} elseif($field == 'email_advanced') {
									$clause = "(({$unified_search_modules[$name]['table']}.id IN (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 '{$_REQUEST['query_string']}%')))";
	//end allow for search by email address

Complete switch statement will look like this:

Code:

...
 switch($def['type']) {
                            case 'int':
                                if(is_numeric($_REQUEST['query_string']))  
                                    $clause .=  "in ('{$_REQUEST['query_string']}')";
                                else
                                    $clause .=  "in ('-1')";
                                break;
                            default:
                            	//MFH BUG 15405 - added support for seaching full names in global search
                            	if ($field == 'last_name'){
                            		if(strpos($_REQUEST['query_string'], ' ')){
                            			$string = explode(' ', $_REQUEST['query_string']);
                            			$clause = "CONCAT({$unified_search_modules[$name]['table']}.first_name, ' ', {$unified_search_modules[$name]['table']}.last_name) in ('{$string[0]} {$string[1]}', '{$string[1]} {$string[0]}')";//when search with space, we think it to be full name.
                            		} else {
                            			$clause .=  "LIKE '{$_REQUEST['query_string']}%'";
                            		}
	//allow for search by email address
                            	} elseif($field == 'email_advanced') {
									$clause = "(({$unified_search_modules[$name]['table']}.id IN (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 '{$_REQUEST['query_string']}%')))";
	//end allow for search by email address
								} else {
                            		$clause .=  "LIKE '{$_REQUEST['query_string']}%'";
                            	}
                                break;
                        }
...

2. Now all you have to do is add the any_email address field to the unified search fields: THIS IS UPGRADE SAFE, BUT IT WONT WORK WITHOUT THE ABOVE CHANGE

Step 1: In the custom/Extension/modules/Accounts/Ext/Vardefs folder, create a php file and put the following code in it:

Code:

<?php
$dictionary['Account']['fields']['id']['unified_search'] = true;
$dictionary['Account']['fields']['id']['name'] = 'email_advanced';
$dictionary['Account']['fields']['id']['vname'] = 'LBL_ANY_EMAIL';
$dictionary['Account']['fields']['id']['type'] = 'name';
$dictionary['Account']['fields']['id']['dbtype'] = 'varchar';
?>

Step 2: In the custom/Extension/modules/Contacts/Ext/Vardefs folder, create a php file and put the following code in it:

Code:

<?php
$dictionary['Contact']['fields']['id']['unified_search'] = true;
$dictionary['Contact']['fields']['id']['name'] = 'email_advanced';
$dictionary['Contact']['fields']['id']['vname'] = 'LBL_ANY_EMAIL';
$dictionary['Contact']['fields']['id']['type'] = 'name';
$dictionary['Contact']['fields']['id']['dbtype'] = 'varchar';
?>

Step 3: In the custom/Extension/modules/Leads/Ext/Vardefs folder, create a php file and put the following code in it:

Code:

<?php
$dictionary['Lead']['fields']['id']['unified_search'] = true;
$dictionary['Lead']['fields']['id']['name'] = 'email_advanced';
$dictionary['Lead']['fields']['id']['vname'] = 'LBL_ANY_EMAIL';
$dictionary['Lead']['fields']['id']['type'] = 'name';
$dictionary['Lead']['fields']['id']['dbtype'] = 'varchar';
?>

Step 4: Rebuild Extensions by clicking on Rebuild Extensions in the Admin --> Repair area.
Step 5: Clear out the unified search cache by manually removing the cache/modules/unified_search_modules.php file. Sugar will automatically rebuild this file once you do a global search.

**ramji123** · 2009-11-18, 12:02 PM

Hi,

I am using sugar pro 5.2.0k

I am not getting the result email address global search.
i followed your instruction but blank result is coming.

Ramji

**allegrodan** · 2009-11-18, 02:19 PM

Which module are you trying to search? Post each filename you changed, along with the code you changed from that file. Also, make sure you did a rebuild of the extensions, and that you deleted the cache/modules/unified_search_modules.php file afterwards.