Multiselect field search

[jumblesale]

Hi,
I have added a multi-select field to the contacts module and users are selecting multiple selections from the list. The problem is that if they then search for one of the matching selections in the list, it doesn't return the users who have selected the matching item AND another. The users who have selected multiple items ARE however returned in the search if they search on all selected items in the field.

So, my question is, I assume Sugar must be doing an 'AND' search on the field- is there any way of forcing this to allow OR as well, or to add a drop down to allow users to choose AND or OR selection?

Thanks in advance,
J

[jumblesale]

Just to add, I've searched on this topic and found a bug fix to be released in v5.1 (http://www.sugarcrm.com/crm/?option=...2-4768f9c616a7) and an ad-hoc fix for 5.0.0a but I am using 5.0.0f and the fix doesn't apply: http://www.sugarcrm.com/forums/showthread.php?t=31214

Cheers,
J

[Gronk]

Workaround added for 5.0.0f to http://www.sugarcrm.com/forums/showthread.php?t=31214

[nirokato]

Can anyone check if this works for Version 5.0.0g ?
I've tried Gronk's solution at http://www.sugarcrm.com/forums/showthread.php?t=31214

I have a custom multi-select field in my accounts section and I added to the Basic Search, still won't work with the fix.

Example of situation:

My Version:
Version 5.0.0g (Build 3391).

Multi-Select:

Categories

• Category A
• Category B

I have an account listed with both A and B selected.
I also have an account with just A selected.

When I search for both or either category both accounts do not show up in the list.


Any suggestions? I'm trying to understand the function, but my PHP is somewhat limited.
I am also idling in irc://irc.freenode.net/#sugarcrm
You may also join the IRC channel by using SugarCRM's own IRC web client:
http://www.sugarforge.org/chat/

I will be there for the next 5 hours.

[nirokato]

This is a Bump. I hope someone is able to help me.

[Gronk]

I've just done the upgrade from 5.0.0f to 5.0.0g, and tried adding a test multiselect to to the accounts module (just in case it used a different system from contacts) and can confirm that the workaround functions.

Your best bet is to turn on logging by modifying log4php.properties, running a search using two or more options in your custom multiselect, and checking the sugarcrm.log file.
You should see something like this:

AND jt0.deleted=0 where ((accounts_cstm.test_c like 'health_resort' or accounts_cstm.test_c like '%health_resort^%' or accounts_cstm.test_c like '%^health_resort%' or accounts_cstm.test_c like '%^health_resort^%' or accounts_cstm.test_c like 'yoga_classes' or accounts_cstm.test_c like '%yoga_classes^%' or accounts_cstm.test_c like '%^yoga_classes%' or accounts_cstm.test_c like '%^yoga_classes^%')) AND accounts.deleted=0 LIMIT 0,21

[nirokato]

Wed Aug 20 10:13:43 2008,283 [27576] DEBUG SugarCRM - Limit Query: SELECT  accounts.id   , accounts_cstm.*, accounts.name , accounts.billing_address_city , accounts.phone_office  , jt0.user_name assigned_user_name , jt0.created_by assigned_user_name_owner  , 'Users' assigned_user_name_mod, accounts.assigned_user_id  FROM accounts   LEFT JOIN accounts_cstm ON accounts.id = accounts_cstm.id_c   LEFT JOIN  users jt0 ON

jt0.id= accounts.assigned_user_id AND jt0.deleted=0 AND jt0.deleted=0 where ((Account_cstm.int_search_c like 'Heating' or Account_cstm.int_search_c like '%Heating^%' or Account_cstm.int_search_c like '%^Heating%' or Account_cstm.int_search_c like '%^Heating^%')) AND accounts.deleted=0 ORDER BY accounts.name ASC Start: 0 count: 26

Wed Aug 20 10:13:43 2008,283 [27576] INFO SugarCRM - Query: SELECT  accounts.id   , accounts_cstm.*, accounts.name , accounts.billing_address_city , accounts.phone_office  , jt0.user_name assigned_user_name , jt0.created_by assigned_user_name_owner  , 'Users' assigned_user_name_mod, accounts.assigned_user_id  FROM accounts   LEFT JOIN accounts_cstm ON accounts.id = accounts_cstm.id_c   LEFT JOIN  users jt0 ON

jt0.id= accounts.assigned_user_id AND jt0.deleted=0 AND jt0.deleted=0 where ((Account_cstm.int_search_c like 'Heating' or Account_cstm.int_search_c like '%Heating^%' or Account_cstm.int_search_c like '%^Heating%' or Account_cstm.int_search_c like '%^Heating^%')) AND accounts.deleted=0 ORDER BY accounts.name ASC LIMIT 0,26

Wed Aug 20 10:13:43 2008,284 [27576] INFO SugarCRM - Query Execution Time:0.000349

Wed Aug 20 10:13:43 2008,285 [27576] ERROR SugarCRM - MySQL error 1054: Unknown column 'Account_cstm.int_search_c' in 'where clause'

It exists, but the where clause just isn't seeing it, what can I do?

[nirokato]

Bumping the thread again, I really need to find out what is going on with my install.
Sorry if I'm nagging.

[nirokato]

Apparently my problem is separate from this issue, discussion has moved off the forum.

It's too bad I don't know enough PHP/MySQL to do this myself.

Gronk, you're help is much appreciated!