What's Missing in Advanced Search?

Published
2012-07-08 14:42
Written by

 

The Progressive Technology Project has compiled a list of scenarios related to doing searches from our groups’  support requests. We would like to put these forward as suggestions/modifications for Advanced Search or custom searches. Perhaps some have already been solved and are lurking quietly as custom searches that we just don’t have on our radar yet. Please share comments and reactions to these - have any of your clients needed something similar?


In case you don't know us, PTP has trained and supports over fifty community organizing groups in the US using CiviCRM, in addition to our other programmatic work. The version of CiviCRM we support is called PowerBase - it includes the CiviEngage module and CiviCampaign component that facilitate activities that all of these organizing groups do regularly to build people power.

I’ve packaged each need below as an actual scenario so you can imagine it immediately, but many can be abstracted to be a bit more general (for example, all dropdown menus in Advanced Search, or all contact info with a primary checkbox).

A note about Search Builder: some of these are possible in Search Builder, but Search Builder has a significantly steeper learning curve. It also may not be maintained forever, so ensuring that functionality comes into Advanced Search that only Search Builder now has is crucial.

 

  1. Find all individuals who have a primary language of Mandarin or Cantonese (or all the English speakers of the world with different kinds of English).

Notes: I would have to create two smart groups and then do another search to OR the two groups together. The problem seems to be that the data type “dropdown” remains a dropdown in Advanced Search. On data entry, it should be a dropdown. But in Advanced Search, it should change from a dropdown to a checkbox (multiselect) field. We think the fix is to make all dropdowns operate in the way privacy options operate or with the uncheck to match any/uncheck to match all.


  1. Search only on cell numbers to use for a live demonstration/rally/event (whether or not primary).

Notes: An organization group has a standard way of entering phone numbers and email addresses. They put accurate information in the database, which means that the primary phone numbers/email addresses will be different from person to person (some want to be contacted at their home number, some at their cell, some at their work email, etc.). Only pulling primary numbers gets me all the best ways to contact people. But I’ve organized an event and I need the cell phone numbers of all the folks I want to turn out for the day of the event. I need to be able to do an advanced search and pull up only mobile phones, whether they are folks’ primary number or not, so I can see what data is missing and contact them for the cell numbers.


  1. ID everyone in the database without an email. (NULL value in email address)

Notes: I need to clean up my mailing lists. I don’t want to have an inflated list full of individuals who don’t even have an email address. That could throw off the mailing report. I need to search on contacts with null email addresses and remove them from all groups that are mailing lists.


  1. Search everyone who is in two groups by being able to AND the groups together.

  1. Find a single external/internal ID aka CiviCRM ID.

Notes: I’m doing an import with a bunch of data. I import successfully matching on the internal/external IDs. A few records have errors, so I need to investigate those contact records. I need to search on the specific unique identifiers.

Another use case is matching on the Voter Activation Network ID - something that’s key for many organizing groups.


  1. Find a single phone number.

  1. Find all contacts with Twitter/Facebook accounts.

Notes: I want to see all the contacts with a Twitter/Facebook account and quickly send them an email to ask them to post to social media about something inflammatory a politician just said. We need to be able to search on all websites/instant messengers of a certain type.


  1. Find all email addresses on hold for a given bounce type. Delete them and/or batch update with secondary emails.

Notes: When I send a big mailing, a bunch of emails get put on hold. I can unhold all those email addresses en masse by doing an advanced search of all emails on hold. The bounce report from a given mailing actually could help me differentiate between the bounce reasons which would let me unhold only the ones that have a temporary bounce type. I should be able to do a search on the bounce type and optionally delete all those emails that are bounced for the following reasons:

Invalid, Inactive but not for Quota, Relay, Syntax, Host

Next, we’d need to be able to batch update all the primary email addresses that are invalid and make the secondary email primary (or do a fix via import).



  1. Find incomplete street addresses.

Notes: I need to find incomplete addresses to update them or decide not to send my direct mailing to them so I don’t waste money. I want to do a search on all individuals/orgs who Street Address, City, State, or Zip is Null. It would be ideal to do this in one single search where I can OR each of the arguments.


  1. I need to search on all records modified/added by anyone BUT Jason, or by everyone except members of a group. In other words, I want to see  all records added/modified by external users.

I would like to see all the records that have been added by themselves via our email sign-up page. If you set up a profile right, listings get added to a group. But if you make a mistake, there is no way of seeing how many people are adding themselves regularly to your database.

 

Comments

Would be nice to be able to search in Advanced Search for everyone who gave more or less than a given total for a given period.

Anonymous (not verified)
2012-07-10 - 00:42

One thing that advanced search doesn't (appear) to do easily is searches that exclude groups e.g.

"Everyone who is in group A (members last year) but not group B (members this year)"

"Everyone who is in group A (members last year) or C (came to the open day) but who aren't now in group B (members this year)"

"Everyone with Tag A but not Tag B" 

The first search is easy enough in CiviMail, but the advanced search and reporting doesn't (appear) to. All of these can, in theory be done in search builder but that isn't approachable to most of my users.

have you looked at the include/exclude custom search? it does exactly what you describe.

Some great suggestions there.

I'd like the ability to create proper boolean searches (AND, OR, NOT). Ideally by being able to string together several advanced searches whithout the need to create intermediary groups.Search builder does some of this but has been depreciated and  requires the user to know the ID of anything they are searching for.

Thanks for the comment. Do you have any specific scenarios related to these boolean searches? We'd like to build a case for having these in advanced search. 

I would like to see a way to search on partial date values for example:

Everyone who was born in October (any year)

Everyone who was born the week of April 1 - 7 (any year)

Everyone who participated in a June event (any year, or last 5 years)

Other date-related searches:

Everyone who is between 7 and 10 years old

Everyone who is at least 18 years old

Better handling of relationship info within searches:

All children of members with a membership type of "young family"

All parents of paricipants in the summer camp programs (camp is set up as an event)

 

 

1. I think what most people really want is a

  • generalized
  • flexible
  • extensible

query builder. I dont think a UI only query builder will satisfy everyone

2. The above is available for Civi + Drupal7 via Views3 which meets all 3 of the above requirements. Yes this is a drupal specific solution for now, but lets focus on the solution rather than the availability across CMS'es :)

3. I think the biggest feature missing from the Views integration, is the ability to take "bulk" actions across one or more contacts. What would be really cool would be to integrate it with something like VBO: http://dharmatech.org/ideas/2012/vbo-actions-civicrm-custom-fields or maybe even CBO: CiviCRM Bulk Operations :)

So who's willing to investigate and help prototype/build integration with Views and all of CiviCRM actions :)

lobo

Right now I can search for everyone who has a specific relationship (for example Primary Contact)  to an organization or a group of organizations, but I'd like to be able to see everyone who has ANY relationship to an organization or group or organizations, so that I could then take advantage of the bulk actions (i.e. I want to send an email to all contacts from a organization or group of organizations).