t e m p o r a l 
 d o o r w a y 

Interactively Filtering A Table

  

A question which often arises is how to display only a subset of a table or query to the user. The following is a form which implements an interactive filtering of the records from a TTable as the user types in a value.

The combos at the bottom allow the user to select from available databases, tables, and fields. The user can type a filtering string in the Filter With edit. The timer prevents the immediate application of the filter - the filter is only applied after its interval passes without a keystroke in the Filter With edit.

The following is the .dfm

object MainForm: TMainForm
   Left = 2
   Top = 156
   Width = 696
   Height = 480
   Caption = 'Test Database Access'
   Color = clBtnFace
   Font.Charset = DEFAULT_CHARSET
   Font.Color = clWindowText
   Font.Height = -13
   Font.Name = 'MS Sans Serif'
   Font.Style = []
   OldCreateOrder = False
   OnShow = FormShow
   PixelsPerInch = 120
   TextHeight = 16
   object Grid: TDBGrid
      Left = 0
      Top = 0
      Width = 688
      Height = 384
      Align = alClient
      DataSource = Source
      TabOrder = 0
      TitleFont.Charset = DEFAULT_CHARSET
      TitleFont.Color = clWindowText
      TitleFont.Height = -13
      TitleFont.Name = 'MS Sans Serif'
      TitleFont.Style = []
   end
   object ControlPanel: TPanel
      Left = 0
      Top = 384
      Width = 688
      Height = 67
      Align = alBottom
      Color = clTeal
      TabOrder = 1
      object DatabaseComboLabel: TLabel
         Left = 8
         Top = 40
         Width = 55
         Height = 16
         Caption = 'Database'
         Font.Charset = DEFAULT_CHARSET
         Font.Color = clWhite
         Font.Height = -13
         Font.Name = 'Arial'
         Font.Style = [fsBold, fsItalic]
         ParentFont = False
      end
      object TableComboLabel: TLabel
         Left = 160
         Top = 40
         Width = 34
         Height = 16
         Caption = 'Table'
         Font.Charset = DEFAULT_CHARSET
         Font.Color = clWhite
         Font.Height = -13
         Font.Name = 'Arial'
         Font.Style = [fsBold, fsItalic]
         ParentFont = False
      end
      object FieldLabel: TLabel
         Left = 312
         Top = 40
         Width = 31
         Height = 16
         Caption = 'Field'
         Font.Charset = DEFAULT_CHARSET
         Font.Color = clWhite
         Font.Height = -13
         Font.Name = 'Arial'
         Font.Style = [fsBold, fsItalic]
         ParentFont = False
      end
      object FilterValueEditLabel: TLabel
         Left = 464
         Top = 40
         Width = 65
         Height = 16
         Caption = 'Filter With'
         Font.Charset = DEFAULT_CHARSET
         Font.Color = clWhite
         Font.Height = -13
         Font.Name = 'Arial'
         Font.Style = [fsBold, fsItalic]
         ParentFont = False
      end
      object FilterValueEdit: TEdit
         Left = 464
         Top = 8
         Width = 217
         Height = 24
         TabOrder = 0
         OnChange = FilterValueEditChange
      end
      object DatabaseCombo: TComboBox
         Left = 8
         Top = 8
         Width = 145
         Height = 24
         ItemHeight = 16
         Sorted = True
         TabOrder = 1
         OnChange = DatabaseComboChange
      end
      object TableCombo: TComboBox
         Left = 160
         Top = 8
         Width = 145
         Height = 24
         ItemHeight = 16
         Sorted = True
         TabOrder = 2
         OnChange = TableComboChange
      end
      object FieldCombo: TComboBox
         Left = 312
         Top = 8
         Width = 145
         Height = 24
         ItemHeight = 16
         Sorted = True
         TabOrder = 3
      end
   end
   object Table: TTable
      Left = 32
      Top = 152
   end
   object Source: TDataSource
      DataSet = Table
      Left = 32
      Top = 192
   end
   object FilterTimer: TTimer
      Enabled = False
      OnTimer = FilterTimerTimer
      Left = 32
      Top = 240
   end
end

This is the code from the .cpp

//---------------------------------------------------------------------------
#include <vcl.h>
#pragma hdrstop

#include "MainFormUnit.h"
//---------------------------------------------------------------------------
#pragma package(smart_init)
#pragma resource "*.dfm"
TMainForm *MainForm;
//---------------------------------------------------------------------------
__fastcall TMainForm::TMainForm(TComponent* Owner)
   : TForm(Owner)
{
}
//---------------------------------------------------------------------------
void __fastcall TMainForm::FormShow(TObject *Sender)
{
   TStringList *DatabaseNames = new TStringList;

   try
   {
      Session->GetDatabaseNames(DatabaseNames);
      DatabaseCombo->Items->Assign(DatabaseNames);
   }
   catch (Exception &VCLException)
   {
   }
   catch (...)
   {
   };

   delete DatabaseNames;
}
//---------------------------------------------------------------------------
void __fastcall TMainForm::DatabaseComboChange(TObject *Sender)
{
   const String Pattern = "";
   const bool NoSystemTables = false; // false means "NoSystemTables"

   String   DatabaseName = DatabaseCombo->Items->Strings[DatabaseCombo->ItemIndex];
   String   AliasDriverName;

   try
   {
      AliasDriverName = Session->GetAliasDriverName(DatabaseName);
   }
   catch (Exception &VCLException)
   {
      AliasDriverName = "Unknown";
   };

   TStringList   *TableNames = new TStringList;

   try
   {
      Session->GetTableNames(DatabaseName, Pattern, /* Use extensions to table name when */ AliasDriverName == "STANDARD", NoSystemTables, TableNames);
      TableCombo->Items->Assign(TableNames);
   }
   catch (Exception &VCLException)
   {
      Application->MessageBox(String("Unexpected exception getting table names - " + VCLException.Message).c_str(), "TMainForm::DatabaseComboChange", MB_ICONWARNING);
   }
   catch (...)
   {
      Application->MessageBox("Unexpected exception of unknown type getting table names", "TMainForm::DatabaseComboChange", MB_ICONWARNING);
   };

   delete TableNames;
}
//---------------------------------------------------------------------------
void __fastcall TMainForm::TableComboChange(TObject *Sender)
{
   Table->DatabaseName = DatabaseCombo->Items->Strings[DatabaseCombo->ItemIndex];
   Table->TableName = TableCombo->Items->Strings[TableCombo->ItemIndex];
   Table->Active = true;

   FieldCombo->Items->Clear();

   for (int Index = 0; Index < Table->FieldCount; Index++)
   {
      FieldCombo->Items->Add(Table->Fields->Fields[Index]->FieldName);
   };
}
//---------------------------------------------------------------------------
void __fastcall TMainForm::FilterTimerTimer(TObject *Sender)
{
   FilterTimer->Enabled = false; // Stop until reenabled by another keystroke

   if (Table->Active)
   {
      Table->DisableControls();

      try
      {
         Table->Filtered = false;

         try // Quoted filter
         {
            if (FilterValueEdit->Text.Length() > 0)
            {
               Table->Filter = FieldCombo->Items->Strings[FieldCombo->ItemIndex] + "='" + FilterValueEdit->Text + "*'";
            }
            else
            {
               Table->Filter = "";
            };

            Table->Filtered = (FilterValueEdit->Text.Length() > 0); // Throws an exception if the field is not a string
         }
         catch (Exception &VCLException) // Field may not be a string
         {
            // Unquoted filter

            if (FilterValueEdit->Text.Length() > 0)
            {
               Table->Filter = FieldCombo->Items->Strings[FieldCombo->ItemIndex] + "=" + FilterValueEdit->Text;
            }
            else
            {
               Table->Filter = "";
            };

         Table->Filtered = (FilterValueEdit->Text.Length() > 0);
         };
      }
      catch (Exception &VCLException)
      {
         Application->MessageBox(String("Unexpected exception setting filter - " + VCLException.Message).c_str(), "TMainForm::FilterTimerTimer", MB_ICONWARNING);
      }
      catch (...)
      {
         Application->MessageBox("Unexpected exception of unknown type setting filter", "TMainForm::FilterTimerTimer", MB_ICONWARNING);
      };

      Table->EnableControls();
   };
}
//---------------------------------------------------------------------------
void __fastcall TMainForm::FilterValueEditChange(TObject *Sender)
{
   if (FilterTimer->Enabled) FilterTimer->Enabled = false; // Reset the timer
   FilterTimer->Enabled = true; // Wait until no keystrokes for the timer interval before applying the filter
}
//---------------------------------------------------------------------------

The following points are of interest:

  • When the form is shown the DatabaseName combo is loaded with the available database names from the Session object.

  • When the DatabaseName combo item is selected, the TableName combo is loaded with the table names for that database from the Session object.

  • When the TableName combo item is selected, the DatabaseName and TableName properties of the Table object are set and it is made active. The FieldName combo is then set to the list of fields for the newly opened table.

  • The user can enter text into the FilterValueEdit. However, that only starts (or restarts) a timer. The timer will therefore only fire when there have been no keystrokes for its Interval.

  • When the timer finally fires, the filter is applied. If the filter fails as a partial text filter, it is reapplied as an unquoted (numeric) filter. If both fail, an error message is produced.

  • Exception handling is present for most situations, especially to ensure that the lists used to accumulate the names are freed.

Note that the names could be directly loaded into the combo items, for instance, with...

      Session->GetTableNames(DatabaseName, Pattern, /* Use extensions to table name when */ AliasDriverName == "STANDARD", NoSystemTables, TableCombo->Items);

which would eliminate the need to allocate and delete the intermediate lists.

Want to work with the code? Shift-click to download a zip file of the form and the .h and the .cpp. Unzip the contents and add them to a project of your own. Note that the form was created on a screen with 1024 x 768 large fonts, but should adapt to size changes because of its use of the Align property for the form elements. Also note that the .exe is included, and uses runtime packages to conserve space.

WARNING: You accept any and all risk involved in using the download, the zip file, the contents of the zip file, and the included .exe. Make sure to back up your system, and scan the zip and its content with a virus scanner for maximum safety.

Copyright © 2004 by Mark Cashman (unless otherwise indicated), All Rights Reserved