Having¶
Having is used to filter the data in the result set. Unlike Where, which filters data before it is returned from the database, Having filters the results displayed in the table. This allows filtering of aggregate data or transforming field values.
Description¶
You have already built a search results set based on your initial Entity selection and additional Entity selections using With/Without. We can then use Where and Having to further filter and manipulate that results set. You will find both these options in the “Filter Conditions” tab at left. You will note a significant difference in the fields available in Where vs Having. In Where, you can select any field that is in the CiviCRM database. In Having, you can only select fields that you have included in the results.
Once you have chosen the result field you wish to filter on, you will need to select your operator and comparison values.
In general, the operators are the same as the similarly-named SQL operators. Here are a few tips: * Matches Pattern will match a regular expressions pattern. * Is Like matches a complete field, so you usually want to use it with wildcards if you want to find parts of the field, like %search term%. * Contains is useful for searching in serialized fields (fields that store multiple values together, such as Contact Subtype which could be Parent, Volunteer, or Staff). It matches only a full value like Parent, not parts of a value.
Negated operators such as Not Like, ≠, or Doesn't Contain won't match NULL fields that have no value. They only match fields that have a value and the value doesn't match the criteria. If you want NULL fields as well, you can search for ≠searchterm or Is Empty.
The example below filters the displayed results to show only results where the phone number matches a regular expression describing a particular format.
By reversing it to show results that do not match the pattern we can find records where we may need to fix a phone number.
While ‘glob-matching’ (using % or *) can get you a long way, regular expressions are more flexible and powerful and are one of the most powerful tools available for filtering results of any kind. There are many good tutorials online.
Although most often you will filter against a value you provide, you can also compare one field to another field. This example will display rows where the display name and the email address are the same.
