Add Boolean logic to Advanced Search
It would be nice to be able to search the member database (or any database really) by specifying criteria such as:
Renewal_Due on or after Sept. 16,2008
(MembershipLevel = Gold OR MembershipLevel = Silver)
Right now, I think, I can only choose to AND (i.e., Match ALL) all the criteria or OR (i.e., Match ANY) all the criteria -- no mixing AND's and OR's. This is okay for very simple reports with only two criteria, but often not what is needed for reports with more than 2 filter criteria.
Old design proposal, not working on it yet and can be changed if we start working on it – https://drive.google.com/file/d/0B0f9kMyQqlBsZ3FQOWRiMERRNkk/view?usp=sharing
Brook Soltvedt commented
Query function should allow any / all match to be applied to each criterion.
Example: I am trying to create a list of members who want an email announcement of an event. Query matches any of the criteria Preferred contact method is email only
Preferred contact method is email and postal mail
But now I want to exclude certain members who have indicated they will not be able to attend. I want the first two criteria to be ANY. But I want Last name is not ______ to be an AND query. Otherwise, every member other than the named one will be included.
Michael Hall commented
I would like to see this feature, as it relates to "OR=" logic. Would be helpful for our organization when trying to find several (but not all) of our memberships of certain levels.
Walt Bilofsky commented
If this design can't be scheduled for the near future, here is a small change that will meet the requirements of a number of the posters below. It will also make the full design easier to use.
Currently if a contact or membership field type is checkboxes, you permit searching on any, all or none of the choices. But if the field type is radio button, we can only search on one of the choices.
My suggestion is for search criteria to treat radio button fields the same as check boxes. So although the member has to choose one of the radio button alternatives, we could specify a single search criterion to select several alternatives.
The current design will allow the same thing, but the admin would have to enter a separate criterion for each desired choice.
Brandon Longley commented
This would be beneficial to have in advanced searches.
I needed this today with a search, luckily there was a very simple workaround exporting the list to excel then filtering out the results however it can get messy.
I needed to make a search for Members within an area to apply for funding.
Postcode was a list of ANY postcodes in that district (10 or so)
Membership Level is ACTIVE.
It's a good point, but it's not a simple thing for us to do - advanced search functionality is technically complicated, there a number of performance challenges to it. We will get to this topic in due time.
Doug Miles commented
In my view, the most common clash of AND and OR is when you have OR logic criteria, but also want to exclude Lapsed members (or only include Active ones). Couldn't you just add a check box for all searches saying "Exclude lapsed: X". One small step for a programmer, one big step for admins!
Larry Killer commented
I think this is where my request fits in on the user board, so here it goes!
It would be nice to be able to search fields that are blank.
Example: We have a regions field where members are assigned to one of seven regions. What I need to do on typically a quarterly basis is search for member where the REGION field is Null/Blank. Right now my only options for the Region field are: Is and Is Not. The only work around is to create a saved search where I add seven Region criteria and set them to Is Not and then the name of each region. This works, but it's another saved search where it should be easy enough to run a search where Region is Null.
For now I will keep my saved search, but it would be nice to eventually see a Null search on just about any criteria that is available.
[Deleted User] commented
Yes, please. It is so frustrating the way it is. I would like to me able to search on fields which are blank, in case someone registering left something out. I have made my own codes and would like to search on greater than or less than for those. I used to use the much-maligned Microsoft Works but at least you could create complicated searches, just the way you wanted them.
Ann Gerckens commented
I would like to be able to create a search using "and" or "Or"
Can we please have the ability to use Boolean operators when making queries against the WA database? It is not difficult. I don't want to program an Apollo (or Soyuz) space rocket. I just want to be able to say, "A and (B or C)". Specifically, I want to make a query that will return me all the records where "MembershipStatus is 'Active' AND (PostCode begins with '42' OR '43').
See [19B-1C0D4E8B-0479] How to combine ANDs and ORs in a Query.
Or even "search within results" would be helpful...
I'll add my vote for Boolean logic -
I also think you underestimate your users. Any database should have it. If you think people will be turned off put it separately from the regular advanced search, but it's sorely needed!
Bill Tidball commented
While I appreciate the Boolean search capabilities is now available using the API and believe it will help resolve this problem in some cases, it is not a solutions for narrowing the list of members when attempting to e-mail using the system.
I also agree with other posters, do not underestimate the user base. I believe it is far more confusing to our clients to NOT have a more advanced Boolean search capabilities then having it would be. At least if it was within the system, the user could study examples and work to solve the problem. The way it is today, it looks like a poorly thought out solution because it lacks any way of sub-setting some searches. (examples provided in several other posts)
By the way, our API supports combination of AND and OR in filtering - see http://help.wildapricot.com/display/DOC/Contacts+API+V1+call#ContactsAPIcall-Logicaloperators
I would also very much like to have access to all fields when searching -- e.g., date joined, event attendance (we would like to know how many of our members have ever attended one of our events), date paid (so we can compare membership levels for a date in this year vs. a date in last year), etc.
Steve Veach commented
There appears to be no way to do even the simplest of Boolean searches in the Advanced search interface.
For example find all active members that are faculty, staff, or students of a university:
(Primary email ends with .edu OR Secondary email ends with .edu) AND Member status is Active
Pamela Rawson commented
I would agree that Boolean logic is something that should be standard for every database search. Yesterday, I had to send out multiple copies of the same email to target 70 middle school teachers in different regions of the state. It would have been much easier to conduct one search targeting middle school teachers AND in region 1 OR in region 2 OR in region 3, etc. to find all 70.
Don't underestimate the abilities of your users. Boolean searches have been around for a long time and are not that difficult to use.
Toni Troop commented
Wholeheartedly agree that the current logic for the search function is woefully limited and inadequate. It's probably the single biggest frustration. The and/or/null issues raised above are all fairly basis in most systems. Without this functionality, we have to jump through hoops to get the segmented list we want. Alternatively we're stuck having to create additional fields that we populate and then search on. Please make this a higher priority. Thanks.
Nancy Scanlan commented
If you could at least start with the AND function, it would really be helpfful. We do a lot of direct targeting emails: we have our meetings in a different state each year, some parts of the meeting have an attraction to a subset of our members,.Instead of doing 3 separate member level searches in 4 separate states and combining all 12 searches, it would be a lot easier to just be able to search on level 1 AND 2 AND 3 and leave 4, 5 and 6 blank, PLUS to search on one state AND surrounding states at the same time: such as CA and AZ and WA and NV, rather than a different search for each of these states.
Having AND, OR and NOT (BUT) operations for each particular search criteria is still not something we going to have soon, unfortunately - we are focusing now on other areas (Content management improvements). We will look into this later - this is not an easy change both in terms of development time and designing it in easy to use and understand way (see earlier Dmitri comment on that).
But we probably can update some of our search criteria (like membership levels and status) and allow to choose several options instead of only one (like "any of selected" levels instead of "is" a single level).