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.
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.
This tutorial is a continuation of Part 4 of the SQL Wizard tutorials, so we will start with the SQL Wizard open in the template created in SQL - Part 4: Filtering with Parameter Values. If you don’t know how to open the SQL Wizard yet, you simply select the ForEach tag, and click the Wizard button in the AutoTag ribbon.
First, we’re going to drag a new column to the Columns box. We’re going to be checking if the Products are Discontinued or not so bring in that column.
Using the same strategy as in SQL - 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.
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.
This is the same strategy as in step 2, and in Part 3 of the SQL 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
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.
Check the right panel of the SQL Wizard. You’ll notice every piece of data shown meets the criteria mentioned several times in this document.
You have completed this tutorial. We recommend taking a look at the SQL - Part 6: Joining Tables tutorial next!