Searching Records

Delphi Database, Delphi Components from ComponentAce

Searching Records

Previous Top Next

You can search against any active table or query using the Locate and Lookup methods. These methods enable you to search on any type of columns in any dataset.
However, by explicitly using indexes, FindKey, GotoKey and other similar methods of TABSTable can improve over the searching performance provided by the Locate and Lookup methods.

Using Locate

Locate moves the cursor to the first row matching a specified set of search criteria. In its simplest form, you pass Locate the name of a column to search, a field value to match, and an options flag specifying whether the search is case-insensitive or if it can use partial-key matching. (Partial-key matching is when the criterion string need only be a prefix of the field value.) For example, the following code moves the cursor to the first row in the CustTable where the value in the Company column is "Professional Divers, Ltd.":

var
  LocateSuccess: Boolean;
  SearchOptions: TLocateOptions;
begin
  SearchOptions := [loPartialKey];
  LocateSuccess := CustTable.Locate('Company', 'Professional Divers, Ltd.', SearchOptions);
end;

If Locate finds a match, the first record containing the match becomes the current record. Locate returns True if it finds a matching record, False if it does not. If a search fails, the current record does not change.
The real power of Locate comes into play when you want to search on multiple columns and specify multiple values to search for. Search values are Variants, which means you can specify different data types in your search criteria. To specify multiple columns in a search string, separate individual items in the string with semicolons.

Because search values are Variants, if you pass multiple values, you must either pass a Variant array as an argument (for example, the return values from the Lookup method), or you must construct the Variant array in code using the VarArrayOf function. The following code illustrates a search on multiple columns using multiple search values and partial-key matching:

with CustTable do
  Locate('Company;Contact;Phone', VarArrayOf(['Sight Diver','P']), loPartialKey);

Locate uses the fastest possible method to locate matching records. If the columns to search are indexed and the index is compatible with the search options you specify, Locate uses the index.

Using Indexes to search for records

TABSTable supports similar indexed-based searches, but use a combination of related methods. The following table summarizes the six related methods provided by TABSTable to support index-based searches:

Method	Purpose
EditKey	Preserves the current contents of the search key buffer and puts the dataset into dsSetKey state so your application can modify existing search criteria prior to executing a search.
FindKey	Combines the SetKey and GotoKey methods in a single method.
FindNearest	Combines the SetKey and GotoNearest methods in a single method.
GotoKey	Searches for the first record in a dataset that exactly matches the search criteria, and moves the cursor to that record if one is found.
GotoNearest	Searches on string-based fields for the closest match to a record based on partial key values, and moves the cursor to that record.
SetKey	Clears the search key buffer and puts the table into dsSetKey state so your application can specify new search criteria prior to executing a search.

GotoKey and FindKey are boolean functions that, if successful, move the cursor to a matching record and return True. If the search is unsuccessful, the cursor is not moved, and these functions return False.
GotoNearest and FindNearest always reposition the cursor either on the first exact match found or, if no match is found, on the first record that is greater than the specified search criteria.

For example, the following code, attached to a button's OnClick event, uses the GotoKey method to move to the first record where the first field in the index has a value that exactly matches the text in an edit box:

procedure TSearchDemo.SearchExactClick(Sender: TObject);
begin
  ABSTable1.IndexName:='idxByLastNameFirstName';
  ABSTable1.SetKey;
  ABSTable1.Fields[0].AsString := Edit1.Text;
  if not ABSTable1.GotoKey then
    ShowMessage('Record not found');
end;

GotoNearest is similar. It searches for the nearest match to a partial field value. It can be used only for string fields. For example,

ABSTable1.IndexName:='idxByLastNameFirstName';
ABSTable1.SetKey;
ABSTable1.Fields[0].AsString := 'Sm';
ABSTable1.GotoNearest;

If a record exists with "Sm" as the first two characters of the first indexed field's value, the cursor is positioned on that record. Otherwise, the position of the cursor does not change and GotoNearest returns False.

Mar 22, 2023