With Release 10.3 we introduced advanced filters in OfficeClip Contact Manager and CRM. I have heard from customers on creating a write up on how to use the advanced filter features for contacts and accounts. CRM data is often more useful when filtered! OfficeClip CRM filters are powerful in that they can express many complex conditions. This article teaches you how to use the filter efficiently.
[more]
Introduction
Figure 1: How to access filters
From the contact list screen (see Figure 1) you can create new filters by clicking on the + icon next to the filter box (see 2 in Figure 1). We see here four contacts on the list screen that we will refer to throughout this tutorial.
Figure 2: The OfficeClip Filter definition screen
The filter definition screen (Figure 2) can be used to create or edit a filter. Every filter should have a unique name (see 1). The scope of the filter (see 2) determines if you want the filter to work only on the data you own or all the data that you can see (based on your permission). You become owner of a contact when you create it or someone else made you the owner. The Fields box (see 3) shows all the fields shown on the list screen (see Figure 1 on how these fields are shown) when the filter is selected. You can also choose to make your filter private or make it visible to everyone in the organization (see 4). The filter condition (see 5) allows you to select the condition on which the filter is created and run. This article will describe various ways you can create conditions and groupings of a filter.
Basic Filters
Let’s see an example filter condition:
Figure 3: A sample filter
Figure 3 shows a sample filter and the result it produced on the list screen.
Let’s say you want to find all the contacts who live in Georgia (GA) OR do not have a work phone OR whose first name contains sk. The first line of the filter shows:
If ANY of these conditions are TRUE
This is called the top level group. It means that the filter will check for all the conditions within this group, and contact matches with any of these conditions will show up in the result. This is the same as OR logic in boolean expressions. In the above example, there are three conditions that belong to this group. They are:
State is GA
Work Phone is not …
First Name contains SK
So the filter will show all the contacts that match any one of these conditions. It is worth noting the second condition has a … which means empty, so it will match all contacts whose Work Phone is not empty. Lets now see the result in Figure 3 on which row matches what part of the filter.
Contacts 1 and 3 in the result set match with the first condition (State is Georgia). Contacts 1, 2 and 4 match with the second condition (Work Phone is not empty) and 2 matches with the third condition (First Name contains SK). So, we see that all contacts (1, 2, 3 and 4) are matched by at least one of the conditions. Because the grouping is ANY, it shows all of them.
Let us now remove one of these conditions and see what happens.
Figure 4: The second condition is removed
In Figure 4, we have removed the second condition (Work Phone is not empty). This filter returns only three contacts. The two conditions we have in this filter are:
State is GA
First Name contains SK
Like before, we see that contacts 1 and 3 are matched by filter condition 1, and contact 2 is matched by filter condition 2. The 4th contact (in Figure 3) is no longer shown in Figure 4 because it doesn’t satisfy any of the filter conditions.
Filter groupings can also be done on FALSE condition of the group.
Figure 5: Testing for false condition in any group
Let’s say you want to find contacts whose state is not Georgia OR the first name does not contain SK.
In this case the filter is evaluated in the same way as before but each individual condition will be tested for false. Let’s walk through the example:
To show the contacts the following two conditions should be false:
State is GA
First Name contains SK
Contact 4 is matched by the filter condition 1 and contacts 1 and 2 are matched by the filter condition 2.
Filter grouping can also be done using the ALL grouping instead of ANY. In this case, only contacts that satisfy all the conditions in the group will be shown in the result. For example, let’s search for all contacts in Georgia who have a work phone:
Figure 6: Filter using ALL grouping
In Figure 6, there are two conditions grouped by ALL. They are:
State is GA
Work Phone is not …
Now go back to Figure 1 and you will see that only contact 1 satisfies both these conditions.
Complex Filters
So far we have discussed simple filter creation. Oftentimes you want to create filters using complex conditions. When you want to create a complex filter, you have to accept trade-offs between power and simplicity. Let’s say you are trying to find all the contacts where:
(The Company Name contains OfficeClip and located in State is Georgia) OR (The Company Name contains the word Test and located in New York)
Let’s see how to write such a filter.
Figure 7: Subgrouping in a filter
First we create two subgroups for each sets of conditions (see Figure 7), then we combine them in the main group.
To create a subgroup filter, click on the + icon and select the Conditions Combination option as shown in Figure 7A.
Figure 7A: Creating Subgroups
Let’s construct them individually:
SubGroup 1: The Company Name contains OfficeClip and located in State is Georgia
Filter:
If ALL of these conditions are TRUE
Company Name contains OfficeClip
State is GA
Matches:
Contact Record 1
SubGroup 2: The Company Name contains Test and located in State is New York
Filter:
If ALL of these conditions are TRUE
Company Name contains Test
State is NY
Matches:
Contact Record 4
Because we want contacts where any one of the subgroups is true, we can use the top level grouping as ANY. So, we see both contact records 1 and 4 in the list.
Now let’s see what happens if, in the above example, we change the top level grouping to ALL and subgroups to ANY. It means that we could be searching for something like:
Search for all contacts where (Company Name contains OfficeClip OR located in Georgia) AND (Company Name contains Test OR located in New York). This example may sound complicated, but we have shown it here to illustrate how complex filters are constructed.
Figure 8: Subgrouping with ANY and ALL reversed
Let’s construct them individually:
SubGroup 1: The Company Name contains OfficeClip and located in State is Georgia
Filter:
If ANY of these conditions are TRUE
Company Name contains OfficeClip
State is GA
Matches:
Contact Record 1, 2 and 3
SubGroup 2: The Company Name contains Test and located in State is New York
Filter:
If ANY of these conditions are TRUE
Company Name contains Test
State is NY
Matches:
Contact Record 3 and 4
Because we want contacts where both of them is true, we can use the top level grouping as ALL. So, we see contact record 3 in the list which satisfies both groups.
Filter using List
If you are trying to match contacts with particular values and you know the exact values you are looking for, you can use the is one of predicate as shown in Figure 9.
Figure 9: Creating filter with is one of predicate
Here you can select the is one of predicate and separate values with a comma. In Figure 9, we see that it only shows contacts 1 and 3, with first names as Scott and John respectively.
Filter Using Date
The OfficeClip filter can also create powerful conditions based on Date. Figure 10 shows a list of all our contacts with the date they were created.
Figure 10: List of all contacts with created date
Using this example we will create some date-based filters.
Figure 11: Testing date based on equals and greater logic
In Figure 11, we show a filter to extract contact records where the date is Nov 27, 2014 or higher. We see that only two out of four (see Figure 10) contacts match and are shown in the result.
Sometimes, we may not know the exact date and we need to search by relative date.
Figure 12: Relative date search
In Figure 12, we search for all contacts created in the last year.
Oftentimes, we need to find contacts based on month in any year. For example, we may want to find contacts which has been entered in the current month.
Figure 13: Filter for finding month in any year
In Figure 13 we show all the contacts created in the current month of any year.
Pdf Document for this tutorial: How-to-use-CRM-Filters-in-OfficeClip.pdf (765.1KB)
Filter image courtesy Wikimedia, creative commons attribution
SK Dutta is a software architect and creator of OfficeClip Suite of products. He loves to design and develop software that makes people do their job better and more fun. He always explores ways to improve productivity for small businesses. He is also an avid reader in many areas, including psychology, productivity, and business.