by Jack Wheeler and Dr. Peter Wayne
Let's say the only thing you remember about a critical customer is that his first name begins with "b." If you're experienced with Alpha Five, you can do a Query By Form to look for all records whose first name starts with "b" and then quickly scan the records in browse view until you identify the customer whom you're seeking. But if your users are not experienced in in Alpha Five, you can provide them with an easy way to search on any of a number of fields. Let's give an example, using the Customer Information form in the sample Invoice application provided with A5.
Here we put a Search button on the Customer Information form:
Figure 1. A Search button has been added to the form.
When the Search button is pressed, a lookup form is launched. The user can then move to any field in the lookup form and begin typing. As the user types, the browse will automatically resynchronize itself to locate the first matching record. At any time the user can change his mind and start searching on another field by just moving to that field and beginning to type:
Figure 2. The user has typed a 'b' in the First Name field, and the browse has moved to the appropriate records.
When the user has located the customer of interest, he or she can either double-click on the browse line, press {ENTER}, or press the Close button to return to the Customer Information form. The Customer Information form will then move to the customer selected in the Customer MultiLookup form.
The Customer MultiLookup form just contains an embedded browse from Customer. The browse has been edited to show only the customer_id, lastname, firstname, and Company fields:
Figure 3. The Customer MultiLookup form with an embedded browse.
The browse is made read-only in the browse Properties:
Figure 4. Set the browse to read-only in the Properties box.
By setting the browse to read-only, we can be certain that the user will not make any changes to the underlying table while typing.
We are going to rely heavily on code developed in another article on a Self-Filling Lookup field. Just as in that article, we will use the OnKey event for the form to capture the user's keystrokes and synchronize to the appropriate record. Here is the OnKey script for the Customer MultiLookup form:
dim shared posn as n
dim shared tempstr as c
dim shared lastfield as c 'lastfield keeps track of a change in field
dim idx as p
if a_user.key.event<>"down" then
end
end if
if browse1.active()<>lastfield then
tempstr=""
end if
lastfield=browse1.active()
if left(a_user.key.value,1)="{" then
' it's a special key or backspace or function key
select
case a_user.key.value = "{ESC}" .or. a_user.key.value="{ENTER}"
this.close()
end
case a_user.key.value="{BACKSPACE}"
if posn>0 then
posn=posn-1
tempstr=iif(posn=0,"",left(tempstr,posn))
end if
case a_user.key.value="{LEFT}"
posn=iif(posn>1,posn-1,0)
case a_user.key.value="{RIGHT}"
posn=iif(posn<len(tempstr),posn+1,posn)
case a_user.key.value="{DELETE}"
tempstr=iif(posn>1,left(tempstr,posn),"")+\
substr(tempstr,posn+2,len(tempstr)-posn)
case a_user.key.value="{F5}"
tempstr=""
posn=0
end
case a_user.key.value="{END}"
posn=len(tempstr)
case else
' do nothing
end select
else
if posn=len(tempstr) then
posn=posn+1
tempstr=tempstr+a_user.key.value
else
tempstr=left(tempstr,posn)+a_user.key.value+\
substr(tempstr,posn+1,len(tempstr)-posn)
posn=posn+1
end if
end if
select
case browse1.active()="Last_Name"
this.index_set("Lastname")
this.find(tempstr)
case browse1.active()="Customer_ID"
this.index_set("Customer_I")
this.find(tempstr)
case browse1.active()="Company"
query.filter="left(Company,"+ltrim(str(len(tempstr)))+")='"+\
tempstr+"'"
query.order="Company"
query.options=""
idx=table.current().query_create()
browse1.refresh()
idx.drop()
case browse1.active()="First_Name"
query.filter="left(Firstname,"+ltrim(str(len(tempstr)))+")='"+\
tempstr+"'"
query.order="Firstname"
query.options=""
idx=table.current().query_create()
browse1.refresh()
idx.drop()
end select
Script 1. The OnKey script for the Customer MultiLookup form. This code is largely cannibalized from the previous article on self-filling lookup fields.
As the user types, the keystrokes are accumulated in a session-level variable, tempstr. The script then checks to see if the user is in the a field with an index. If the user is typing in either the lastname field or the customer_id field, then an index is used to locate the closest matching record. If the user is typing in the firstname field or the Company field, a query is used.
Notice the variable, lastfield, in Script 1. Lastfield is always set to the value of the last active field in the browse. If the user moves from one field to another, then the value of browse1.active() changes and the value of tempstr is reset to "".
Just to be certain that nothing remains in tempstr between calls to this form, we'll add this small script to the form's OnInit event. Here we'll set tempstr to an empty string and posn to 0:
''XBasic dim shared tempstr as c dim shared posn as n posn=0 tempstr=""
Script 2. The OnInit script for the form.
Our last step is to code the script for the Search button in the Customer Information form. It's not very difficult - we'll open the Customer MultiLookup form as a dialog form, and then when the user closes the lookup, the Information form can read the value of customer_id from the lookup form and synchronize itself to that record. Remember, a dialog form is not "closed" by a user action, it is only hidden, so the calling form can always read anything it wants to from a dialog form:
f=form.view("Customer MultiLookup","dialog")
cust_id=f:Tables:customer.customer_id
f.close()
parent.index_set("Customer_I")
parent.find(cust_id)
Script 3. The OnPush script for the Search button.
Don't forget to explicitly close your dialog forms from your scripts!
There's a lot of behind-the-scenes activity going on with every keystroke in the MultiLookup form. Each keystroke results in another index search or another query of the customer table. If your form is very complex, you may get an annoying flicker with each keystroke. So keep your lookup form simple! Don't forget, also, that queries on unindexed fields will run slowly as tables get large. If your users are doing frequent searches on a field, then indexing that field will speed up your application's performance.
4/30/00
Don't forget, we need your feedback to make this site better!