Filter by related module’s field

At times we have clients asking for similar requirement, that they want to filter results by related module’s field.

Lets take basic modules and achieve similar goal by coding. Here, one needs to have idea about creating basic MySQL query.

We have Accounts and Contacts module in SugarCRM. Accounts has one-many relationship with Contacts. We will add Accounts module’s type field in Contacts search.

Steps are as below,

Step 1 : Create non-db field in Contacts module named as account_type_search. Write following code into custom/Extensions/modules/Contacts/Ext/Vardefs/<anyname>.php

Step 2 : Write following code into search field definition. Copy modules/Contacts/metadata/SearchFields.php to custom/modules/Contacts/metadata/SearchFields.php if doesn’t exist and write following code into it. If it exists at custom/modules/Contacts/metadata/SearchFields.php then add following code

'account_type_search' => array (
	'db_field' =>
	array (
	  0 => 'id',
	),
	'query_type' => 'format',
	'operator' => 'subquery',
	'subquery' => 'SELECT accounts_contacts.contact_id FROM accounts_contacts
				   INNER JOIN accounts ON accounts.id = accounts_contacts.account_id AND accounts.deleted = 0
				   WHERE accounts_contacts.deleted = 0 AND accounts.account_type IN ({0})',
),

Step 3 : Create custom/modules/Contacts/metadata/metafiles.php and write following code into it. If it exists at custom/modules/Contacts/metadata/metafiles.php, then do changes as per your needs. 

<?php
$metafiles['Contacts']['searchfields']='custom/modules/Contacts/metadata/SearchFields.php';

Step 4 : Add account_type_search field for searching into custom/modules/Contacts/metadata/searchdefs.php through studio.

Step 5 : Do “Quick Repair And Rebuild” from admin.

Note :

Here, <anyname> means the any file name for example, custom_fields, search_fields, etc. .

Hope you find this blog post helpful.

Feel free to add comments and queries, that helps us to improve the quality of posts.

You can contact us at [email protected]

Thank you.