Paradox Community
Search:

 Welcome |  What is Paradox |  Paradox Folk |  Paradox Solutions |
 Interactive Paradox |  Paradox Programming |  Internet/Intranet Development |
 Support Options |  Classified Ads |  Wish List |  Submissions 


Paradox Newsgroups  |  Paradox Web Sites  |  Paradox Book List  |  FAQs From The Corel FAQ Newsgroup  



TIP:PdoxWin:Using Criteria Tables instead of Tilde 
Variables:2000:06:10

Version 1.0 (2000.06.10)
written by Dennis Santoro
edited by Paradox FAQ Team
Reposted by Mike Irwin: 2001.02.10

====================
0. Introduction
====================

This FAQ addresses the matter of using criteria tables as 
an alternative to tilde variables, especially for dates, 
when creating paradox queries. It also addresses the use of
criteria tables to limit forms and reports to a subset
of records within a table.

-------------------------------
 0.1 Legal Info and Disclaimers
-------------------------------

Paradox is a trademark of Corel.
Borland Database Engine (BDE) is a trademark of Inprise.

The information provided in this FAQ is provided "as is" 
and is not warranted in any way. The information provided 
in this FAQ is not endorsed or authorized by Corel or 
Inprise in any shape, form, or manner. 

The editors claim NO responsibility for ANY illegal 
activity regarding this file, or as a result of someone 
reading this file.

You may distribute this file, as long as the copies are
complete, unaltered, and are in electronic form only.

-------------
 0.2 Feedback
-------------

Please send feedback in a Corel Paradox newsgroup or the
news:comp.databases.Paradox newsgroup to any of the FAQ 
Team mentioned in the "FAQ: FAQ FAQ" document.

Please preface the subject of your post with the string
"PDXWIN FAQ" to alert Team members to the function of the
message.

Please specify the FAQ name and section number the comment
applies to, if any.

==============================
1. General Information
==============================

In Paradox queries can be used to select records from one 
or more tables. Queries are often used as programming 
constructs within user designed applications.

The most common method of setting criteria within queries 
is to place values within the fields in the query. When
constructed in code these values are substituted for by
tilde (~) variables. The tilde variables can then be set by
requesting that the user input values or by using available
information such as the currently displayed record's key
value and assigning those values to the tilde variables.

An alternative approach is to establish criteria tables and
join fields between the criteria fields and the related
fields in the table(s) from which you wish to select 
records through use of example elements. This method has 
certain advantages over the tilde variable approach in some
circumstances.

========
2. Problem Definition
========

When using tilde variables in queries, the variable is
ultimately cast as a string, as is the entire query. This
has been shown to cause numerous problems; especially when
using dates as query criteria. In addition, the use of 
tilde variable can become difficult or impossible when it 
is not possible to know in advance how many criteria need 
to be set on a field. Both of these problems can be simply 
resolved using criteria tables instead of tilde variables.

==========
3. Discussions
==========

A common use of tilde variables is to submit dates to limit
the selection of records. As has been seen in many of the
Y2K posts in the Paradox news groups, this can result in
problems when users do not cast the date as a properly
formatted string. Note that this problem is also examined 
in the FAQs on Y2K and in the General Text FAQs.

Unfortunately, what constitutes a properly formatted string
is dependent on a number of settings which may be obscure
to a user and may be different from machine to machine.
A query such as:

Query


ANSWER: :Private:ANSWER.DB


:Data:PROVSTAT.DB|Provider#| Proc  | Loccode| Status|
                 |Check    | Check | Check  | Check PEND..|


:Data:PROVSTAT.DB | StatBegin| Statgrant       | Statend |
                  | Check    | Check ~startdate| Check   |


EndQuery

where ~startdate is a variable, cast as a string with a
value of ">= 01/01/2000" will work if the date string is
properly formatted for the BDE settings and windows date
settings in existence on the machine on which the query is
run. As an alternative the query:

Query


ANSWER: :Private:ANSWER.DB


:Data:PROVSTAT.DB|Provider#| Proc | Loccode | Status|
                 |Check    | Check| Check   | Check PEND..|


:Data:PROVSTAT.DB | StatBegin | Statgrant      | Statend |
                  | Check     | Check >=_join1 | Check   |


:Private:STARTEND.DB | Startdate |
                     | _join1    |


EndQuery

will run on any machine regardless of settings when the 
date desired is properly input into the table 

:Private:Startend.db,

since that table and the :Data:Provstat.db table will both
have to translate dates under the same settings. Since 
dates are actually stored not as dates, but as integers, 
they will be converted properly from both tables and will 
match on any machine on which this query is run.

In both cases you will have to get the date value. Most
likely you would ask the user to supply it through a 
dialog.

In the first case you would assign the user input value to
the variable. In the second the user would directly 
populate the startend.db field. This table is in :Private: 
(an alias asigned to point to your :PRIV: directory) in 
this example, since you would not want another user to be 
able to reset the values between the user input and the 
query being run.

:Private: is not your only choice, and may not be the best
one, but the condition that the criteria can not be changed
by another user must be met. The criteria table can be
created on the fly in code, placed permanently in the
directory from which it will be used or kept as a master in
one location and copied to other locations as needed.

More complex criteria table queries, and ones that use
multiple criteria fields, multiple values in some criteria
table fields and non date data are of course possible,
such as:

Query


ANSWER: :Private:Answer.DB


:Data:PATIENT.DB | ID#       | Lname  | Fname  |
                 | Check _j1 | Check  | Check  |


:Data:TREATDT.DB | ID#  | Treatdate      | TID#     |
                 | _j10 | >= _j6, <= _j7 | _j11,_j9 |
                 | _j10, _j1 | Check     | _j11,_j2 |


:Data:PROCS.DB | TID#      | Proctype | Log#   | PID#  |
               | Check _j2 | Check    | Check  | Check |


:Private:STARTEND.DB | Startdate | Enddate |
                     | _j6       | _j7     |


:Private:CTRSLCT.DB | CtrCode |
                    | _j8     |


:Data:PATSTAT.DB | TID#   | Memcenter | TreatCenter |
                 | _j9    | Check     | Check _j8   |


EndQuery

(please note that the string "_join" has been reduced to
"_j" in this query to ensure that it fits without wrapping.
Join markers do not need to be called "_join", so the
queries should still work.)

In this case we are using 2 criteria tables and 3 criteria
fields from them. To track through this query to see what 
it does follow the joins and the criteria. This query does 
the following:

1. For any patients who were treated at the center or
centers listed in ctrslct.db (_j8) we select the treatment
center and the member center, and link that to their TID#
(_j9) in the Patstat.db.

2. Patients with a relevent patstat.db record and a 
treatment date greater than or equal to the startdate in 
startend.db and less than or equal to the enddate in 
startend.db (_j6 and _j7) are located in the treatdat.db 
and the ID# and TID# are used as criteria against the 
treatdat.db itself (_j10 and _j11) and the actual Treatdate 
is selected.

3. The ID# is used to select Patient.db info (_j1) and the
TID# is used to select Procs.db data This results in a 
table with the structure:

ID#, Fname, Lname, Treatdate, TID#, Proctype, log#, PID#,
Memcenter, TreatCenter where each record is a patient who 
was treated at one of the selected centers within the date 
range desired.

----------------------------------------------------

Another advantage of using criteria tables is the ability 
to validate criteria data and simplify user data entry. For
example, date fields used in Startend.db will not allow
invalid date entries and can be entered using the date
stamping possible by pressing the space bar.

Another advantage of criteria tables is the ability to use
them regardless of the number of values needed for 
criteria. 

For example, in the following query it would be impossible
to know ahead of time, how many of the hundreds of possible
values of Proc a user may want to look at. To code this 
with tilde variables you would have to determine the values 
and cycle through a loop which keeps assigning additional 
values to the tilde variable along with the OR operator. 
Using a criteria table, you let the user select from all 
the possible choices, use those choices to populate the 
criteria table (Procslct.db in this instance) with as many 
values as needed and that list will determine the values 
which will be selected from the tables of interest.

Query


ANSWER: :Private:ANSWER.DB


:Data:PROVSTAT.DB | Provider#    | Proc         | Loccode |
                  | Check _join3 | Check _join1 | Check   |


:Data:PROVSTAT.DB|Status  |StatBegin|Statgrant|Statend    |
                 |Check APPR..|Check|Check    |Check blank|


:Data:PROVIDER.DB | Provider# | ProvLname | ProvFname |
                  | _join3    | Check     | Check     |


:Data:PROVIDER.DB | PracDeg | HomeCtr |
                  | Check   | Check   |


:Private:PROCSLCT.DB | Proctype |
                  | _join1   |

EndQuery

--------------------------------------------------------

An additional use of the criteria table approach is to
restrict the records in a report or form. 

By using the same method of populating a criteria table as 
used for queries you can then link the criteria table as 
the master on a data model for a form or report. This link 
then restricts the possible records which can be displayed 
on a form or report to ones that link to the criteria 
table. 

This can be a particularly effective method for such things 
as printing specific invoices (by populating the criteria 
table with the numbers of interest) looking at all records 
for a particular customer etc.

This can also be combined with the criteria query to, for
example, use a criteria table to select all the invoices 
for a given date range to a named answer table (which just
includes the invoice #) and having that named answer table
be the master for a copy of your normal invoice form or
report.

Now the report or form will be restricted to just the ones
of interest.


Paradox Community Newsgroups


 Feedback |  Paradox Day |  Who Uses Paradox |  I Use Paradox |  Downloads 


 The information provided on this Web site is not in any way sponsored or endorsed by Corel Corporation.
 Paradox is a registered trademark of Corel Corporation.


 Modified: 15 May 2003
 Terms of Use / Legal Disclaimer


 Copyright © 2001- 2003 Paradox Community. All rights reserved. 
 Company and product names are trademarks or registered trademarks of their respective companies. 
 Authors hold the copyrights to their own works. Please contact the author of any article for details.