Cascading Progressive Search with a list box

By Doug Chanley

I have used Peter Wayne's OnKey progressive search in other applications. It worked well for me, but recently I came into a situation that needed something more. I have to give a little background about the application to explain what I needed:

The application is relatively small, used to generate a Material Take Off (parts list) & Material Request Form for construction/maintenance jobs. There are 3 different companies involved with input or use of data input into the application. Two of these companies are major corporate entities with a bad case of "MS" (Microsoft Syndrome). I would imagine that they have spent an outrageous amount of money on a software solution, but they are not without problems. I’m just trying to get my foot in the door with one small solution.

The workflow is as follows:

1. Company 1 requests the work,
2. Company 2, the contractor, completes the work & has to request supplies from Company 3.
3. Company 3, the supplier, provides the materials and maintains the parts list.

The problem was the table used to populate the parts list contained 5,000 records and used a lot of word fragments in the description field. Imagine the difficulty that a user in the contractor's office, unfamiliar with the way the materials are indexed by the supplier, will have in placing an order for [1/8 X CLOSE STD BLK CS CW PIPE NIPPLE TBE A53] by picking from a standard lookup filled with thousands of similarly named items!

The OnKey Progressive search sounded like what I needed to use, but how would someone in the contractor's office know what to begin typing variable? If the contractor's employee types "nipple" in the product description above, the lookup could return many different classes of pipe nipples, or no matches at all, depending on the indexing scheme chosen by the supplier. In time the user might figure out that "A53" would give them a specific list of threaded pipe nipples but it is unrealistic to expect them to become instant experts on the supplier's parts list!

My solution was to give the contractor a Cascading Search option. My Cascading Search works like the Cascading Lookup that Alpha Five provides, but unlike the built-in Cascading Lookup it is not limited to a fixed number of selections defined in field rules, but instead can work on large tables of data. The user first selects the general category of material from a drop-down listbox. I then use the general category to filter the parts that are shown. Once the browse of parts in the proper category is displayed, an OnKey progressive search is used to select the exactly matching part.

Here is a screen shot of the search in action:

Figure 1. The Cascading Progressive Search in action. The user first chooses PIPE NIPPLES A53 from the listbox of general categories. Then whatever is typed in the LINE CHOICE edit control will serve as the basis for the OnKey progressive search.

The script for the OnKey progressive search is setup the same as in Peter's earlier article.

After looking at the original script attached to the OnKey event of the form I noticed that it used 2 variables that I needed to consider. The "list_choice" variable used to display the keys that the user typed & "tempstr" another variable that is used to store the keystrokes, pass its value to "list_choice" and orders the browse via table.current().fetch_find(tempstr). I created both of these variables on the vendor’s inventory form as session level variables. I assigned "list_choice" as a type-in field type & "tempstr" as a listbox field type. All that was left was to fill in the choices for the listbox with "labels" that the contractor would recognize & "values" that the search would use to order the browse accordingly.

Figure 2. Assign "listbox" as the field type for "tempstr".

Figure 3. Fill the choices for the listbox with labels that make sense to the contractor.

Actually there was a couple of things that I still needed to do. One was to get the "value" from the "tempstr" listbox into the "list_choice" variable. This was accomplished with this simple script attached to the OnChange event of the tempstr’s container "listbox":

Figure 4. The OnChange script for "listbox".

I saved the form & put it in view mode to see how it would work. When the form is called the "line_choice" variable is empty. At that point the user can type a string directly into the line_choice field, but if the user selects an item from the "tempstr" listbox the backspace key will not delete a character from the line_choice field until the user enters an additional character from the keyboard. Then backspace will clear line_choice. I decided to included an additional button on the form to clear the value of line_choice & tempstr when that condition exists:

Figure 5. The OnPush script for the "Clear_line" button.

The changes that I made to Peter Wayne's original OnKey script were minor. The results of the changes are just what I needed to for the contractor. This is something that the contractor can use right away with terms with which they are familiar!

The scripts, the table and the variables

As I wrote before, the script for the Onkey progressive search is setup the same as in the earlier OnKey article by Peter Wayne. I created the form in Figure 1 and attached it to the supplier's parts inventory table. On this form I placed 2 session variables, "list_choice", and [ "tempstr" type=list box ] & an embedded browse of the inventory table. The table primary index expression is "keyword(descriptio)" & is used in the OnKey event script of the form. The next script is attached to the tempstrvariable in the OnChange event. It sets the value of tempstr (also used in the forms OnKey event) & writes it to the "line_choice" variable.

Here are the scripts:

dim shared tempstr as c
dim shared posn as n
posn=0
tempstr=""
 

Script 1. OnActivate form event.

dim shared tempstr as c
dim shared posn as n
dim shared line_choice as c
dim up as c
dim repeat as c
table.current().index_primary_put("descriptio")
if this.active()="line_choice" then
 select 
 case a_user.key.event="down"
 if left(a_user.key.value,1)="{" then 
 ' it's a control 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))
 a_user.key.handled=.t.
 end if 
 case else
 ' pass through
 a_user.key.handled=.f.
 end select
 else
 posn=posn+1
 tempstr=tempstr+a_user.key.value
 a_user.key.handled=.t.
 end if
 case a_user.key.event="up"
 up=a_user.key.value
 a_user.key.handled=.t.
 case a_user.key.event="repeat"
 repeat=a_user.key.value
 a_user.key.handled=.t.
 end select
 table.current().fetch_find(tempstr)
 Browse1.refresh()
 line_choice=tempstr
 line_choice.refresh()
end if

Script 2. OnKey form event.

tbl=table.current()
dim line_choice as c
line_string=:Mcj_Lnv:listbox.value
:Mcj_Lnv:Line_Choice.Activate()
Line_Choice=line_string
sys_send_keys("{F5}")
end

Script 3. OnChange event for the tempstr listbox.

9/27/99

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

Return to home