customize search fields and add new one’s in SugarCRM

sugarcrmdevelopers —  February 8, 2011

Editors Note: This post comes from the blog of Milos Miric located at http://www.eontek.rs, where posts frequently about customizing SugarCRM. In this post he addresses a concern that came up in the forums recently, where a user was looking to customize the search fields in their Sugar instance.

After a lot of time spent working on this i want to share with you how you are able to customize search fields and searchdefs so in the end you can insert a field that will be a search criteria and that field can be from another module related directly or related to a module that is related to another module.

We will be working with accounts module and we want to add a field that will be criteria for a search. This field is in module semi_metadatas and relations go like:

accounts->semi_keywords->semi_metadatas

We want this field to be dropdown from distinct values from semi_metadatas table. To accomplish this we will create a field in accounts vardefs with the following definition:

/*
 * @author Milos Miric
 */
$dictionary["Account"]["fields"]["test"] =
		array(
			'name' => 'test',
			'vname' => 'test',
			'type' => 'varchar',
			// function to call that will return html that will be inserted
			'function' => array('name' => 'test', 'returns' => 'html', 'include' => 'devteam/miric/dropdown.php'),
			'required' => false,
			'do_report' => false,
			'reportable' => false,
			'comment' => 'Currency in use for the campaign',
			'source' => 'non-db'
);

We now need that file with a function for this to work. Here is simple example but what here needs to be done is make a db-query and get all distinct values inserted:

/*
 * Date: 28.12.2010.
 * Filename: dropdown
 * Description:
 *
 * @author Milos Miric
 */
function test($focus, $field, $value, $view){
$html = '<option value="market">Market</option>
  <option value="test">Test</option>
  <option value="test2">Test2</option>
  <option value="test3">Test3</option>';
return $html;
}

After field definition we need to notify the search form to show our field on the form by changing searchdefs.php :

/*
 * @author Milos Miric
 */
'basic_search' => array(
	'name',
	'billing_address_city',
	'phone_office',
	array('name' => 'address_street', 'label' =>'LBL_BILLING_ADDRESS', 'type' => 'name' , 'group'=>'billing_address_street'),
	array('name'=>'current_user_only', 'label'=>'LBL_CURRENT_USER_FILTER', 'type'=>'bool'),
	array('name' => 'semi_keywords_keyword','customCode' => 'aaa'),
	// our new field
	array('name' => 'test', 'label' => 'test field', 'type' => 'enum'),
	),

After we add our field to search form we told it to be type => enum and search will generate the < select> < /select> for us and our function for field will be returning < option> < /option>. The next thing is we need to modify the query so the search is using our new field as account.id IN (… )

We modify the searchfields.php in accounts and add the following:

/*
 * @author Milos Miric
 */
// what we added here is a subquery that will be run and that it needs to return a list of account id's
// and this query will be added in final search query and it will look like
// select accounts.id IN (SELECT query down there)

'test' => array(
	'query_type' => 'default',
	'operator' => 'subquery',
	'subquery' => 'SELECT acc.id FROM accounts AS acc
		INNER JOIN semi_keywords_accounts AS kwacc ON kwacc.accounts_idb = acc.id
		INNER JOIN semi_keywords AS kw ON kw.id = kwacc.semi_keywords_ida
		INNER JOIN semi_kw_semi_kwmetadatas AS kwmd ON kwmd.semi_keywords_ida = kw.id
		INNER JOIN semi_metadatas AS md ON md.id = kwmd.semi_metadatas_idb
		WHERE acc.deleted <> 1 AND kwacc.deleted <> 1 AND kw.deleted <> 1
		AND kwmd.deleted <> 1 AND md.deleted <> 1 AND md.name LIKE',
	'db_field' => array('id'),
	'type' => 'enum',
	),

After all this is done, on your search you will have a dropdown that will hold the values from our other module and the query will do LIKE ‘value%’ in it’s search.

I hope this knowledge will help you on your quest in customizing sugarcrm.

Be good!

Share