Using the Search Builder

Concepts

The Search Builder allows you to create and save complex searches (queries) by combining multiple search fields into search clauses (referred to as criteria sets). Each criteria set can have one or more search fields (criteria). For example:

Find contacts whose primary address is in California AND who are in the Newsletter Subscribers Group.

The search fields within a criteria set are always combined using AND - so the resulting records must meet each and every criteria in the set.

If you need to find records which meet criteria A OR criteria B - you use additional criteria set(s). These are labeled Also include contacts where... in the Search Builder. Building on our first example, we would use two criteria sets to get these results:

Find contacts where the primary address is in California AND who are in the Newsletter Subscribers Group
Also include contacts where the primary address is in Oregon AND who are in the Newsletter Subscribers Group

This example might also be described using parentheses to group the search criteria:
Find contacts where ( primary address is in California AND member of Newsletter Subscribers ) OR ( primary address is in Oregon AND member of Newsletter Subscribers )

For folks who are more used to database query terminology...multiple search criteria in a set are AND'd together. Multiple criteria sets are OR'd together.

Creating a Search Builder Query

Start by deciding which fields and values you want to use as your search criteria and how you want to combine them (AND and OR phrases).

Access the Search Builder tab from the Find Contacts menu
Select the type of record your first criteria belongs to (Individual, Organization, Household, or Contribution are supported in v1.5).
Now select the field you want to search on (for example - State)
If you've picked a location (address, email or phone) field - you will also select the location type. OR...select Primary if you want to search on the contacts' primary location regardless of whether it is Home or Work or...
Select a comparison operator. Select = if you want an exact match. Select < (less than), > (greater than), etc. for numeric or date ranges. Select LIKE if you want a fuzzy text match using wildcards.
Enter the value you are searching for.
Repeat these with another criteria in this "set" - if you want to find contacts who meet BOTH criteria.
If you want to add alternative criteria (...OR contacts who...), then add criteria to the next criteria set - Also include contacts where...
If you need more search criteria rows, click Another search field.
If you need a 3rd (or additional) criteria set - click Also include contacts where... (link) at the bottom of the form.

The columns returned in your search results always include the contact Sort Name PLUS a column for each search criteria you've included in your query. For example, if you're searching on State and Birth Date and Most Important Issue (a custom field) - these are the columns you'll see in your results. The only exceptions are Group(s) and Tag(s) which are currently not included in the results columns.

Special Values and Formats are Required for Some Search Fields

For v1.5, Search Builder requires that you use special values and formats for some types of search fields, including Groups, Tags, States, Countries, yes/no (boolean) fields and multiple-choice custom fields. These requirements are described in the Special Search Criteria section below.

Fuzzy Matches

If you want to search on partial names or other incomplete phrases - use the LIKE operator, and use the % symbol before or after your search term. For example, to find contacts whose last name begins with "Gree":

Record Type	Field	Operator	Value
Individuals	Last Name	LIKE	Gree%

You can take any of the actions with your search results as you can with other CiviCRM search methods. This includes Export, Print, Delete, etc.

Special Search Criteria

Search Builder requires special values when matching on values which are stored as keys in the data. This includes Groups, Tags, States, Countries, yes/no (boolean) fields and multiple-choice custom fields. Specific date formats are also required.

To search by...	Use this value or format...
Group	The Group ID. Find this integer value by checking the gid= value in the Members link for the group (from Manage Groups)
Tag	The Tag ID. Find this integer value by checking the id= value in the Edit link from Administer CiviCRM » Tags
State	Either the full state name (e.g. 'California') OR the state_province ID (e.g. 1004). Find the ID by viewing the HTML source for the State select field while editing a contact.
Country	Either the full exact country name as stored in the CiviCRM database (e.g. 'United States') OR the country ID (e.g. 1228). Find the ID by viewing the HTML source for the Country select field while editing a contact.
Dates	Use YYYYMMDD format. Example: Enter August 18, 2006 as 20060818
Yes/No Checkboxes	This covers fields like DO NOT EMAIL, IS DECEASED, etc. Use a 1 for Yes and a 0 for No
Multiple Choice Custom Fields	Use the Option Value you assigned when creating the Multiple Choice selections. This may not be the same as the displayed value - as the Option Label is displayed in select fields and when viewing the contact record.

You can search for matches on multiple Groups, Tags or other ID values using the IN operator. For example, using the sample data - if I wanted to find contacts who are members of either the Newsletter Subscribers group (ID=1) or the Advisory Board group (ID=3), I could enter:

*Individuals -> Group(s) -> IN -> (1,3)

IMPORTANT: Surround your set of values with parentheses, and do NOT use spaces between the comma-separated values.

Saving a Search Query

If you want to re-use a Search Builder query - you can save it as a Smart Group.

Create and run your search.
From the - more actions - drop-down, select New Smart Group and click Go.
Provide a name for your Smart Group.

You can now re-run this query anytime by selecting your smart group from Manage Groups.