Searching Multiple Select Dropdown fields

[mrobertson]

When you add make a search selection on a field that has multiple selections, it only returns records that do not have multiple selections listed.

Eg. Say I have two accounts, on of them has "Books^,^Magazines" in my custom field and the other only has "Books".
If I select "Books" in my search screen, I only receive one record back instead of both.


mrobertson

[mrobertson]

I think it can be fixed by setting the search option to LIKE instead of IN, does anybody know where I can change the search type for custom fields?

mrobertson

(on a side note the inline spell checker in Firefox 2.0 is very nifty)

[westowl]

Did you ever figure this out? I'm having the same problem... mutliple selections in search doesn't work.

[martin.huber@canoo.com]

I also have run into that bug:

it seems the DetailView is storing the multiple field value as a concatenated text of the form 'A^,^B^,^C' (note the ^).
The query that is built when searching for A, B (that should have the meaning of a contains A or contains B) is:
.... where custom_field in 'A, B'

This is working, if the custom field only contains A, but not if it does contain multiple values as shown above.

In fact I could not think of a really easy way (I am not very used to sql), but the query probably should be something like:

... where split(custom_field, '^,^') contains A or split(custom_field, '^,^') contains B ...

Perhaps one of you who know sql better than me can think of a solution.

Involved files:

include/formbase.php, line 61-63
if(!empty($_POST[$prefix.$field][0])){
$_POST[$prefix.$field] = implode('^,^', $_POST[$prefix.$field]);
}

include/SearchForm/SearchForm.php, from line 187:
if(is_array($parms['value'])) {
$operator = 'in';
$field_value = '';
foreach($parms['value'] as $key => $val) {
if($val != ' ' and $val != '') {
if (!empty($field_value)) {
$field_value .= ',';
}
$field_value .= "'" . $GLOBALS['db']->quote($val) . "'";
}
}
}

Best regards,
Martin

[martin.huber@canoo.com]

Ok - I now found out that
1. this bug already has been reported at bugs.sugarcrm.com and is scheduled to be fixed in 4.5.1

2. I nevertheless made a temporary bugfix for the time being that is working for me. Here is the lines I added to include/SearchForm/SearchForm.php (the few lines beteen the two comment // bugfix and // end bugfix).

That fix is far from being perfect, but it seems to work ok for me (until the official fix is out). Especially it would work wrong, if one of the search words (the dropdown values) is a substring of another one. Then the bigger one would be matched even if searched for the smaller one. But this is not the case in our setup.

Maybe this patch has sideeffects, but I didn't notice one. And since it is "only" searching, it cannot be harmful.

Regards,
Martin

                         if($type == 'date') {
                           // Collin - Have mysql as first because it's usually the case
                           // The regular expression check is to circumvent special case YYYY-MM
                           if($GLOBALS['db']->dbType == 'mysql') {
                                 if(preg_match('/^\d{4}.\d{1,2}$/', $field_value) == 0) {
                                    $field_value = $timedate->to_db_date($field_value, false);
                                    $operator = '=';
                                 } else {
                                    $operator = 'db_date';
                                 }
                           } else if($GLOBALS['db']->dbType == 'oci8') {
                                 if(preg_match('/^\d{4}.\d{1,2}$/', $field_value) == 0) {
                                    $field_value = $timedate->to_db_date($field_value, false);
                                    $field_value = "to_date('" . $field_value . "', 'YYYY-MM-DD hh24:mi:ss')";
                                 }
                                 $operator = 'db_date';
                           } else if($GLOBALS['db']->dbType == 'mssql') {
                                 if(preg_match('/^\d{4}.\d{1,2}$/', $field_value) == 0) {
                                    $field_value = "Convert(DateTime, '".$timedate->to_db_date($field_value, false)."')";
                                 }
                                 $operator = 'db_date';
                           } else {
                                     $field_value = $timedate->to_db_date($field_value, false);
                                     $operation = '=';
                           }
                        }
 
 
                        if($GLOBALS['db']->dbType == 'oci8' && isset($parms['query_type']) && $parms['query_type'] == 'case_insensitive') {
                              $db_field = 'upper(' . $db_field . ")";
                              $field_value = strtoupper($field_value);
                        }
 
// bugfix, line 260, include/SearchForm/SearchForm.php:
// test for every multi select parameter, if it is a substring of the field:
// build sql of the form:
// locate('term1', FIELD)>0 OR locate('term2', FIELD)>0 ...
                if(is_array($parms['value'])) {
                    foreach($parms['value'] as $key => $val) {
                        if($val != ' ' and $val != '') {
                            if(!empty($where)) {
                                $where .= " OR ";
                            }
                            $where .= " locate('" . $GLOBALS['db']->quote($val) . "',$db_field)>0 " ;
                        }
                    }
                }
// end bugfix: test for every multi select parameter, if it is a substring of the field
 
                        $itr++;
                        if(!empty($where)) {
                            $where .= " OR ";
                        }
 
                         switch(strtolower($operator)) {
                            case 'like':
                                $where .=  $db_field . " like '".$field_value.$like_char."'";
                                break;
                            case 'in':
                                $where .=  $db_field . " in (".$field_value.')';
                                break;
                            case '=': 
  

[heaveyb]

Martin,

Have been looking for a fix for this for weeks now!

Thanks so much as your fix has resolved the problem...will let you know if encounter any problems

This is currently the only way round I have found to Sugar's missing classification capability...this is a glaring gap in functionality that has been noted elsewhere in this forum and hopefully will appear very soon in future release

Cheers

Ben

[martin.huber@canoo.com]

This is currently the only way round I have found to Sugar's missing classification capability...

That is exactly the context we also are using the custom multi select fields

Best regards,
Martin

[heaveyb]

Also do you know how to add these multi-selects into the quick forms?
I have tried adding them based on previous experince by modifying to XXXXFormBase.php but dont know how to refer to the input - have tried using;

<select name='account_category_c[]' size='5' title='{ACCOUNT_CATEGORY_C_HELP}' MULTIPLE=true>{OPTIONS_ACCOUNT_CATEGORY_C}</select>

but this doesnt populate the list with correct values....probably due to missing options reference but not sure where you have to place that?

Any help welcome

Thanks

Ben

[martin.huber@canoo.com]

Hi Ben,

I did this once with a normal single select field in sugar3.51. Therefore I don't know if it is directly comparable to 4.5.

But there I had to modify 2 files:
1. the ListView.html (just copied on line and modified it for my field)
2. the ListView.php is preparing the OPTIONS (my field has been called "CONTACT_TYPE_C):
:
if (isset($contact_type_c)) $search_form->assign("CONTACT_TYPE_C_OPTIONS", get_select_options_with_id($app_list_strings['contact_type'], $contact_type_c));
else $search_form->assign("CONTACT_TYPE_C_OPTIONS", get_select_options_with_id($app_list_strings['contact_type'], ''));

Best regards,
Martin

[mycrmspaceclemens]

Hi Sugar Team

Could a Sugar Team Member confirm that this 'showstopper' bug will be fixed in Sugar 451. In the bug tracker (#10139) it only says Fixed in release 'future',

Cheers