searching a mult-select field in search builder

[craftee123]

I'm trying to use search builder in order to search for contacts that have one of multiple attributes of a custom field that I created, and I'm not having any luck, presumably because of the fact that the custom field is a multi-select field...

For example, I have a custom field called "person flag" with 20 different multi-select choices, including "law enforcement", "large donor" and "lawyer."  I want to pull a list of people who are either law enforcement or large donors.  I know that I have to type in the option value, not the label.

When I do an advanced search, I find that I have 5 law enforcement officers.  Then, when I do another advanced search, I have 7 large donors.  When I set up the query in search builder, I get no results.  In fact, when I just search (using search building) for law enforcement, I only get 3 results, presumably because the other 2 contacts are also marked as "lawyer."

I have to say that even in my small non-profit where we have 2 dedicated IT staff and 4 tech-savvy people working on implementing CiviCRM, none of us seem to have figured out the proper functionality or usability for search builder or find that it meets our needs as an extension of the advanced search capabilities...

~Sarah

[Kurund Jalmi]

Sarah,

Please check: http://wiki.civicrm.org/confluence/pages/viewpage.action?pageId=8370

Few guidelines how to use Search Builder.

kurund

[Donald Lobo]

Sarah:

Search Builder is still fairly limited in its capabilities. We hope to improve it (along with other aspects of CiviCRM) over the next few releases.

If you have specific ideas on how we can make things better / easier please let us know. If you can contribute resources to help improve what is lacking, that would be even better . Please feel free to edit the wiki page that kurund mentioned with your comments and understanding of search builder

thanx

lobo

[curufinwe]

Some of our frustration with Search Builder is because you have to use it for everything.  This is because there is no capability for advanced search to do "or" operations within a field (Groups and Tags are the exceptions of course).  And the Search Builder ends up being almost unusable itself for many simple cases. 

To describe people in our system, we created a lot of custom data fields with multiselect, for instance a "person type flag".  Now if we want to end up with a listing, say, of both "fireman" and "policeman" (two different values of that field) we need to do two separate advanced searches because there is no way to OR those two values.  Sure we could have used tags for these categories, but hey, we also want a custom field for religion, and should those just be tags too?, and what about political party, should those be tags too?  You end up with a huge cloud of different variables as tags with no way to separate the taxonomies/custom fields from each other.  Not to mention the enormous unsorted array of little checkboxes you would have to deal with.

So we implemented a lot of these demographics as separate custom fields and assumed that the search builder is going to work for us.  Not very fun for the users, but alas, can't be helped.  So how can we search for both policemen and firemen in our custom field in the search builder?  Let's see....

Well, we know that in the search builder you have to search for the database values because it can't translate like the advanced search can.  So what if we set our database option values to the same string as the option labels when we create the custom data to make it easier for our non-technical users to find out what to enter in the search builder?  (click, click, click ...30 mins later) Ok, done with that.  So for firemen, you would search for up [Individual][PersonFlag][=][firemen]  Well, the only problem is that there is still no easy way to search for more than one value for a multiselect field in the search builder.  Say you want both firemen and policemen, but not airline pilots . . . . . . who are moslems, christians, hindus, jews but not secular.  If advanced search could do OR within some fields, you could just select the ones you want in each multi-select field and hit submit.  But in search builder this turns into:

firemen and
muslim

[or]

policeman and
muslim

[or]

policeman and
christian

[or]

fireman and
christian

[or]

policeman and
hindu

and so on and so on....whole screens full.   Now imagine if we had three variables!

In my opinion, multiselect custom fields are pretty much unuseable because of this.  I think their use should actually be officialy discouraged until the search system is overhauled.  I wish someone had told us that groups are the only real way to give people attributes that are at all searchable in civcrm. It really would save people a lot of time going down the wrong rodent holes. 

Using groups this case MIGHT be easier  - the search builder results might look like
[individuals][groups][in][25,15,45,101]
and
[individuals][groups][in][12,18]

Of course that's totally opaque to the users without a serious "cheat sheet" for all the group id codes.  We're talking lots of printouts that have to be kept updated all the time. 

Also, have you ever tried to do data entry where you have to keep switching to the groups tab to add people to every group under the sun just to give them their basic characteristics?  Painful.

Of course with groups, you also can't do exclusions (not in group x) so you're kind of up a creek if you want to absolutely make sure that people in group x don't get pulled in your search.  This is the type of operation that real organizations need to do every day - see above: NOT pilots and NOT secular.  So basically, groups are right out. 

So theres not really any good solution to this problem using stock civiCRM that we can find.  We'd kill for a bright idea if anyone can post a reply!

CiviCRM is a great product, but there are some extremely basic use cases that can be PARTIALLY achieved in the CiviCRM system in any one of 10 ways.  The problem is that none of those 10 ways actually achieve the goal of making the use case bulletproof and straightforward.  Unless we're missing something..... please help!

[Donald Lobo]

Sarah:

A few thoughts, comments and clarifications:

1. Your description on the limitations of advanced search and search builder is right on. There have been a few mails on this on the mailing list in the past. We have tried to improve things incrementally the past few releases but do acknowledge that we have a way to go to make it complete.

2. Building a generalized query engine for a relatively complex ERD is not a simple problem. If it was, we would have implemented it already

3. CiviCRM is an open source project. To some extent the success of CiviCRM relies on folks stepping up to "scratch their own itch". We would love for someone to step up and design / build a more generalized / flexible query engine. Since u'll have 2 dedicated IT staff and 4 tech-savvy people, maybe u'll could contribute some time and energy and come up with something that meet your needs, user-friendly and flexible. We could also figure out how to hack the system to make it work for u'll now. If this is an option, please do contact us on IRC / email.

lobo