![]() |
![]() |
|
![]() |
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. ![]() |
![]() |
|