by Selwyn Rabins
It's often useful, when there are a lot of records in a table, to have a filtered lookup - this means the user has a lot fewer records to look through to find what he wants. Here's one way to do it (you can download this Sample Application):
The image above shows a simple form with two combo-box fields. The Vendor field is populated by using a script in the OnInit event of the form (note: this could also be done in Field Rules without using any Xbasic, as long as the combo boxes are bound to fields in the table, rather than variables. That's because Alpha Five only allows you to define field rules for fields, not for variables):
dIM pObj AS P
pObj = topparent:vendorid.this
dim vendorList as c
vendorList = table.external_record_content_get("vendor","alltrim(name) +'|'+vendor_id")
pObj.settings.dynamic_list = vendorList
'vendorList looks like this: vendorName|vendorID
'this causes a5 to display the vendorName, but store the vendorID in the field
'we need to initialize the combo box to show the first value in the vendorList
'but we need to set it to the vendorName which is the word to the left of the "|" in the
'first line of the list.
vendorid.text = word(word(vendorList,1,crlf()),1,"|")
dim currentVendorId as c
currentVendorId = alltrim(word(word(vendorList,1,crlf()),2,"|"))
'next we populate the Vendor field
dim productList as c
productList = table.external_record_content_get("product","description","description","vendor = " + quote(currentVendorId))
pObj = topparent:productID.this
pObj.settings.dynamic_list = productList
productID.text = word(word(productList,1,crlf()),1,"|")
This also populates the Product field. Then, when the user selects a different vendor, a script in the OnChange event of the Vendor field repopulates the Product field:
dim v as p
v = topparent.variables()
dim productList as c
productList = table.external_record_content_get("product","description","description","vendor = " + quote(v.vendorID))
DIM pObj AS P
pObj = topparent:productID.this
pObj.settings.dynamic_list = productList
productID.text = word(word(productList,1,crlf()),1,"|")
Note that both combo boxes are set so the choices are determined by Xbasic (the code shown above). The properties screen for each combo box is shown below:
Also, note that the combo box displays one value (the NAME field), but stores another value (the VENDOR_ID field). This means that you don't have to display a value that would be meaningless to the user, but you can still retrieve that value.