Filters 101 Filters 101
A database filter enables the user to extract information from database file(s) that are to be printed on the report. The ability to use filters in database programs has always been one of the strongest features of database applications (dBase, Foxpro, Access, etc).
Filters do not change any information contained in database file(s); they do not copy information to another file either. A filter simply "hides" information that is undesired, which explains why they are called "Filters". No matter how many times a database filter is used or created, it is guaranteed that they will NEVER harm database information.
A filter relies on 3 pieces of information to work:
A field name from the database
An operator symbol
The text string or number to compare the database field against
The database field name is defined in the the database structure, click HERE for more in-depth information. The Dictionary window allows the user to choose the database field from a list of every database field, making this step very painless to complete. For example, a database field is named "State"; it is 2 positions long, and contains only text ("az", "ca", "tx", etc) abbreviations of the 50 states of the United States.
An operator symbol tells the FormMaker what to do with the database field that has been chosen from the dictionary. Again, the Dictionary helps by displaying a list of every operator that is available for building filters. An operator symbol can be selected based upon the type of information contained in the database file. Some operators are used only on text (such as "="); some are only used on numeric information (such as ">", "<"). For this example we have chosen a text field, so we will use the equal sign for the operator symbol.
The last piece of the filter is one provided by the user; only the user knows what information is to be extracted from the database. For this example let's assume we want to print a report of only those customers living in Texas; the abbreviation for Texas is "tx", so we will use "tx" as the last piece of the filter.
The example filter resembles this:
INV:State = 'tx'
This is a very simple filter, but clearly it shows all three filter components. Let's modify the filter to use a numeric field and a numeric operator. Let's assume that we want to print a report of all customers who have at least a $5000 line of credit with our company. A sample filter would resemble this:
CUS:LOC > 5000
In part 2 some simple filters were shown to help understand the components of a database filter. A database filter can be very simple, or fairly complex - it depends on the type of report that is to be created.
In it's most simple form database filters can be used for three different situations:
Extracting information based on text
Extracting information using numbers
Extracting information based on a logical condition
Here are some situation descriptions and a simple database filter for each:
Customers who drive a BMW
CUS:CarType = 'bmw'
Products made by MierWater
ITM:Company = 'mierwater
Items with an onhand quantity greater than 20
ITM:QtyOnhand > 20
Children who are too young to be in Kindergarten
Nam:Age < 7
Customers who have pets
NAM:Pets = 1
Items with no detailed information available
ITM:Detailed = 0
The example filters above are very simple in structure, but some interesting details quickly become apparent:
The first two filters for Text-type filters require a quote symbol (') before and after the text string that is being compared against the database field. The next two filters for Integer-type filters don't use a quote symbol; that would make them a text string. The last two filters for Logical-type filters use either a 0 (False) or 1 (True) to indicate true or false. This is because the information for this database field is stored as a one position integer in the database. For those users with dBase experience this is quite different than what dBase stores a value of true as "Y" or "T", and a negative value as "N" or "F"
Previously, some very simple filters were displayed. While structurally correct, they could use some modification to make them 100% reliable. The user never knows exactly how database information is stored; some database programs have very strict safeguards incorporated in them to ensure data is highly structured. Even then it is better to think defensively and create database filters that will work correctly in any situation.
Let us assume that a database program tracks customer sales. The data entry screen allows the user to type in the customer name, their address, etc. Naturally the customer information is text information (A-Z, a-z); let us assume that the boss wants a report printing all customers living in Burbank; a simple filter should work:
CUS:City = 'Burbank'
The filter above looks correct, and would work IF the data entry clerk ALWAYS typed in the city 'Burbank' the same way every time. If the CapsLock key was turned on and the clerk instead typed in 'BURBANK' the filter wouldn't work. That is because the comparison of the database field CUS:CITY must EXACTLY match the text string 'Burbank'!
There are several ways of fixing this common problem. Here is the long way of solving the problem:
CUS:City = 'Burbank' or CUS:City = 'BUrbank' or CUS:City = 'BURbank' or CUS:City = 'BURBank' or CUS:City = 'BURBAnk' or CUS:City = 'BURBANk' or CUS:City = 'BURBANK'
By using the functions UPPER() or LOWER() the filter can be defined like this instead:
upper(CUS:City) = 'BURBANK'
-or-
lower(CUS:City) = 'burbank'
Either way is guaranteed to work 100% of the time. The operational speed of the filter will also increase by using the upper() or lower() functions instead of using the very long filter example. The important thing to consider here is this - never take for granted that database information will be typed in exactly as it should be.
As mentioned before, database filters can be very simple or complex; it depends on the situation. To accommodate this situation, the database language has created what is called BOOLEAN OPERATORS. These are used to "join" together multiple database filter conditions (also known as Expressions). It is important to know how to use a BOOLEAN OPERATOR properly to achieve the desired result.
There are only a handful of BOOLEAN OPERATORS; only four of these are used to create complex filter conditions. These are
AND
OR
NOT
XOR
Let's use some example situations and display what a sample filter for each would be:
Customers from Texas that make over $50000 a year
upper(CUS:State) = 'TX' AND CUS:Income > 49999
People who like ice cream or truffles
CUS:IceCream = 1 OR CUS:Truffles = 1
Cars that can be any color except orange
upper(INV:Color) NOT = 'BLACK'
Notice that every time we are comparing a text string (such as the state field) the function UPPER() is used. This is a good practice to get into; whenever filters are using/comparing text, use the UPPER() or LOWER() functions.
Notice that the CUS:IceCream and CUS:Truffle fields are true/false fields; a value of one means that the field is TRUE (zero would mean false).
Notice that the NOT operator is used with the equal sign; this BOOLEAN OPERATOR is most commonly used with another CONDITIONAL OPERATOR (greater than, less than, equal to).
It is important to understand that there is an order of precedence involved when combining two or more expressions into one filter. A simple definition for "precedence" can be "what the computer will look for first in the database". There are five levels of precedence:
Level 1: Parenthesis characters that surround a portion of a filter expression
Level 2: Conditional operators (<, >, =, etc)
Level 3: NOT
Level 4: AND
Level 5: OR
When the filter expression is evaluated the precedence order will determine the result of the filter. The filter always returns a value of either TRUE or FALSE; either the database field(s) matches what is being checked for or it doesn't. There is no other result that a filter can return. Keeping this in mind, here are some additional filter examples:
upper(CUS:Car) = 'FERRARI'
Above, we are checking for customers who own cars made by Ferrari. If their car is a Honda, the filter condition returns a value of FALSE, and won't be selected for printing on the report.
Let's modify the filter to check for 'Ferrari' and 'Lotus' automobiles by using a BOOLEAN OPERATOR:
upper(CUS:Car) = 'FERRARI' AND upper(CUS:Car) = 'LOTUS'
In this example the BOOLEAN OPERATOR of AND is being used. However, let's examine what is happening and why the above example will fail. The BOOLEAN OPERATOR of AND means that both expressions MUST return a value of TRUE for that database record to be selected. There is no way a car can be both a Ferrari AND a Lotus, so that database record will be skipped.
We need to change the BOOLEAN OPERATOR to the OR operator so the filter looks like this:
upper(CUS:Car) = 'FERRARI' OR upper(CUS:Car) = 'Lotus'
f the car isn't a Ferrari the OR BOOLEAN OPERATOR will continue evaluating the filter expression, since a value of TRUE isn't required for the first part of the expression. Therefore, if the car is a Ferrari or Lotus, that database record will be selected for printing.
Some examples:
Male customers over 30 who drive a Lincoln or Cadillac automobile
upper(CUS:Sex) = 'M' and CUS:Age > 30 AND upper(CUS:Car) = 'Lincoln' or upper(CUS:Car) = 'Cadillac'
Clients between the ages of 30 and 40
CUS:Age > 29 AND CUS:Age < 41