DATA SEARCHING

Searching for values/records in non-indexed fields.
Incremental Searching Part 2.

   Preface
In the Incremental searching article, we saw one algorithm that searches for values in a database table. GoToNearest method was used to find the nearest match to a partial field value. In order to use the GoToNearest an application must specify the IndexName property of a table. In other words if we want to search for some Company we have to make sure that Company field is defined as secondary index (ByCompany).

Many of you have mailed me with questions like: "How can I search for records in a table on non-indexed field?" In this article we'll extend the Incremental filling search technique to find a value in any (every) field - indexed or non-indexed

   GotoData
First the function. lTable is our Table to search, lField is TField we've added with the Fields Editor and lValue is the string value we want to find in lField. The GotoData returns True if the value (more precisely: nearest match) was found False otherwise.

function GotoData (const lTable: TTable;
                   const lField: TField;
                   const lValue: String) : Boolean;
var bMark : TBookMark;
    sPart : string
begin
 Result := False;
 if not lTable.Active then Exit;
 if lTable.FieldDefs.IndexOf(lField.FieldName)
  < 0 then Exit;
 bMark := lTable.GetBookMark;
 With lTable do begin
   DisableControls;
   try
     First;
     While not EOF do begin
       spart:=Copy(lField.AsString,1,Length(lValue));
       if LowerCase(spart) = LowerCase(lValue) then
       begin
         Result := True;
         Break;
       end
       else Next;
     end; {while}
   finally
     EnableControls;
   end; {try}
 end; {with}
 if (Not Result) then lTable.GotoBookMark(bMark);
 lTable.FreeBookMark(bMark);
end;

   Project
To see how this function works we'll use the project (and the code) from the Incremental searching article.

In order to use our GotoData function we have to place one TComboBox component on a form. Change combo's name to cboFields, set its Style property to csDropDownList. We fill this combo with Field Names in the main form's OnCreate event handler.

//fill the combo with Field Names
for i:=0 to Table1.FieldCount - 1  do
    cboFields.Items.Add(Table1.Fields[i].FieldName);

The GotoData function is then used in the edSearch OnChange event handler. Simply replace the code

//goto nearest match
with Table1 do begin
  SetKey;
  FieldByName('Company').AsString:=edSearch.text;
  GotoNearest;
end;
sfind := Table1.FieldByName('Company').AsString;

with

//GotoData-goto nearest match
if GotoData(Table1,
            Table1.FieldByName(cboFields.Text),
            edSearch.Text) then
 sfind := Table1.FieldByName(cboFields.Text).AsString
else
 sfind := '';

   Final words
Be sure to download this project. As always, if you need some help with code, don't hesitate to mail me, I'll be glad to explain it a bit more...

   Related