Self-Filling Lookup Fields: Redesign your Lookups in Alpha Five

by Dr. Peter Wayne

Alpha Five version 4 introduces the OnKey event, giving the Xbasic programmer the ability to intercept any keystroke. In a previous OnKey article I showed you how to progressively seek within a browse as keys were typed in a search field. In this article we'll go one step further, and I'll show you how to use the OnKey event to create a field that fills itself in as soon as you type enough characters to identify a lookup field uniquely.

An example will make this clear. Suppose you have a table with the following entries:

If we have a lookup field for a company name on a form, then as soon as we type one key, an "i", the field should fill itself in with "ibm", since there is only one company whose name begins with "i." However, we would need to type 5 keys, "softq", to distinguish "softquad" from "softlogic." Since we need to continuously monitor the typing in a field, we know even before we begin to code that this is a job for the OnKey event.

In the first part of this article, we'll simply place a variable on a form and match the variable against the company names as typing occurs. Create a new, blank form based on the keystrokes table. In form design, add a session-level character variable, choice:

Figure 1. Choice is a session-level character variable.

Here is our form:

Figure 2. Place choice on a blank form.

Once we place choice on the form, we actually have 2 different entities named choice: there is a choice field object on the form, and a choice character variable. There will be times when we refer to the field object and other times when we refer to the variable's value. This can be confusing at times, but I'll try to make it as clear as possible whenever we switch mindset from a field on a form to a variable in memory.

Now we're going to need another variable in which to store the keystrokes in choice as they are entered. We'll call that variable tempstr. We'll also need to keep track of how long an entry has been made in the choice field. This will be a numeric variable which I will call posn. (In my mind I pronounce tempstr as "tempstring" and posn as "position," but it's too time-consuming and error-prone to type long variable names.) These variables will be passed around the form-level events. I could declare them in the form's layout, but I don't have to, since I'm not placing them on the form. I will just declare them in whatever event needs them.

The first event that initializes everything is the form's OnActivate event. Here I'll set tempstr and choice to empty strings and posn to 0:

''XBasic
dim shared tempstr as c
dim shared choice as c
dim shared posn as n
posn=0
tempstr=""
choice=""

Script 1. The OnActivate script for the form.

The real work is done in the OnKey script. As we often do, we'll "think aloud" in semi-structured English, or pseudocode before writing the script:

check to see if the keystroke occurred in the choice box
         if so, then see if the keystroke is a special key
         if it's a special key, let Alpha Five handle it, otherwise
            add the key to tempstr
            check tempstr against keystrokes.dbf to see if there's a unique match
            if there's a unique match, then fill in "choice" with the match
    

Script 2. Pseudocode for the OnKey event.

The actual code for the OnKey event is more tedious because there's a lot of "housekeeping" that's covered over in the pseudocode. Here is the first approach to the OnKey event code:

''XBasic
dim shared tempstr as c
dim shared posn as n
dim shared choice as c
dim tbl as p
dim idx as p
dim nrecs as n

tbl=table.current()

if this.active()="choice" then
 if a_user.key.event="down" then
  if left(a_user.key.value,1)="{" then 
   ' it's a special key or backspace or function key
   ' pass all special characters to form 
   a_user.key.handled=.f.
  else
    posn=posn+1
    tempstr=tempstr+a_user.key.value
  end if
  a_user.key.handled=.f.
  ' check to see if a unique name can be matched 
  query.filter="left(name,"+ltrim(str(len(tempstr)))+")='"+tempstr+"'"
  query.order=""
  query.options="I"
  idx=tbl.query_create()
  nrecs=idx.records_get()
  if nrecs=1 then
   ' unique match found, update the field on the form
   choice=tbl.name
   choice.refresh()
   a_user.key.handled=.t.	
  end if
  idx.drop()
 end if ' a_user.key.event="down" 
end if ' active='choice' 

Script 3. First attempt at OnKey event script.

I don't want to minimize the effort in Script 3. We used the <index_pointer>.records_get() method to see how many names matched the entry in the choice field. We also ignored all the "special" characters and just passed them through. As a first approximation, it's not bad. In fact, if the truth be known, it was closer to my fourth attempt than my first attempt! If you start typing "micr" it will fill out "microsoft." But the script doesn't handle backspaces, left-arrows, right-arrows, deletions. We need a more intelligent script to handle these.

I won't take you through the different experimentations I made to handle the special keys. I'm not even sure that the script I came up with is bullet-proof in all key combinations, but I haven't seen any errors yet. Here is the next script iteration:

''XBasic
dim shared tempstr as c
dim shared posn as n
dim shared choice as c
dim tbl as p
dim idx as p
dim nrecs as n
 
tbl=table.current()

if this.active()="choice" then
  if a_user.key.event="down" then
    if left(a_user.key.value,1)="{" then 
     ' it's a special key or backspace or function key
     select
      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
      case a_user.key.value="{END}"
       posn=len(tempstr)
      case else
       ' do nothing
     end select
     ' pass all special characters to form 
     a_user.key.handled=.f.
    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
    a_user.key.handled=.f.
    ' check to see if a unique name can be matched 
    query.filter="left(name,"+ltrim(str(len(tempstr)))+")='"+tempstr+"'"
    query.order=""
    query.options="I"
    idx=tbl.query_create()
    nrecs=idx.records_get()
    if nrecs=1 then
    ' unique match found, update the field on the form
    choice=tbl.name
    choice.refresh()
    sys_send_keys("{END}")
    tempstr=trim(choice)
    posn=len(tempstr) 
    a_user.key.handled=.t.	
   end if
   idx.drop()
  end if
 end if ' a_user.key.event="down" 
end if ' active='choice'  

Script 4. This script handles special editing keys.

Script 4 shows us how we can fill in a variable with a value from a table. Here is an example of an "invisible user" typing in "softq" and having the field fill in with the first unique match:

Figure 3. Self-filling field in action

And in real life...

In a real application we usually are filling in a lookup field from a table that is not the base table for the form we are using. In that case the code we constructed in Script 4 needs to be modified. The 2 major modifications are:

1. The lookup table is not already opened when the OnKey event fires. Therefore it is necessary to open the table for each invocation of the OnKey event. Since opening and closing tables is time-consuming, I would ideally like to open the lookup table once in the OnInit event for the form, and keep it open for the lifetime of the form. Unfortunately each Xbasic script automatically closes all table pointers that it opens, so it is not possible to open a table pointer in one event and repeatedly refer to it in another event. Perhaps Alpha Software will modify the behavior of table pointers in the future.
2. Using a query is not the fastest way perform a lookup. I made sure there was an index on the lookup field and used the index to perform the lookup. To verify check whether there is more than one match against the value in tempstr, I do a fetch_next() on the lookup table and see if the next record also matches.

Here, then, is an example of an OnKey event script for a self-filling field in a real application. In this application, the field payee_name is filled by a lookup to the table payee.dbf:

''XBasic
dim shared tempstr as c
dim shared posn as n
dim lookup_tbl as p
dim candidate_text as c

if this.active()="payee_name" then
 if a_user.key.event="down" then
  lookup_tbl=table.open("payee",file_ro_shared)
  lookup_tbl.index_primary_put("payee_name")		
  if left(a_user.key.value,1)="{" then 
   ' it's a special key or backspace or function key
    select
     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
     case a_user.key.value="{END}"
      posn=len(tempstr)
     case else
      ' do nothing
    end select
    ' pass all special characters to form 
    a_user.key.handled=.f.
   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
    a_user.key.handled=.f.
    ' check to see if a unique name can be matched
    lookup_tbl.fetch_find(tempstr)
    if left(lookup_tbl.payee_name,posn)=tempstr then
     candidate_text=trim(lookup_tbl.payee_name)
     lookup_tbl.fetch_next()
     if (lookup_tbl.fetch_eof()=.t.) .or. (left(lookup_tbl.payee_name,posn)>tempstr) then
      ' it was unique, stuff it in!
      payee_name.text=candidate_text
      sys_send_keys("{END}{F2}")
      tempstr=trim(payee_name.text)
      posn=len(tempstr) 
      a_user.key.handled=.t. 
     end if
    end if			 
   end if
   lookup_tbl.close()	
 end if ' a_user.key.event="down" 
end if ' active='payee_name' 

Script 5. The OnKey script for a real application.

And to show you that it works, here is an entry in my checkbook. Note that as soon as I type bel that the payee field is filled in with BELL ATLANTIC:
Figure 4. A real-life application with a self-filling field.

If you run a version of Script 5 you will notice a small delay as you type characters in the payee field on the form. There also is a small flicker as the field is filled in. The entry delay is caused by the need to open payee.dbf on every keystroke in that field. I wasted more time than I care to admit trying to devise ways to keep payee.dbf open: I added it to the set structure on which the form is based, I tried to pass it around through shared variables, and finally I tried the sure-fire expedient of holding my breath until my cheeks turned purple, but even that failed. Maybe you, gentle reader, can devise a way to keep from having to continually reopen the lookup table, or maybe Alpha Software's development team will come up with a way to pass pointer variables around. In the meantime, the self-filling field works, and on fast machines with a local lookup table, the performance is adequate for all but the fastest typists.

If you improve on these scripts, don't forget to let me know! I'll post your amendments on this site!

11/7/98

Don't forget, we need your feedback to make this site better!

Return to home