Now that we know the basics of Boolean Algebra, let’s have another look at our more complex expression. If we want to test all contacts against the expression

Marketing Decision Makers

This equates to the following 2 conditions, namely:

Job Title contains “Marketing”

and:

Job Title does not contain “Assistant”

Because both conditions must be true for a contact person to be included in the list, they must be joined by the AND operator. However because the second condition is a negative we must use the AND NOT operator, as in:

Now let’s look at the problem of the State field having several different values, namely:

State = “WA”

State = “OR”

State = “CA”

Because any one of these conditions must be true for a contact person to be included in the list, this is an OR Condition (Either State must equal “WA” OR State must equal “OR” OR State must equal “CA” for a contact to be listed).

Now we look at the complete set of criteria for a record to be included in the listing of Marketing decision makers in the US.

The condition is:

Job Title contains “Marketing” AND NOT Job Title contains “Assistant” AND (State = “WA” OR State = “OR” OR State = “CA”)

Another important aspect of Boolean Algebra comes into play here, namely “Nested Conditions”.

We can think of this condition as:

(Marketing Decision Makers) AND (lives on the West Coast)

So wherever you have multiple conditions that must be evaluated together before being combined with another set of data, you need to enclose these conditions in brackets. In this case, given the fact that any contact who is a marketing decision maker AND who is living on the West Coast (his or her State is either WA, OR, or CA) must be included in the list means that the 3 “State” OR conditions must be evaluated together first and then the result ANDed with the Job Title condition. So these 3 OR conditions must be nested inside brackets.

You’ve probably figured out by now that you cannot enter this filter using only the Advanced Tab of the Filter dialog that you are already familiar with. This is where the SQL Tab comes in.

We're now ready to set about creating and modifying the View.