![]() |
![]() |
|
![]() |
Combining setRange() with setGenFilter() - the best of both worlds by Tony McGuire There are some very real differences in setRange() and setGenfilter(). You need to be familiar with each of these built-in methods in order for this document to make sense to you. Please read Beyond Help - setGenFilter and Beyond Help - setRange for an explanation of these two functions. While both of these functions operate by presenting a limited view of a table, the methodology differs greatly as does the speed and flexibility. However, by combining these two functions you gain by utilizing the strengths of both. First, since we will be applying a setRange(), our table must have a primary key. We will also define secondary indices. Much as in the SQL world, the way you define these indices is critical to your successful use of setRange(). Your success with setRange() is determined by understanding how your secondary indices are designed. This is a lot of the 'engineer' in Database Engineer. Using the setRange() example, we will create additional secondary indices as well as utilize the one created in the example. Advanced Example of Using setRange() Once you have a table and secondary index, you can use setRange() to filter on one or more fields. Criteria set using setRange() apply as exact matches on all fields but the last one, which can be a low/high range (or high/low range, but more on that later). Setting both low and high as the same value allow you to retrieve an exact match on the last field as well. There is a 'gotcha' built into this. If you have 3 fields, and want an exact match on every one of them, you might be tempted to pass 3 values in setRange(). Unfortunately, the first value passed is an exact value on the first field and the second and third are low and high values for the second field. You need to pass 4 values - the first two values as exact matches on the first two fields, and the 3rd and 4th values being low and high values for the 3rd field; you just pass the same value for both. setRange(), while incredibly fast, does not have a lot of flexibility. Not all fields contain values that can be retrieved in ranges. However, you might be surprised at how ranges can be applied to make your application incredibly fast. Let's use the real estate example from the Beyond Help - setRange page. Create a secondary index called 'AddressNa'. Include the StreetName field and StreetNumber field, in that order. To locate properties on a particular street, "Greenfield Dr", your users might be tempted to search by the full name - "Greenfield Dr". They would fail quite often. From experience, I know that data entry is not an exact science. This address would appear as "Greenfield Dr", "Greenfield Dr.", "Greenfield Drive", "Green Field Dr" and every combination thereof. On top of that, the users would attempt to search by every one of these combinations. If you ask for only the first 4 or 5 characters, however, the success ratio approaches 100%. Using the "Address" index, setRange("green","greenzzz") results in every combination above being included in the resulting ranged table. Of course, it also retrieves every other property whose address starts with Green. Unless your area is different than ours, there won't be very many returns anyway, and at worst the user scrolls through a few records. But they succed with your program every time. You could also create another secondary index called 'AddressNo' and reverse the field order. If the user enters both street name and street number, use this other index to set the range - setRange("1020","green","greenzzz"). You can have multiple secondary indices defined, and decide which index to use based on which fields, or combination of fields, contain user input. Combining setRange() and setGenFilter() The first and most critical thing to learn in making the best use of these two functions is: Once you use setRange(), you can add a setGenFilter() on top of it. setGenFilter() doesn't replace setRange() - it further limits the view. Thus, tcAvailable.open(":aliasname:Available.db","ZipPrice") ; using the setRange() example tcAvailable.setRange("90210","125000","145000") ; get properties in 90210 with price ; between $125,000 and $145,000 dyAvailable["bedrooms"]=">=3" ; 3 bedrooms or larger dyAvailable["SquareFeet"]=">=1400" ; at least 1400 square feet tcAvailable.setGenFilter(dyAvailable)results in the view being limited to properties in Zip Code 90210, price range $125,000-$145,000, 3 or more bedrooms, 1400 square feet or larger. You utilized the incredible speed of setRange() to create a very limited view of the table; then applied setGenFilter to that limited view. setGenFilter wasn't applied to the entire table, it was applied only to the limited view that the setRange() resulted in. Combining setRange() and setGenFilter() works with table or uiObjects in exactly the same way. In a form, declare a uiObject (uiAvailable) and a dynarray (dyAvailable string) at the form level. This is so any use of these variables will persist for any construct on the form. The form is attached to "Available.db". Place a tableframe on the top 2/3 of the form; the tableframe name should be "available". Place 3 unbound fields (could be dropdown picklists as well) for retrieving user input for ZipCode, LowPrice and HighPrice. In addition, place 2 picklists; these will be filled with values for the user to select MinBedRooms and MinSquareFeet (Name the fields as I have indicated). You will want to create some picture restrictions for the input fields, or verify that the values entered are valid for the underlying fields that the values will be applied against. In the form open, declare uiObject.attach(available) in order to apply the operations to the tableframe. In a pushbutton on the form, you could use: dyAvailable["bedrooms"]=">="+MinBedRooms ; must be text field, or use string(MinBedRooms) dyAvailable["SquareFeet"]=">="+MinSquareFeet ; must be text field, or use string(MinSquareFeet) uiAvailable.setRange(ZipCode,LowPrice,HighPrice) uiAvailable.setGenFilter(dyAvailable)The results are that the tableframe now reflects only records that match both the setRange() and setGenFilter() criteria. No external "answer" tables to manage or retrieve values from. If your table has a "select" field for the user to select individual records from, you can instantly provide a report based on this limited view. Design your report with a filter on the "select" field. When the user selects records and clicks on your 'print' button, the report includes only those selected (it doesn't use the setRange() or setGenFilter(), it just uses the selected properties obtained by use of them). The incredible speed of setRange(), combined with the flexibility of setGenFilter(), are what make Paradox such a great solution for providing data on a web site with full-featured, interactive searches. For many scenarios, it is a viable contender when competing with much higher-end SQL based systems. If you want to put your company information on the internet, but aren't ready to spend $50,000-$100,000 or more to get there, there is NO better solution than Paradox using the included OCX. This applies to nearly any industry, for nearly any small to medium sized company. For Beyond Help on setRange(), see setRange For Beyond Help on setGenFilter(), see setGenFilter For a complete tutorial on creating Internet applications using Paradox® and the OCX, see our Internet Section. 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. ![]() |
![]() |
|