search multiple custom fields from one search field

[supersource]

Is it possible to search among several custom fields (say option_1_c, option_2_c, option_3_c) from one search field (say options_c) by building a new Advanced search in Studio? I'm trying to duplicate the functionality of "Any Phone" or "Any Address" in the standard Advanced Search. I'm using version 5.2.

[danilaz]

Yes, it is possible, but instead of building a new Advanced search in Studio you have to edit one of these files:

- modules/<MODULE>/metadata/SearchFields.php
- custom/modules/<MODULE>/metadata/SearchFields.php

You have to add your search field (e.g. my_options) in SearchFields.php file this way:

 $module_name = 'my_module';
$searchFields[$module_name] =
        array (
                'name' => array( 'query_type'=>'default'),
                'current_user_only'=> array('query_type'=>'default','db_field'=>array('assigned_user_id'),'my_items'=>true, 'vname' => 'LBL_CURRENT_USER_FILTER', 'type' => 'bool'),
                'assigned_user_id'=> array('query_type'=>'default'),
                'my_options'=> array(
                        'query_type' => 'default',
                        'operator' => 'subquery',
                        'subquery' => array(
                                'OR' => 'SELECT my_module_table.id FROM my_module_table LEFT JOIN my_module_table_cstm ON ( my_module_table.id = my_module_table_cstm.id_c ) LEFT JOIN my_related_module_table ON ( my_related_module_table.id = my_module_table_cstm.my_related_module_table_id_c ) WHERE my_module_table.deleted =0 AND my_related_module_table.name LIKE',
                                'OR2' => 'SELECT my_module_table.id FROM my_module_table LEFT JOIN my_module_table_cstm ON ( my_module_table.id = my_module_table_cstm.id_c ) LEFT JOIN my_related_module_table ON ( my_related_module_table.id = my_module_table_cstm.my_related_module_table_id1_c ) WHERE my_module_table.deleted =0 AND my_related_module_table.name LIKE',
                                'OR3' => 'SELECT my_module_table.id FROM my_module_table LEFT JOIN my_module_table_cstm ON ( my_module_table.id = my_module_table_cstm.id_c ) LEFT JOIN my_related_module_table ON ( my_related_module_table.id = my_module_table_cstm.my_related_module_table_id2_c ) WHERE my_module_table.deleted =0 AND my_related_module_table.name LIKE',
                        ),
                        'db_field' => array(
                                'id',
                        )
                ),
        ); 
  

In this example, we create a text field for our module's search form also available in Studio, so you can use it in the Basic Search or Advanced Search layouts as any other field.

But what our custom search field does is to pass a custom SQL subquery to the populateFromRequest() function of SearchForm class (see include/SearchForm/SearchForm2.php file, line 667).

In our example, that subquery is an array of SQL querys corresponding to each SELECT necessary to search through each one of the fields we want to include in our custom search field.

Further more, the fields used in our example are of type Relate To, so you can search at the same time in multiple fields of other module through an unique field present in our module's search form.

I hope this help you. You can find more information in this URL: http://www.sugarcrm.com/wiki/index.p...24searchFields