![]() |
![]() |
|
![]() |
Beyond Help: setRange by Tony McGuire Paradox Help on setRange() (Copied from the OnLine Help System in Paradox® 9. Paradox 9 was used to write this article, if you're using a different version of Paradox, some dialogs may be different than described. setRange() was added in Paradox 5, so the info below should be valid for all version from 5 through the most current version.) Specifies a range of records to associate with a Table variable. This method enhances the functionality of setFilter, which it replaces in this version. Code that calls setFilter continues to execute as before. Syntax 1. setRange ( [ const exactMatchVal AnyType ] * [ , const minVal AnyType, const maxVal AnyType ] ) Logical 2. setRange ( rangeVals Array[ ] AnyType ) LogicalDescription setRange specifies conditions for including a range of records. Records that meet the conditions are included when the table is opened. setRange compares the criteria you specify with values in the corresponding fields of a table's index. If the table is not indexed, this method fails. If you call setRange without any arguments, the range criteria is reset to include the entire table. Syntax 1 allows you to set a range based on the value of the first field of the index by specifying values in minVal and maxVal. For example, the following statement examines values in the first field of the index of each record: tableVar.setRange(14, 88) If a value is less than 14 or greater than 88, that record is filtered out. To specify an exact match on the first field of the index, assign the same value to minVal and maxVal. For example, the following statement filters out all values except 55: tableVar.setRange(55, 55) To set a range based on the values of more than one field, specify exact matches except for the last one in the list. For example, the following statement looks for exact matches on Corel and Paradox (assuming they are the first fields in the index), and values ranging from 100 to 500 (inclusive) for the third field: tableVar.setRange("Corel", "Paradox", 100, 500) In Syntax 2, you can pass an array of values to specify the range criteria, as listed in the following table.
Beyond Help While the help above was taken from the setRange() | TCursors section, it shows the use of table variables. The use of setRange() is virtually the same for both, as well as for a UIObject. Don't let semantics fool or confuse you. The first thing that should be learned is that setRange() operates based on indexed fields ONLY. Conversely, setGenFilter() will operate on any field(s). Table keyed fields are indexed, as are fields that you make part of a 'secondary' index. This is accomplished by using Tools | Utilities | Restructure from the Paradox menu. In order to create secondary indices, the table must have a 'primary' key (at least one field keyed); it may have a compound key as well (multiple fields keyed). It may not seem logical to key a particular table, but for the power that setRange() brings, it is often well worth the initial effort to do so. Even if you never use the primary/compound key, the ability to create secondary indices is, again, often worth the effort. From the restructure dialog box, you key one or more fields by clicking in the box in the far left column. As your mouse cursor enters this box, it will turn into the symbol of a key. Clicking in the box will create a key symbol in the box. A table's primary key, whether a single or compound key, must begin with the first field in the table and extend through every field in order. You cannot start a primary key on the third or fourth field, nor can you skip any fields when creating a primary key. Secondary indices, however, allow you to create a single or compound index (key) by selecting fields in any order you wish; they don't have to start with the first field, nor is your selection of fields dependent on the order the fields appear in your table. You may use a single field, or multiple fields. Planning this ahead of time is critical to the success of making actual use of a secondary index. You may have up to 16 fields in each secondary index, and up to 127 indices for each table. Why use setRange()? One user reported a query that took over 20 minutes over a wireless LAN. He converted his table for use with setRange() and got his results in 1 minute, 45 seconds. More than 10 times faster! (See The Need for Speed in the Paradox Programming section.) This is because queries transfer all data over the connection from the location of the data to the copy of Paradox conducting the query. Also, a setRange() uses a 'limited' or 'restricted' view of the data rather than breaking out matching records and creating a new table. Even on a computer where the tables and Paradox are located together, there is a dramatic difference in speed when setRange() can be applied. How to make use of setRange() Let's take a table ("available.db") that is keyed on a single field, fldKey. fldKey is a unique identifier for each record. There are 7 other fields, all numeric - Price, Age, Bedrooms, Baths, Acreage, SquareFeet (A real estate application). Also, there is a numeric field for the street number, a numeric field for ZipCode, and an Alpha field for street name. In addition, there are many descriptive fields about each property; these are more likely to be fields you want to display rather than search on. fldKey isn't nearly as useful to people wanting to find a home as the other fields, although it could be used in advertising and related back as a method for locating the particular record. Most people, however, will want to locate multiple properties and select the one that most nearly matches their wants/needs. Since the table has a primary key (fldKey), we can add one or more secondary indices. We will create multiple secondary indices, based on the type of search someone might need to conduct. These secondary indices will be designed based on the various types of searches we will offer in our application. The most common type of real estate search starts with area (ZipCode). Price is also a large determining factor. Next is usually either SquareFeet (size) or # of Bedrooms (they aren't always interdependent). Our first secondary index, therefore, is going to start with ZipCode. For the second field, we will use Price. For a name, we will give it "ZipPrice". In addition, we will include the SquareFeet and Bedrooms fields. Create the index as non-unique, caseinsensitive, and Ascending order for ZipCode and Price, and Descending order for SquareFeet and Bedrooms. Using this index for a tcursor might look like: var tcAvailable TCursor strZipcode, strLprice, strHprice String endVar ; note that the variables are string. ; setRange() will work on a numeric field even ; using string variable as the parameters. strZipcode="90210" strLprice="125000" strHprice="145000" tcAvailable.open(":aliasname:available.db","ZipPrice") ; you could also use ; tc.Available.open(":aliasname:available.db") ; switchindex("ZipPrice") ; But why 2 steps instead of 1? (switchindex is ; useful in many other situations, though) tcAvailable.setRange(strZipcode,strLprice,strHprice)Since you are passing three items to setRange(), it applies the first as an exact match requirement for the ZipCode field (first field in the secondary index). It next applies the second and third parameters as a range for the Price field (second field in the secondary index). Also, because an index contains a pointer to a sorted list of each record, the resulting restricted view comes back sorted based on the fields in that index. The first field won't matter, since all records will have a value of 90210. However, since the index contains the Price field, the restricted view will also be sorted in price order, low to high. Why did we include the SquareFeet and Bedrooms fields? Because, even though we didn't use them in our setRange() function, the results now appear sorted by Price. If Price is the same they appear in SquareFeet order - largest to smallest. The same applies to Bedrooms if Price and SquareFeet are the same. The resulting tcursor (tcAvailable) will be pointing to a restricted view of the 'available.db' table which contains only those records in ZipCode 90210 with a Price between $125,000 and $145,000. This operation, conducted on a table with, for example, 10,000 records, would likely take less than 2 seconds, less than 1 second on a fast computer. You could then use scan tcAvailable : to step through the records, tcAvailable.instantiateView("myanswer.db") to write the restricted view as a new table on disk, or tcAvailable.copy("myanswer.db") to copy the tcursor to a table on disk. You can also perform column calculations or any other functions available to a tcursor. More importantly, since setRange() works the same way for a UIObject, you could have a form with blanks at the bottom of the form to fill in for ZipCode and Price. Above you can place a tableframe containing "available.db". Fill in the blanks and click on a button with the following code: var uiAvailable UIObject endVar uiAvailable.attach("available") ; "available" is the name of the table frame uiAvailable.switchIndex("ZipPrice") uiAvailable.setRange(zipcode,lowprice,highprice) ; zipcode, lowprice and highprice are the ; field names you use to gather user inputNow, the tableframe instantly contains matches to the user's requirements. Want some real speed, with flexibility thrown in? Combine setRange() and setGenFilter(). For Beyond Help on setGenFilter(), see setGenFilter For an explanation on combining the speed of setRange() with the flexibility of setGenFilter(), see Combining setRange and setGenFilter For some real world experiences that showcase the difference between using a Query and setRange(), see The Need for Speed Discussion of this article |
![]() 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. ![]() |
![]() |
|