CiviCRM Community Forums (archive)

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.

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

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

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

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?

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!

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.

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.