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 Programming Articles  |  Beyond Help Articles  |  Tips & Tricks Articles  


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
EndVar
I 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 = " "

endMethod
Between 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:
  • Focus was on the "Item" field.
  • The form was in edit mode.
  • The contract had changed.
The way I handled things was by writing a custom method, RebuildLookup(), and assigning it to the "Item" field. It’s actually a procedure, with no parameters being passed, but in ObjectPAL it’s easier to document a custom method.

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

endMethod
Ah, 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

endMethod
Finally, 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("")

endmethod
We 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.