How to add incremental search to your (database) application.
Just a few words
Today, developing database applications using Delphi is easy as clicking a mouse button. With the help of the Database Form Wizard, we can create simple (or even complex) database aware forms/programs in seconds.
Every Delphi developer can build data processing application, but only those who take care of the user will rise among others. If we want our programs to be "user friendly" we have to provide some additional routines that will speed up data manipulation.
This article will be dealing with data searching.
In order to fully understand the code and techniques described in this article, it will be desirable for you to have some basic knowledge of Delphi's database programming (specifically: indexes and keys) and Object Pascal in general
Building a simple data previewing form
We will start this tutorial with Database Form Wizard. Of course, everything the wizard does, we can do by hand but the wizard saves a lot of time. Use the Database Form Wizard to build a single-table main form that displays all the fields of customer.db (dbdemos alias) in a grid. To make the grid display data sorted, use Object Inspector to change the TTables component IndexName property to ByCompany.
Now, we need to place one TEdit control somewhere on the form, name it edSearch. (Use Object Inspector to change TEdit's name property to 'edSearch'). If you want to, you can remove TDBNavigator control - we don't need it for this simple project.
TTable has a number of functions that will search for values in a database. Some of these are Goto, GoToKey, GoToNearest, Find, FindKey, Find Nearest, etc. For a complete reference see Delphi's help, topic: Searching for records based on indexed fields.
In order to provide simple search mechanism, in our application; add this code to handle the edits OnChange event as follows:
procedure TForm1.edSearchChange(Sender: TObject); begin with Table1 do begin SetKey; FieldByName('Company').AsString:=edSearch.text; GotoNearest; end; end;
As you can read from Delphi' help, this is just one of the few ways we can use to locate desired records. GotoNearest method of TTable component finds the nearest match to a partial field value.
Run your application, and as you type letters in edit control Delphi moves the cursor to the record that most closely matches the text in edSearch.
Note: if you don't understand what does SetKey do, don't be lazy: read the help files.
This technique is commonly known as incremental search.
Some more spicy solution
Let us now extend our search "algorithm" with a few lines of code. The idea is to fill the text property of edSearch control with the value of the selected (found) record - we want our users to be aware (a bit more) of found record and its value.
Of course, we have to provide the same functionality as before. I think that this technique and its final result will be much more eye candy - user friendly.
Incremental filling search
Replace edits OnChange event with the following code:
procedure TForm1.edSearchChange(Sender: TObject); var txt, sfind:string; len:integer; begin //don't do anything if user presses //delete or backspace if edFromCode = true then begin edFromCode := false; exit; end; //don't do anything if there is //no text in edSearch txt:=edSearch.Text; if Length(txt)=0 then exit; //goto nearest match with Table1 do begin SetKey; FieldByName('Company').AsString:=edSearch.text; GotoNearest; end; //calculate what part of text should be selected sfind := Table1.FieldByName('Company').AsString; len := Length(sfind) - Length(txt); if len > 0 then begin edFromCode:=true; edSearch.Text:=sfind; edSearch.SelStart:=Length(txt); edSearch.SelLength:=len; end; end;
For this technique, to be fully functional, we have to write some code in edits OnKeyDown event handler:
procedure TForm1.edSearchKeyDown (Sender: TObject; var Key: Word; Shift: TShiftState); begin if (Key=VK_DELETE) or (Key=VK_BACK) then begin if Lenght(edSearch.Text)>0 then begin; //onchange event should not be executed... edFromCode := true; end; end; end;
This code will assure that our new search algorithm works with delete keys.
Note: edFromCode is Boolean type variable defined at the beginning of the unit (above all event handlers, but after the implementation line) - it is global for this unit (we are referencing edFromCode from two different procedures).
Try running your program now. While you type letters in edit control, Delphi finds nearest match and fills its value in edit control.
I hope you will find some place in your applications for this approach to data searching. If you need some help with code, don't hesitate to mail me, I'll be glad to explain it a bit more... Finally, if you want to provide even more visible approach to data manipulating (using DBGrid) check out Coloring DBGrid article. For non-indexed field searching check Data Searching Part 2.