Any update on "OR" searching on custom multi-select fields??

[sewen]

I found this original forum entry "on searching custom multi-select fields",  http://forum.civicrm.org/index.php/topic,671.0.html.  When I tried to attach a comment it said the forum entry was old and I should start a New Topic.

Anyway, I was wondering if there has been an update on "OR" searching on multi-select fields... or has someone come up with a work around.  We want to attach multiple categories to a member, using a custom multi-select field, and then search for members that fall in different categories, i.e. members that own a hotel, have an attached restaurant, and rent boats.

Any help would be appreciated.

Thanks.

[Donald Lobo]

We have not implemented anything in core for OR support for multi-select custom elements.

However this might be a good fit for the custom search functionality developed in 2.0. Would be good contribution and help other folks who are looking for somethign similar

wanna take a crack at it? we an help out on IRC

lobo

[ctarascio]

Hello lobo,
   Turns out we also have a need to implement the "OR" functionality for custom multi-select fields. In short: we need custom multi-select fields to behave in searches the same way that built-in multi-select data fields do.
   We can't do the development but I can see about funding part of this effort. It would be great if  "others" could pitch in as well. Any takers? 

How many hours of work do you think will be involved? I realize it's probably too late to get this into 2.0 but how about 2.1?

Thanks,
Cynthia

[Donald Lobo]

Cynthia:

filed and fixed for 2.1

http://issues.civicrm.org/jira/browse/CRM-3190

Its a pretty small and easy fix. you might be able to convince aaron to patch your install for you. He can get the patch here:

http://fisheye.civicrm.org/changelog/CiviCRM?cs=15008

lobo

[ctarascio]

Dear lobo,
   You are the greatest!!!! I owe you the beverage of your choice when next we meet. Thank you so much for this.

Cynthia

[ctarascio]

Lobo,
   Two possibly silly questions:

1. Can we apply the patch even though we are running 2.0.13874?
2. Will the patch effect Advanced Search, Search Builder or both? I looked at CRM-3190 and couldn't tell exactly what was effected.

Thanks again,
Cynthia

[Donald Lobo]

1. You should be able to apply the patch for 2.0.13874. You might need to manually apply it since the files have changed a bit. However i do think the code will be valid in 2.0.x

2. The patch effects Advanced Search and Profiles only. I'm not sure if search builder allows searching of checbox/multi-select fields in a sane manner

lobo

[ctarascio]

Thanks again lobo 

[petednz]

Maybe I am missing a point here but we are using a lot of Custom Data with multiselect options (Lobo should be able to confirm this ;-))

I have not had any probs that I was aware of in using Search Builder for OR or AND.

In the attached image it shows Policy Issues (has 150 multiselect options  ) while the others are checkboxes within the same custom data group.

This set up shows the OR but can also do it with AND.

(Happy to be told I am on the wrong planet - and if so which one - but seems to be what is being discussed.)

Am I right in thinking the patch here will mean that if we select more than one checkbox in custom data for a multiselect field it will run the search as an OR rather than an AND? Or does the patch let us choose?

[Donald Lobo]

I need to be more descriptive . Yes u can enter "single element" values for a multi-select in search builder. If you need to do an OR / AND for n elements, you will need to add it n times (not the greatest usability experience, IMO)

The new feature in 2.1 will have the same behavior as current. It introduces a new checkbox / multi-select element titled "Use SQL OR". If this checkbox / multi-select is selected, we'll do an OR instead of the default AND

Its not the cleanest user experience, but was pretty easy to add in. I suspect the functionality is important enough to forgive the not-great layout

lobo

[Chris Burgess]

FYI - we're at CiviCRM 2.0 SVN r14412, for us this patch applied fine.

We've merged in the custom PHP mod also, so I'm now applying any post-2.0 patches to copies of the patched files in a separate directory. All working well so far.

Thanks Lobo and CiviCRM team!

[petednz]

any chance the OR could be implemented so it can be applied across Custom Fields within the same Tab?

[petednz]

Lobo - contrary to my skype reversal, i think it does not do what I am hoping it could do. If I have several Custom Data fields in a Custom Group can I use the 'sql or' so that it runs the 'or' query between the Fields rather than only within the multiselects in a field.
Note in the example below, 'All issues' is a single checkbox, as is 'Health and Wellbeing'. Policy Issues is a 150 strong multiselect.
My search gives me this
    *  All issues = Yes ( OR )   ... AND ...
    * Health and Wellbeing = Yes ( OR )   ... AND ...
    * Policy Issues = A2 ( AND )

I was hoping for
    *  All issues = Yes ( OR )   ... OR ...
    * Health and Wellbeing = Yes ( OR )   ... OR ...
    * Policy Issues = A2 ( AND )

(Note, if it can't be used this way then I am not sure why having the 'use SQL OR' needs to be there for a single checkbox, but i presume it is because it was easier to have it there, than work out how to not have it there ;-))
Only asking as I have 150 or so Mailing Lists to get users to construct and this would have been a fine way to do it.
I know the better solution would be Custom Search, but isn't an area we have yet become familiar enough with to just easily build them.

[Donald Lobo]

I just checked in a fix so that the additional checkbox (Use SQL OR) is not displayed when there is only one checkbox / multi-select element

This issue resolved AND/OR within a single custom field not across different custom fields. The latter needs more thought (and probably lots more code also!)

lobo

[davej]

Good to have the choice of AND or OR.

But the phrasing "Use SQL OR" is extremely poor from a user-interface point of view. What proportion of end users - who might be members of the public doing a profile search for their local toddler group or knitting circle - know what SQL is? Now the trickier part: coming up with a constructive suggestion! Here's one:

[Tick to match ANY; untick to match ALL]   (en_GB)
[Check to match ANY; uncheck to match ALL]   (en_US)

- With the brackets, to distinguish this checkbox from the option values.