OData - Part 5: Complex Filters


To combine logical operators to create a complex filter set, while creating a list of all products that need to be reordered AND are not discontinued AND whose name starts with G OR whose name starts with C.

Background Information

Suppose the character Spock from the Star Trek series asks you to filter a set of data. Logical beings such as the Vulcans can’t help but user logical operators from time to time, so either you’re on your own, OR you can read this tutorial AND learn how to create complex filters using the logical operators: AND and OR (AKA in AutoTag “all” conditions, and “any” condition). It is also pertinent to know that many different data types can be compared, even dates and times.


Inserting a Tag, ForEach Tag, OData - Part 1: Basic Selection, OData - Part 3: Filtering with Static Values, OData - Part 4: Filtering with Variable Values

Starting Template

This tutorial is a continuation of Part 4 of the OData Wizard tutorials, so we will start with the OData Wizard open in the template created in OData - Part 4: Filtering with Variable Values. If you don’t know how to open the OData Wizard yet, you simply select the ForEach tag, and click the Wizard button in the AutoTag ribbon.


1. Drag Discontinued property in

First, we’re going to drag a new property to the Select box. We’re going to be checking if the Products are Discontinued or not so bring in that property.

Show Me How!

2. Add a filter such that Products.Discontinued is equal to False

Using the same strategy as in OData - Part 3: Filtering with Static Values, add a filter to test this condition: if Products.Discontinued is equal to False.

With the other filter already in place from Part 4, the two filters work together such that all returned results must meet both conditions. This is indicated by the all in the statement “where all of the following conditions are true” In other words, the products must be under the reorder level AND not be discontinued.

Show Me How!

3. Add a group where any condition is true

Now we’ll add another group by clicking to add a group. This is similar to using parentheses in a mathematical expression. We are saying A AND (B OR C) so A must be true, and either B or C must be true as well. In other words, any of B or C must be true. In this case, B and C will be set in steps 4 and 5 respectively.

Show Me How!

4. Add a filter such that Products.ProductName starts with G

This is the same strategy as in step 2, and in Part 3 of the OData tutorials again. Add a filter, set the left hand side to Products.ProductName, set the condition to starts with (which only applies to strings) and set the right hand side to G

Show Me How!

5. Add a filter such that Products.ProductName starts with C

See step 4. This final condition makes the entire expression look like X AND A AND (B OR C). The conditions made in step 4 and this step are mutually exclusive, so they won’t ever be (and don’t have to be) true at the same time, only one or the other has to be true.

Show Me How!

6. View returned results

Check the right panel of the OData Wizard. You’ll notice every piece of data shown meets the criteria mentioned several times in this document.

Show Me How!

Quiz Yourself!

How many levels of groups can you create in the OData Wizard filtering box?
Only 1 as shown in this tutorial
As many levels as I have conditions to fill them with.
Unlimited. I can just keep adding empty groups and they will stay there when I open the OData Wizard again
About 5.
True/False: Filters can test any data type
Yes. OData Wizard is all powerful. It can even compare files, pictures, and DNA.
Yes, with obvious limitations. Pictures cannot be compared, and with dates and times, it depends on how they are stored in the database.
No. Only numbers and limited operations on text.
No. Only numbers


You have completed this tutorial. We recommend taking a look at the Selection Variables tutorial next!