How To Filter Data With The Where Clause

This topic discusses using the WHERE clause in the Report Writer. When you use WHERE in an expression, you are querying the database to filter data. You are asking the Report Writer to print out only records that meet the criteria defined in the WHERE clause. When this WHERE clause is used properly, you acquire great control over your data. Very complex reports may be rendered swiftly and easily when data is retrieved in the appropriate logical sequence.

We will discuss the basic syntax for writing a WHERE clause and show examples.

The WHERE clause is most often used to print a list of records. This involves creating a loop in your report. To get the most out of this discussion, it is necessary to thoroughly understand the relationship between different forms in the database and the concepts of Lists and Loops. Please refer to How To Print A List Of Records.

Using a loop, you may print a list of records from your database. If you include a WHERE clause at the start of the loop statement, you can filter the list that is printed to print only the records that match certain criteria.
The WHERE clause may filter data with Equals (=), Greater Than (>), Less Than (<) and a Range criteria (..)
A report can prompt for a value at run time using a WHERE clause with the ? symbol.
You may use multiple WHERE clauses to fine tune the criteria. However, you may not reference the same criteria more than once. For instance, you may not say WHERE (@de.sta = ACT) WHERE (@de.sta = BAN). That won't work. It is necessary to try a range instead or some other means of fine tuning your filter.

It is preferable to search by numbers rather than by text. For example, rather than performing a search by Client Name, search by Client Number instead for reliable results.

Notice the use of the operand "no total" in the examples. Whenever you print a loop, this operand "no total" should be included unless you specifically want totals at the end of your loop.

Syntax

Here is the syntax for each of the specifiers you may use with the WHERE clause. Each specifier is described briefly and further examples are given later on.

In the syntax:

{field_code] indicates a valid field identifier for a printable field in the form being scanned.

{value} can either be an absolute value or a field code.

If a value is specified, please be sure to conform to valid field values. For example, dates must be entered as MM/dd/yy or the report will complain as it prints. Spaces in values are not always interpreted correctly.

Text values should be enclosed in double quotes.

If a field code is specified, the field must be of the same type as the comparison field. For example, dates can only match dates.

There should be spaces in front of and after the Equals (=), Exact Match (==), Greater Than (>) and Less Than (<) signs, as well as the Range (..) specifier and the Prompt (?). If you don't include the spaces, results are unpredictable.

Equals

SYNTAX:

WHERE ({field_code} = {value})

Example:
WHERE (@de.li = 06/13/3005)

EQUALS: (=)
This statement causes the loop body to be printed once for every record whose field matches the {value} entered. With text, this will grab partial matches. For instance, WHERE (@de.na = "north") will grab "north dakota" as well as "northern."

Exact Equals Match

SYNTAX:

WHERE ({field_code} == {value})

Example:
WHERE (@de.op == 1)

EXACT EQUALS MATCH: (==)
This statement causes the loop body to be printed once for every record whose field exactly matches the {value} entered. Using this operator will return only records with field values that are identical to the match parameter.

Example:

@op no total WHERE (@op.id == 1)

will return only the operator whose ID is 1, not all operator IDs that begin with 1, such as 1, 111, and 1JC.

If you use the clause below,

@tvarOpr* = ? Enter Operator ID
@tr no total WHERE (@tr.op == @tvarOpr)
@tr.fi @tr.op @tr.tu
@tr

Collect! will search for only those transactions whose operator value matches exactly the @tvarOpr value.

Greater Than

SYNTAX:

WHERE ({field_code} > {value})

Example:
WHERE (@de.ow > 1.00)

GREATER THAN: (>)
This statement causes the loop body to be printed once for every record whose field is greater than the {value} entered. Note that for text fields, the test for matching is only as many characters as you specify in the {value} part. For example, WHERE (@de.na > "h") will print all debtors with names from "H" onward. While WHERE (@de.na > "ho") will print all debtors with names from "Ho" onward.

Less Than

SYNTAX:

WHERE ({field_code} < {value})

Example:
WHERE (@de.ow < 1000.00)

LESS THAN: (<)
This statement causes the loop body to be printed once for every record whose field is less than the {value} entered. Note that for text fields, the test for matching is only as many characters as you specify in the {value} part. For example, WHERE (@de.na < "h") will print all debtors with names up to "Gz." While WHERE (@de.na < "ho") will print all debtors with names up to "Hn."

Range

SYNTAX:

WHERE ({field_code} = {value} .. {value})

Example:
WHERE (@de.fi = 0001 .. 0999)

RANGE: (..)
This statement causes the loop body to be printed once for every record whose field is greater than the first {value} entered, and less than the second {value} entered. For text fields, the test for matching is only as many characters as you specify in the {value} part. For example, WHERE (@de.na = "A" .. "G") will print all debtors with names from "A" to "Gz." While WHERE (@de.na = "An" .. "Go") will only print debtors with names between "An" and "Goz."

Not Equal

SYNTAX:

WHERE ({field_code} != {value})

Example:
WHERE (@de.u1 != "exclude")

NOT EQUAL: (!=)
This statement causes the loop body to be printed once for every record whose field does not equal the {value} entered. With text, this will grab partial matches. For instance, WHERE (@de.na != "north") will exclude "north dakota" as well as "northern."

Prompt

SYNTAX:

WHERE ({field_code} = ?)

Example:
WHERE (@de.sta = ?)

PROMPT: (?)
This statement causes the report to pause before printing. A dialog box displays asking the user for a value to use to filter the report's data. For example, WHERE @de.na = ? will allow the user to type in a value.

Further examples and detailed use of the WHERE clause are included below.

Where Clause Using Range

This conditional statement will cause all accounts with a status of ACT (Active) through BAN (Bankrupt) to be printed for the currently selected Client.

//---Only list matching records in the report.

@cl.de no total WHERE (@de.sta = ACT .. BAN)
@de.na @de.fi @de.sta
@cl.de

You can add conditionals, filtering further the records that are pulled by your code. Please see Multiple WHERE Clauses below.

Multiple Where Clauses

Adding to the example above, we can filter the list of Debtors to print only those whose Owing is more than $1,000.

@cl.de no total WHERE (@de.sta = ACT) WHERE (@de.ow > 1000)
@de.na @de.fi
@cl.de

The conditions are additive. In other words, these conditions work together to print all Debtors whose status is ACT, AND whose owing is greater than $1,000.00.

You may use up to five WHERE clauses in a single loop.

You may not reference the same criteria more than once. For instance, you may not say WHERE (@de.sta = ACT) WHERE (@de.sta = BAN). That won't work. It is necessary to try a range instead or some other means of fine tuning your filter.

Where Clause Using A Prompt

@de no total
@de.at no total WHERE (@at.d1 = ?)
@de.na
@de.at
@de

When this report is run, the user will be prompted to enter a date range to search for in Debtor attachments since @at.d1 is a date field. Then all the debtors with attachments with a Date 1 that matches the criteria will be listed in the report.

This example uses a report to facilitate searching. You can print the results to screen and use them as needed. This provides a way to search the attachments for all debtors for particular information that may be hard to retrieve otherwise.

There are virtually unlimited uses of WHERE clauses with a prompt (?). Whenever you want to vary the data retrieved in a report or make it possible to use the report for different conditions, this prompt can be coded into the design. Also, you can modify an existing report that retrieves a particular range of data, to prompt for a value, instead.

For instance, in the examples above:

@cl.de no total WHERE (@de.sta = ACT) WHERE (@de.ow > 1000)
@de.na @de.fi
@cl.de

@de.ow > 1000 could be replaced with @de.ow > ? to make this a variable report that prompts for a value and prints different data depending on the value that the user enters when the report is run.

Prompt With Custom Message

When you prompt for data, you can also display instructions to the user to specify what you want entered. To do this, you would place your text after the prompt. Please leave a space between the prompt and your message.

For example:

@tvarOwing = ? Enter the minimum Debtor's Owing Amount

When the Prompt box is displayed on the screen, your message will be displayed in it above the field for the user's data entry. For the example given, the user would see "Enter the minimum Debtor's Owing Amount" on the screen when the report is run.

Prompt With A Variable

To use the results of your prompt in a WHERE clause, you would use a variable to obtain the data. Be sure to assign a data type to the variable first! In the example below, the variable @tvarOwing is initialized as currency with the $ symbol.

For example:

@tvarOwing$ = 0.00
@tvarOwing = ? Enter the minimum Debtor's Owing Amount

@cl.de no total WHERE (@de.sta = ACT) WHERE (@de.ow > @tvarOwing)
@de.na @de.fi
@cl.de

Please refer to How To Use Variables for more information on this example.

Prompt Once For Input

If you are printing from a list, and your report contains a prompt for user input, Collect! will only prompt once. The input is then applied to each account in your list. Collect! can also process multiple prompts within the report.

This relates to selecting the "All" option when printing from a list of tagged accounts.

Pulling Information From Subforms

You can also filter by fields in sub forms in your report queries using the WHERE clause.

This feature applies to any information contained in sub forms related to a particular dialog. It does not work with forms that are merely "attached" to the dialog but rather sub forms that are used to add additional information such as settings. Examples of these are listed below.

DEBTOR FORM SUBFORMS

Debtor Detail

Financial Detail

CLIENT FORM SUBFORMS

Client Settings

One very practical example of this is listing clients with a Billing Period of your choice. Since Billing Period is in a Client subform, the Client Settings, previously one had to put a Client Settings loop into the report to extract the billing period. Now, this is possible with a WHERE clause. This means only one loop instead of two and optimizes the report production.

EXAMPLE

@cl no total WHERE (@cs.bp = Monthly)

Some data here ..

@cl

In this example, the report writer is able to determine Monthly billed accounts while in the Client loop and can filter the data appropriately. (@cs is the Client Settings form)

Where Clauses With Zero Values

Use care when constructing your WHERE clauses. If you set your criteria too broadly, you can slow down your report quite unnecessarily, as well as producing wrong results..

For instance:

@de no total where ( @de.ju = 0.00 .. 999999 )
@de.na
@de

The above clause cycles through ALL debtors because you specified no Judgement Amount or a Judgement Amount.

While the next clause will go much faster because you are saying ONLY those accounts with a Judgement Amount.

@de no total where ( @de.ju = 0.01 .. 999999 )
@de.na
@de

This is also true when using GREATER THAN with the WHERE clause.

For instance:

@de no total where ( @de.ju >= 0.00 )
@de.na
@de

The above clause cycles through ALL debtors because you specified no Judgement Amount or a Judgement Amount.

While the next clause will go much faster because you are saying ONLY those accounts with a Judgement Amount.

@de no total where ( @de.ju >= 0.01 )
@de.na
@de

Optional Keys - Finding Records With Empty Field

You may want to find records that have no information in a particular field. As an example, an empty email address.

@de no total where (@de.em < !)
@de.na @de.em
@de

This WHERE clause never returns any results. This is because @de.em is an optional key. Optional keys are not stored in the index file if the field is empty or blank. The solution is to augment your query with a known primary key such as Debtor Name.

@de no total where (@de.na > *) where (@de.em < !)
@de.na @de.em
@de

The report above lists all accounts that do not have an email address.

If the primary key you are using is smaller than the optional key, then you should use the @firstkey command as follows.

@firstkey
@de no total where (@de.fi > 0) where (@de.em < !)
@de.na @de.em
@de

You can ascertain which key fields are optional keys from the database schema.