Tutorial 03 - Locate records

Let's add search capability to our small application. Make a copy of the tutorial 02 project and add the following components:

• TEdit
  • Name: SearchEdit
  • Text: "" (Empty)
• TButton
  • Name: SearchButton
  • Caption: Search

Design overview

The design consists of a edit component where the user will put the search text (contact name or part of) and an associated button. When the button is pressed for the first time, the search starts from the first record. Pressing the button again, will continue the search from the current record. Changing the text resets the search and next time the search will start from the first record again. If no record was found the edit color will be changed to red.

There are other, and more useful, ways of implementing such feature, like live search, but to keep code simple the current design was chosen.

Implementation

In the OnClick event of SearchButton put:

var
  SearchText: String;
  Options: TLocateOptions;
  Ok: Boolean;
begin
  SearchText := Trim(SearchEdit.Text);
  if SearchText = '' then
    Exit;
  Options := [loCaseInsensitive, loPartialKey];
 
  if FSearching then
    Ok := ContactsDataset.LocateNext('Name', SearchText, Options)
  else
    Ok := ContactsDataset.Locate('Name', SearchText, Options);
  FSearching := True; 


  if Ok then
    SearchEdit.Color := clWindow
  else
    SearchEdit.Color := clRed;  
end;

Explanation:

  SearchText := Trim(SearchEdit.Text);
  if SearchText = '' then
    Exit;
 

This code gets the search text from the edit component and check if is not empty. Trivial.

  Options := [loCaseInsensitive, loPartialKey];

Set the options that will be passed to Locate* functions. loCaseInsensitive means the search will match characters with different case. loPartialKey means that a record will be matched if starts with the search text.

It's also possible to use wildcards in the search key, e.g., searching for '*Paul*' will match any value that has 'Paul' regardless of the position. To enable this option set soWildCardKey in ContactsDataset.Options.

soWildCardKey  and loPartialKey are mutually exclusive with the later taking precedence. So in order to soWildCardKey take effect, loPartialKey must not be passed in options

  if FSearching then
   Ok := ContactsDataset.LocateNext('Name', SearchText, Options)
  else
   Ok := ContactsDataset.Locate('Name', SearchText, Options); 

FSearching is a global property that is set if a search was already started. More on this later.

Locate and LocateNext takes the field to be searched as first parameter and the value that must be matched in the second parameter. The options is passed in third parameter. Both returns True if a record that matches the parameters is found. The difference between the two is that Locate starts the search from the first record while LocateNext starts from the active record.

In the OnChange event of SearchEdit put:

  FSearching := False;
  SearchEdit.Color := clWindow; 

Trivial: resets the searching flag and the edit color.

Done

Is possible to search/filter records in more ways using TSqlite*Dataset. We'll see this later.

The project file can be found here

I could not resist and made an live search version ;-)