![]() |
![]() |
|
![]() |
How to "Filter" a Lookup Table © 2001 Rick Carter The Problem Sometimes not all of the choices in a lookup table are appropriate at any one time. In my case, I had a program that was assigning contractual work based on several different contracts. Once the contract had been selected, I wanted the user to select only from the items in that contract. After numerous attempts, I found out that Paradox® would not allow any sort of filter or range to be applied to a lookup table. However, I found a way around this limitation. The Solution: In Brief I’ll describe my strategy briefly, then in greater detail for those who want to see all the code. Only one user edits this application at a time; a different strategy would be needed for a multi-user application. Although Paradox will not allow any sort of range or filter to be applied to a lookup table, Paradox will not complain at all if items are added to or deleted from the table while the program is running, or even if the table is emptied! I kept my original lookup table intact, with all the possible contract numbers, and their related item numbers and item descriptions. This table was removed from the data model, and used as a source table. I created another table with a similar structure, but without the contract number field, and linked that in as the "lookup table." Any time the contract number changed, I emptied the lookup table, then ran an Insert Query to populate the lookup table with the appropriate records from the active contract. The Solution: In Detail My approach was to write ObjectPAL code for four built-in methods and one custom method, all attached to the field for the contract "Item." I would need to maintain a variable holding the value of the current contract. That variable would need to persist throughout the session, no matter what object had focus, but it would only need to be accessible from the Item field. I declared it in the Item’s "Var" window: Var ActiveContract String EndVarI needed to initialize the value of the variable to avoid "unassigned variable" errors; the easiest way seemed to be to assign an empty string in the Item field’s "Open" method, which would be called once when the form opened: method open (var eventInfo Event) ActiveContract = " " endMethodBetween the quotes is a single space; actually, I could have used any string that would never be used as a contract number. ObjectPAL programming is event-driven, and the challenge is always to find where and when the methods should be called. I decided that the only time the lookup table would need to be rebuilt was when three conditions were all met:
I decided to call the method any time focus moved to the "Item" field, and the form was in edit mode. I put the following code in the "Item" field’s "Arrive" method: method arrive (var eventInfo MoveEvent) if isEdit() then RebuildLookup() endIf endMethodAh, but what if focus was already on the "Item" field, and the user entered edit mode? In that case, I would trap the event in the "Item" field’s "Action" method, and call the same custom method: method action (var eventInfo ActionEvent) if EventInfo.id() = DataBeginEdit then RebuildLookup() endIf endMethodFinally, we come to the custom method that does all the work. I’ll show the whole method, then explain it later: method rebuildLookup() var q Query s String tc tCursor endvar s = Contract_Num if s = ActiveContract then return endif message("Rebuilding lookup table....Please wait....") if not tc.open("CONTLOOK.DB") then errorshow() return endif if not tc.empty() then errorshow() tc.close() return endif tc.close() q = Query CONTLOOK.DB | Item Num | Item Descr | Insert | _join1 | _join2 | CONTR.DB | Item Num | Contract Num | Item Descr | | _join1 | ~s | _join2 | EndQuery if not q.executeQBE() then errorshow() return endif ActiveContract = s message("") endmethodWe said we would proceed with rebuilding the table only if three conditions were met, and the third is that the contract has changed. The first thing this method does is compare the contract number in memory to the current value in the Contract_Num field. If they are identical, nothing more needs to be done, and the method is exited. If we’re proceeding, our first task is to place a "Please Wait" message on the Status Bar telling the user what is happening. It would have been good form to also change the mouse cursor to an hourglass, but, in my experience, everything was happening so fast that I didn’t bother. If the Contract has changed, the next thing to be done is to use a tCursor to clear all the records in "CONTLOOK.DB," the lookup table. Any time an unexpected error occurs, a call to "errorshow()" insures that all the error messages will be displayed, and the method returns without proceeding further. Now we’re ready to run the query that will take the appropriate records from our source table, "CONTR.DB," and add them back into the lookup table "CONTLOOK.DB" whenever the "Contract Num" value is the same. By the way, don’t try typing the whole query into your code. Construct your query interactively, using one of the possible values for the "Contract Num," and verify that it runs properly. Then, use "Edit - Paste From" to paste that query into your code, and substitute the "tilde variable" for the value of "Contract Num." After running the query, we assign the "ActiveContract" variable to the new contract, clear the message on the Status Bar, and we’re done! 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. ![]() |
![]() |
|