One of Outlook’s (fairly well) hidden secrets is the SQL Tab on the Filter dialog in the Outlook 2002 and 2003 Custom View Organizer. This article explores how to use the SQL Tab when customizing Outlook Views to create advanced queries or filters in Outlook to group, sort and filter your Outlook data depending on the task at hand.

Note: this article does not apply if you’re using Outlook 2000!

We look at:

• Defining the Problem - The Task at Hand
• Importing the Sample Data
• Identifying the Filter Conditions
• Boolean Algebra
• Defining the Query in Boolean terms
• Creating and Modifying the View
  1. Creating the View
  2. Setting the Fields for the View
  3. Defining the Filter for the View
  4. Modifying the Filter using the SQL Tab
• Summary
• MX-Contact: Managing Contact Lists inside Outlook / Exchange
• About OutlookWise and ExchangeWise

Note: If you are not familiar with the basics of creating Outlook Views, you might like to review the following article first:

Profiling Contacts using the Outlook Forms Designer - 02

Suppose I have the following list of Contacts in my Personal Contacts folder that represent the customers and prospects I’m currently dealing with. (For details on how to import this set of data, if you’d like to try the exercises described, please refer to Importing the Sample Data)

I want to get a listing of just those contacts that are marketing decision makers based on the West Coast (of the United States) say for the purposes of sending them a newsletter or direct mailing.

In order to do this, I need to create a filtered view in Outlook. (A view is a sub-set of my data in a particular folder that is grouped, sorted, and filtered according to specified criteria). However, in certain cases (such as the task for this exercise), the criteria for the filter conditions are such that they cannot be set up using the basic filter dialogs, and require they be entered via the SQL Tab.

My first challenge is in Identifying the Filter Conditions that equate to these specific criteria.

The exercises in this article are based on a small set of Outlook sample contacts. If you wish to actually test the principles discussed using this sample data, you may import the sample CSV file, a link to which is given below. There are only 14 contacts in this database so they can easily be deleted again once you are done.

Sample CSV File - Outlook_Contacts.csv

A CSV (Comma-Separated-Variable) file is a data file where the fields are separated by a comma. By default if you open a CSV file and you have Microsoft Excel installed, the file should open in Excel, as shown below:

For details on importing this file, refer to the following document:

OutlookWise Tutorial – Importing Outlook Data

Once you have imported the file, you should have the following contacts showing in your Contacts folder. Alternatively, you may want to create a new sub-folder below your Contacts folder and import these contacts into this sub-folder, so that this data does not interfere with your “working” contacts list.

The task defined for us is to get a listing of just those contacts who are marketing decision makers on the West Coast (of the United States).

My first challenge is to identify the fields in my Outlook Contacts folder that equate to these individual criteria.

So in this example how would I identify a “marketing decision maker”? This might be anybody that has the word “Marketing” in their Job Title, but is also a Manager, Director, Vice President, etc. I could single out each of these Job Titles individually, so as to include Marketing Manager, Marketing Director, Marketing Vice President, Vice President – Marketing, etc. However, if I look at my data carefully I notice that I can just exclude “Assistant” in this particular case, as Marketing Assistants would not typically be decision makers. So my first condition would be:

Job Title contains the word “Marketing”

but

Job Title does not contain the word “Assistant”.

Notice we use the “Contains” operator so that we include those contacts with “Marketing” anywhere in the Job Title, such as Marketing Manager, Vice President - Marketing, etc.

The next condition is contacts “located on the West Coast” (of the United States). So here I would use the State field to “home in” on the required contacts. So the conditions would be:

State = “WA”

State = “OR”

State = “CA”

However, joining these particular conditions together requires an elementary knowledge of what is known as Boolean Algebra (AND/OR and NOT Conditions).

In order to really understand how to create complex queries, we need a basic knowledge of Boolean Algebra. But a word of advice to those of you who are not that mathematically oriented and who shudder at the thought of anything related to Algebra – don’t panic. Boolean Algebra is not that difficult to grasp and makes perfect sense once you’ve got the basics.

Boolean Algebra is simply a way of rephrasing a complicated logical statement into a series of simpler AND, OR and NOT conditions without changing its meaning. The rules of Boolean Algebra are straight-forward, and can be applied to any logical expression. The notational system was developed by the English mathematician George Boole to permit an algebraic manipulation of logical statements. Today, all computers employ Boole's logic system - using microchips that contain thousands of tiny electronic switches arranged into logical ‘gates’ that produce predictable and reliable conclusions.

For the purposes of trying to understand the 3 Boolean operators, namely AND, OR and NOT, let’s look at 2 simple sets of data, namely

Blue Set (Dark Shading): Contacts whose Job Title = (is exactly) Marketing Manager

Yellow Set (Light Shading): Contacts whose Country = (is exactly) United States of America

Boolean AND

When the AND operator is used to join the 2 sets, the results retrieved will contain both Blue and Yellow information. So in this case you would only get contacts that are Marketing Managers AND have United States of America as their country. This would be the Green set shown above.

Boolean OR

When the OR operator is used to join the 2 sets, the results retrieved will contain either Blue, or Yellow, or Blue and Yellow information together. So in this case you would get contacts that are Marketing Managers or have United States of America as their country, or both. This would be the entire set of Blue, Green and Yellow shown above.

Boolean NOT

When 2 conditions are combined with a NOT operator, the results retrieved will contain only Blue exclusively. So in this case you would get contacts that are Marketing Managers but do not have United States of America as their country. This would be the Blue set above.

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.

Now that I have identified the fields that equate to the criteria I have defined, I am ready to create a View called “Marketing Decision Makers on the West Coast”. We go through the following steps:

Creating the View

Setting the Fields for the View

Defining the Filter for the View

Modifying the Filter using the SQL Tab

.         Click View -> Arrange by -> Current View -> Define Views

2.         The Current View Organizer screen will be displayed:

3.         Click New to create a new View:

4.         Enter the View Name “Marketing Decision Makers on the West Coast”. (Leave the Type of view as Table). Click OK. You will then get the Customize View dialog:

1.         In this example I’m working with 2 fields that do not ordinarily appear in the default Views, namely Job Title and State. So it would be useful to see these fields in the View. To do this click on the Fields button at the top of the Customize View dialog:

2.        The Show Fields Dialog will be displayed:

3.    From the Frequently-used fields select Job Title and State in turn and click Add-> to move the fields to the right hand window showing the fields that will appear in the View.

4.     Use Move Up and Move Down to reposition these fields so that they will display where you’d like them to:

5.         Click OK to return to the Customize View dialog.

1.         Notice that on the Customize View screen the Filter… is set to Off, meaning this View by default would show all Contacts in the list

2.         Click on Filter, and go to the Advanced Tab:

3.         Before using the SQL Tab it is often good to start with the Advanced Tab because this is easier to use in terms of defining our criteria. Most of the time we can just use the SQL Tab to modify the query already created using the Advanced Tab.

4.     We now enter our first 2 conditions, namely Job Title contains “Marketing”, and Job Title doesn't contain “Assistant” (we will modify this later in the SQL Tab). We select Job Title either from Frequently-used fields or All Contact fields in the list dropped down by the Field button. Click Add to add the condition to the list.

5.         We then do the same for our other conditions, namely State = “WA” and State = “OR” and State = “CA”. We select State again either from Frequently-used fields or All Contact fields in the list dropped down by the Field button. Click Add to add each condition to the list.

6.         The last thing we need to do is now edit the query using the SQL Tab.

1.         If we click on the SQL Tab we should see the following:

2.         The SQL tab might look a bit intimidating at first, because the fields have Exchange DASL complex schema names rather than the familiar Outlook field names. However, don’t be too concerned; the SQL queries are relatively easy to modify. While you can create power filters if you know some SQL syntax, you don't need to know a lot about SQL to create filters not possible using the other filter tabs.

3.         Note that Outlook has defaulted the second condition to an OR condition because this is the usual scenario in the case of fields that are the same. However in our case this will not work. Edit the criteria by clicking on Edit these criteria directly and change the expression to read:

4.         We then click OK to return to the Customize View screen. Then click OK to return to the Custom View Organizer, and Apply View to save and activate the view:

5.         All the Marketing Decision Makers that are located on the West Coast are displayed in the View – mission accomplished.

The SQL Tab is a powerful tool for the Outlook users who need to be able to derive listings of sub-sets of their Outlook data based on more complex queries than just simple AND/OR conditions.

Most of the time one can create the query using the Advanced Tab as we always do and then modify the SQL query that has been created, rather than trying to write the SQL query from scratch.

If you’re looking for an application to assist you with managing a contact list of some kind, whether it be customers, prospects, members, suppliers or whatever, check out MX-Contact. MX-Contact is a CRM, Contact Management and Sales Automation package that runs inside Microsoft Outlook 2000, 2002 or 2003. The system utilises all the standard functionality of Outlook but provides many additional features that transform Outlook into a powerful Contact Management and CRM system.

www.mxcontact.com

OutlookWise is both a web site and newsletter aimed at keeping Microsoft® Outlook® users up-to-date with news, information and articles of interest on Outlook and Exchange Server, as well as reviews of add-on products and utilities, all geared to enhancing your knowledge of Outlook and increasing your productivity.

If you’ve found this article useful, please subscribe to the monthly newsletter.

If you would like to contribute an idea or article, or tell us about an exciting product or utility that complements Outlook and/or Exchange Server, please feel free to e-mail us.

OutlookWise is a service to the Outlook community from ExchangeWise, which is a specialist software company focusing on the development and marketing of applications and utilities that enhance the functionality of Microsoft Outlook and Exchange Server.

OutlookWise is edited by Brian Drury, founder of ExchangeWise, and the architect of MX-Contact, a CRM, Contact Management and Sales Automation System for Microsoft Outlook. Brian has over 20 years experience in the IT industry and has focused on CRM, Messaging and Collaboration systems for the last 14 years.