Visual Foxpro Autocomplete - Tips and Tricks
July 11th, 2007
Last night I spoke at the Atlanta Foxpro Users Group meeting on Autocomplete - Tips and Tricks.
Here is the issue/problem: The client wants a lookup against existing manufacturers and constrain them (for the most part) against the existing list, but would also like to be able to add a manufacturer on the fly with as little fuss as possible. My initial thought is the combo box, but you are constrained by the contents of the combo box on what can be chosen. So then I think of the new Autocomplete feature of the textbox.
Another issue is that the database for the system is in MySQL and can be large. Autocomplete only works against a VFP free table. So, how do you get autocomplete to work with a MySQL backend, large dataset, but constrained to a VFP free table?
I poked around the Internet in all of the usual places and I just couldn’t find any information on how to make Autocomplete work, especially not in the environment that I was running in. There were a couple of blog entries by Calvin Hsia and Jorge Mota that helped out a lot. Along with some additional testing, I was able to come up with something that worked out.
If Autocomplete would work against a cursor, this article would have been done right now, but Autocomplete works against a table sitting on the HD. I was really getting worried now. What if I wanted Autcomplete features against a table that is over 2G?
While testing, I was also trying to determine when the table was opened. Was it during the init of the form? Load of the form? Did it leave it open? I couldn’t really tell, because as far as I was able to determine, the Autocomplete table is opened in a private data session.
I was thinking I was going to fail in my endeavors so I went to the xSource directory to see if the source code for Autocomplete was in there. I was hoping to get some of my questions answered, and if I hit a wall, I could see what technology the VFP Team used in Autocomplete and roll my own to where it was acceptable. Unfortunately, the source for Autocomplete isn’t in the xSource code. Another potential roadblock.
All this worry was for naught however. Autocomplete was built in such a way as I was able to get everything working in an acceptable manner.
Enough already. Stop talking about the roadblocks and talk about the solution! {You didn’t know that I had multiple personalities, did you!?!}
To get autocomplete working, you must have three properties set in the textbox control: AutoComplete, AutoCompTable, and AutoCompSource. These are reasonably well documented but I’ll make some brief comments. AutoComplete stores a numeric between 0 (Off) and 4 that indicates what type of lookup you would like. AutoCompTable holds which VFP table to open for the AutoComplete lookup. Yes, this can be a full or partial path such as c:\temp\MyAutoCompTable or .\AutoCompData\MyAutoCompTable. This is good news in that you could specify a different table for each control if you would like. The VFP Team left it up to you on whether to have a single AutoComplete table or multiple. The AutoCompleteSource holds the value that is also used, in combination of the value of the textbox, in the lookup of the value in the AutoComplete table.
The first good news is that the Autocomplete table doesn’t appear to be opened until a value is typed into the text box. This means that on the Init of the textbox itself that I can manipulate the table and ZAP it if necessary. Why would you need to do this? Well, if you want to use Autocomplete against a MSSQL/MySQL/Oracle/PostgreSQL back end with a billion records in it, you may want to start out the day with an empty Autocomplete table.
The second good news is that the AutoComplete table isn’t opened exclusively. We would have guessed this as the VFP Team would want AutoComplete tables shared among users, and even within the same application using multiple instances of a form, and across multiple instances of the textbox even on different forms. This also means that we can open the AutoComplete table in our current datasession and insert records on the fly.
The third piece of good news is that the refresh of the AutoComplete display occurs after the InteractiveChange event. This means that we can do a lookup against our backend database and conditionally insert records into the AutoComplete table and have those values immediately reflected in the display to the user! We’re cooking with gas now!
We’re up to the fourth piece of good news. That’s a lot of good news considering how much concern we started with. Notice that with the late opening of the AutoComplete table after the init, and that the display of the AutoComplete values happens after the InteractiveChange event, means that all of this lends itself to building in custom methods into a textbox class. OOP as it should be. It would be simple to build some logic into the INIT of the textbox control to state “IF THIS.AutoComplete > 0 THEN THIS.AutoCompleteInit()” where AutoCompleteInit is a custom method that takes care of setting up the environment for AutoComplete and the needs of this control. Likewise in the THIS.InteractiveChange event, we could call to our AutoCompleteInteractiveChange method to do any custom lookups necessary.
The fifth piece of good news is that AutoComplete is not slow. I was curious and concerned about this as well. Unfounded worry, thankfully. AutoComplete adds its own index to the table. I know they have made speed improvements to LOCATE and the SQL SELECT, but nothing beats a fully utilized index on a table.
So there you go. Go forth and use AutoComplete where necessary and don’t worry! There are times where you want the user to be semi-constrained by the previous entries for a field (normally constrained by a combobox control) but also need the ability to add a value on the fly (not available normally via a combobox control). This is a great tool for the right job. VFP, once again, surprises me on what a great tool it is.