by Dan Maas
| Dan Maas is a former Foxpro programmer who is making the transition to Alpha Five. He shows, here, a way to provide a full text search capability for lookups. Dan doesn't have all the answers, and he leaves us with a question (a challenge?) at the end. You can reach Dan at dmaas@mcn.org |
One of the things that I have noticed in reading the forums and looking at the various database programs is that they all seem to come up short in one area. That of searching for records. They all expect the user to either know some Part Number that has no bearing on the item being looked for or there is some multi-fielded table or query that needs to be filled in with the starting portions of what may be the searched item. There are ways to make the computer search for a string anywhere in the field but this usually requires cryptic search punctuation such as *, $, or logic keys such as .AND. , .OR. and so forth. These methods are fine for an application that has few items or the user has a lot of time.
In the real world, however, I’ve found that neither time or small databases are the norm. This brings to the forefront the need to be able to, with just a few keystrokes, locate the item that is needed. The only way to do this is to take a small amount of input and return a manageable list of choices within a few keystrokes. I call this Intelligent Searching or Lookups.
Alpha Five ships with a sample application called invoice in the Samples\Invoice directory which I would like to use to demonstrate this simple method of making the lookup function provided by alpha infinently more useable. I should warn you that I am not an Xbasic Programmer and only wish I could use this in other areas. Assume that you work for the Store for which this application was written. Assume also that instead of the 25 or so items in the product database there are 2500 to 10,000 items which is still a fairly small amount. Now assume that you have to find the “Ray Block Sunglasses” in the product database but your not sure what the description is and you surely don’t know the part number!
Go ahead, run the sample invoice program, pull up a customer invoice and try to find the “Block Ray Sunglasses”. When you add an invoice item you are presented with the entire Product database (which in this case is very small) so with a minimal amount of scrolling you can find what your looking for. But what if this database is large? It would help to have the lookup sorted by Description but we aren’t sure if these are “ray blockers” or “Glasses” or what. Maybe they are NYKEY ray blocking sunglasses, or …well you get my meaning.
The solution is a simple filter applied on the fly. This requires a quick queried index generation but is still much quicker than filling out multiple forms or trying to use Alpha’s locate in the lookup function. Try altering the standard sample invoice program like this and see if this would work in your application.
First of all add a global variable to the Invoice Set which will hold our lookup information:
Then modify the Lookup field rule for the Invoice_Items:Product_ID and add the following expression to the filter line of the lookup parameters containsi(product_id+description,"*"+trim(var->lookup)+"*") this will cause the lookup display to only show the records that match our “Lookup” variable anywhere in either the Product_ID or Description Fields. We could add more fields if needed but for this application these will be adequate. Also change the lookup Display Order to Description instead of Product_ID as this will make more sense when we're trying to find something. Like So:
Finally we need to add the lookup variable to our invoice detail browse so that it is right there when we add a new record. So edit the invoice_items browse in the Invoice form and add the lookup variable to the embedded browse as follows:
Now save the form and run the application. Choose the invoice option and you’ll see invoice number 00001. Click on the Invoice Items tab and you will see your items browse with the lookup field. Scroll down the list till you add a new invoice item and your cursor is in the lookup field of the browse.
Now you will remember that we were going to try to locate the “Ray Block Sunglasses” but we don’t know how they have been entered. Just type any portion of any word into the lookup field such as Glass or Ray or Block or Sun or even a portion of the Product Id:
Then when you tab into the Prod No field the lookup popup will appear with all the matches presented in a manageable size:
If you don’t see what you want you can just hit escape and change the lookup field and try again. You will find what you're looking for very quickly. Even someone who has never looked for anything in your database before will be able to find things in short order.
I know this is probably an unorthodox method for doing this but as I said at the outset, I am not an Xbasic Programmer but needed a better lookup. This has worked well for us. The lookup field for every record will change to the last search string, as they are all the same variable. But this sure speeds up the invoice entry in our shop.
Now the whole reason for my purchasing Alpha Five was for the “FULL TEXT INDEXING AND SEARCHING” which seems like it should work here. I have tried without success to use a full text index in the filter expression of the lookup feature of A5. If someone could show me if this is possible the index wouldn’t have to be built every time. Also, if someone could enlighten me on other ways to accomplish this in other areas with some Xbasic programming (like a user function that was adaptable to any lookup anywhere) it would be greatly appreciated.
9/28/00
Don't forget, we need your feedback to make this site better!