Dynamically filtered table lookups

Alpha Five version 3 allows you to filter a lookup table, but the lookup filter is static – it is only evaluated when the table is first opened. If you want to change the lookup filter for each lookup, you're out of luck. Or so I thought, until I hit upon this clever way of tricking Alpha Five into dynamically re-evaluating the lookup filter.
I'll illustrate the problem and its solution with an order system for an auto parts store.

Sample tables

I'll use 3 tables: manufacturers.dbf, autopart.dbf, and orders.dbf.

The structure of the tables is:

My lookup rules for orders.dbf are straightforward. The manufacturer field is selected from manufacturer.dbf, and the part_num and description fields are selected from autopart.dbf:
Lookup field rule for the manufacturer field of orders.dbf

Lookup and fill field rule for the part_num field of orders.dbf

Notice that the filter in the lookup is "manufacturer=var->mfr". I cannot directly refer to the manufacturer field in orders.dbf in creating an expression for my lookup, but I accomplish the same goal by creating a table-level variable, mfr, in which I store the value of the manufacturer field:

Create session-level character variable, mfr, in Orders.dbf
I can set the value of mfr after each entry of in the manufacturer field by programming a short script for the OnWroteField event:
''XBasic
dim shared mfr as c
dim tbl as p
tbl=table.get("orders")
mfr=tbl.manufacturer
OnWroteField script for the manufacturer field of orders.dbf
At this point, if I enter "vw" for the manufacturer, my lookup for part_num will only display those records in autopart.dbf that are manufactured by Volkswagen. Great! I'm almost done. I reset the value of mfr in the OnSaveRecord event for the orders table:

''XBasic
dim shared mfr as c
mfr=""
Script for OnSaveRecord in orders.dbf

To update the mfr variable when an old record is changed, I added this script to the CanChangeRecord event for orders:
''XBasic
dim tbl as p
dim shared mfr as c
tbl=table.get("orders")
mfr=tbl.manufacturer
Script for the CanChangeRecord event for orders.dbf
This script violates the general rule that CanXX events are permission events and OnXX events are used for all other manipulation. In this case, I want to obtain the pre-existing value of the manufacturer field, which I can only get through the Can event. Finally, I added one more small script to the CanEditField event of the part_num field to make sure that a manufacturer had been chosen before the user attempts to select a part_num:
''XBasic
dim tbl as p
tbl=table.get("orders")
if isblank("tbl.manufacturer") then
cancel()
ui_msg_box("Manufacturer","needs to be entered first")
end if
CanEditField script for part_num

Caveat emptor

Be aware that there is a potential problem with these scripts. Because the scripts use the syntax, tbl=table.get("orders"), then if the orders.dbf table is opened with an alias, the table.get() will fail. An alias will be automatically generated by Alpha Five if more than 1 copy of the table is opened at the same time. You should rarely if ever run into this problem. If you did, the workaround is to use the table.current() syntax instead of table.get(). Table.current() does not use the table's alias, but it does depend on the construction of the set structure being used at the time. In either event, it would be prudent to place a small error trap in each script, to cover the unusual instance in which the table.current() or the table.get() might give unanticipated results.

The good news is that there is no need to write field-level scripts to use a filtered lookup in Alpha Five version 4. A5v4 will accept a filter as part of the lookup field rule. The lookup filter for the part_num field would then simply be manufacturer=orders->manufacturer. There is no need to create any variables or any OnWroteField scripts.

updated 10/13/98 - pkw

Return to home page

Mail your comments on this article