Last week we learned how to tell when the basic Vendor Group filtering wouldn’t work for us. This week we will learn how to write a filter statement that WILL satisfy our filtering needs providing you with greater reporting flexibility.
When we ended last week we realized that we needed to use the Expression option in the Condition type dropdown. After we selected the Expression Condition type we noticed that the Condition box below it was active. The next step is to write our expression in the Condition box but before inputting anything it’s important to understand how to write this more advanced expression.
For ease of explaining the first part of our expression will use the two state options. Because we want to include all Vendors in either New York or Pennsylvania we need to write an expression that says: State equals New York OR State equals Pennsylvania. Since New York is filter row 1 and Pennsylvania is filter row 2 that statement will be written as follows: 1 OR 2. Because we want this condition to be evaluated first, we will group this statement in brackets.
The next part of our statement is to include only active Vendors. After the first part of our filter is run we will have only the Vendors from the two states specified but now we want to further filter those results to keep only the active Vendors. Since we want Active Vendors that are also in those two states we need to have both of our conditions satisfied, which is an AND operator. As stated above the first portion of our statement will be grouped in brackets so that it is applied first. Next we will add the AND statement outside of the brackets as it happens second. We chose to write this statement written as (1 OR 2) AND 3 as shown below but in actual fact it would return the same set of vendors if written 3 AND (1 OR 2).
You might be wondering why wouldn’t we write our statement as 1 OR (2 AND 3)? Let’s consider that statement for a minute. The brackets tell us to first evaluate the 2 AND 3 portion of the equation. Written out it is telling us to find all the records where State equals Pennsylvania and Status is Active. Ok, that sounds good, we want all Active Vendors in Pennsylvania! We’re right on track. Now let’s add the rest of the filter to this. The first part is read as State equals New York OR is in the group we made in the last step. The last step gave us all the active Vendors in Pennsylvania so now we will add to that all the Vendors in New York. The problem here is that the group of vendors in New York will include everyone, no matter which state! That’s incorrect! We only want the vendors who are Active in those two states!
Although advanced group filtering may seem a bit daunting from the start it is a powerful tool! Play around and learn how to make it work for your organization!
