This month, we’re focusing on a tool in CiviCRM that doesn’t get the love it deserves, in part because it can be a bit tricky to use: the Search Builder.
In many situations, the Advanced Search is the most flexible search tool in your toolbox, since it offers access to the most fields, gives you the option to show results in a variety of formats, and includes the most flexibility for exporting data. However, there are a few important things you cannot do even with the Advanced Search - for example, searching for contacts who do not have a particular characteristic, or searching for contacts who meet either one criterion or another but not necessarily both.
Here’s a cookbook with some ‘recipes’ for using Search Builder.
Searching for What a Contact Is Not
Imagine you want to search for contacts who have never registered for an event in your system... the Search Builder is your best means of doing that. Take a look at this screenshot.
Searching for contacts where participant ID is null simply means that we’re searching for people who have no participant records in CiviCRM at all. You could do a similar search for contacts who have never donated or made any payments in CiviCRM by changing out the search above to select “Contribution” in the first dropdown, “Contribution ID” in the second, and “Is Null” in the third.
Note: The Search Builder doesn’t automatically exclude contacts who are in the trash, so it’s important to remember to include that exclusion in your search (the “Contact is in Trash does not equal Yes” option shown above).
Searching for Either/Or Options
Perhaps you want to search for contacts who have either never donated or never registered for an event. By using the Search Builder’s “also include contacts where” function, you can do this easily. See below:
This will give you search results of contacts who meet one set of the criteria but not necessarily both.
Using Other Search Options
In both of the previous examples, we’ve searched for null values, so you already know that’s possible with the Search Builder. In many cases, searching for “is empty” will yield the same results as “is null” but it’s worth experimenting with both.
The Search Builder also includes the ability to search by “in” and “not in”, and “like” and “not like” (searching for a specific string of text, for example); several different mathematical comparisons; and - if you’re really bold and willing to stretch yourself - “regex” (regular expressions). From what I’ve seen, only the simplest regular expressions will work in the search form, but I’ve successfully used regular expressions to find invalid email addresses - just to give you one idea about what they can do.
Are you interested in learning more? Have questions about any of CiviCRM’s search functions? We’d be glad to talk with you about them - just email your support email address.