DBGrid filtering

The QuantumGrid by Developer Express provides Excel style filtering, Outlook Filtering and lots of inplace editors. I think it's quite expensive though. Their web site is http://www.devexpress.com

Hi nnbbb09

Thanks for reply, excellent suite of components but far too expensive.

Any other suggestions?

Rgds Palin

not perfect, but mayby an idea

unit dbg_filter_u;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  Grids, DBGrids, DBTables, Db, StdCtrls;

type
  TForm1 = class(TForm)
    Table1: TTable;
    DataSource1: TDataSource;
    Query1: TQuery;
    DBGrid1: TDBGrid;
    cbFilterBox: TComboBox;        //a hidden combobox (Style = csDropDownList)
    procedure Table1AfterOpen(DataSet: TDataSet);
    procedure Table1AfterPost(DataSet: TDataSet);
    procedure DBGrid1TitleClick(Column: TColumn);
    procedure cbFilterBoxChange(Sender: TObject);
    procedure cbFilterBoxClick(Sender: TObject);
    procedure cbFilterBoxExit(Sender: TObject);
  private
    Procedure FillPickLists(ADBGrid : TDBGrid);
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.DFM}

//For Accessing some Protected Methods
type TCDBGrid = class(TCustomDBGrid);

//Storing the Values into the Picklist-Propertys of the asscociated Columns,
//this may cost time depending on the amount of the dataset
Procedure TForm1.FillPickLists(ADBGrid : TDBGrid);
const
  SQL_Text = 'Select Distinct %s From %s';
var
  q : TQuery;
  i : integer;
Begin
  If (Assigned(ADBGrid)) and
     (Assigned(ADBGrid.Datasource)) and
     (Assigned(ADBGrid.Datasource.DataSet)) Then
  Begin
    If (ADBGrid.Datasource.DataSet is ttable) Then
    begin
      q := TQuery.Create(self);
      try
        try
          q.DatabaseName := TTable(ADBGrid.Datasource.DataSet).DataBaseName;
          for i := 0 to ADBGrid.Columns.Count - 1 do  //for each column
          begin
            if ADBGrid.Columns[i].Field.FieldKind = fkData then  //only physical fields
            begin
              ADBGrid.Columns[i].ButtonStyle := cbsNone;  //avoid button-showing
              ADBGrid.Columns[i].PickList.Clear;
              q.Close;
              q.SQL.text := Format(SQL_Text,[ADBGrid.Columns[i].Field.FieldName,TTable(ADBGrid.Datasource.DataSet).TableName]);
              q.Open;
              While not q.eof do
              begin
                ADBGrid.Columns[i].PickList.Add(q.Fields[0].AsString);
                q.next;
              end;
              q.close;
            end;
          end;
        finally
          q.free;
        end;
      except
        raise;
      end;
    end else
      Raise exception.Create('This Version works only for TTables');
  end else
    Raise Exception.Create('Grid not properly Assigned');
end;

//Initial-Fill
procedure TForm1.Table1AfterOpen(DataSet: TDataSet);
begin
  FillPickLists(DBGrid1);
end;

//Refill after a change
procedure TForm1.Table1AfterPost(DataSet: TDataSet);
begin
  FillPickLists(DBGrid1);
end;


//Show a Dropdownbox for selecting, instead the title on Titleclick
procedure TForm1.DBGrid1TitleClick(Column: TColumn);
var
  ARect : Trect;
  DummyTC : TColumn;
begin
  If column.PickList.Count > 0 then
  begin
    cbFilterbox.Items.Assign(column.PickList);
    ARect := TCDBGrid(Column.Grid).CalcTitleRect(Column,0,DummyTC);
    cbfilterBox.top := Column.Grid.Top+1;
    cbfilterBox.left := Column.Grid.left+Arect.Left+1;
    cbFilterbox.Width := Column.Width;
    cbFilterBox.Tag := Integer(Column); //Store the columnPointer
    cbFilterBox.Show;
    cbFilterBox.BringToFront;
    cbFilterBox.DroppedDown := True;
  end;
end;

//Build up the Filter
procedure TForm1.cbFilterBoxChange(Sender: TObject);
begin
  cbFilterBox.Hide;
  if cbFilterBox.Text <> TColumn(cbFilterBox.Tag).Title.Caption then
  begin
    Case TColumn(cbFilterBox.Tag).Field.DataType of
      //Some Fieldtypes
      ftstring :
                 TTable(TDBGrid(TColumn(cbFilterBox.Tag).Grid).Datasource.Dataset).Filter :=
                   TColumn(cbFilterBox.Tag).Field.FieldName+' = '+QuotedStr(cbFilterBox.Text);

      ftInteger,
      ftFloat  :
                 TTable(TDBGrid(TColumn(cbFilterBox.Tag).Grid).Datasource.Dataset).Filter :=
                   TColumn(cbFilterBox.Tag).Field.FieldName+' = '+cbFilterBox.Text;
    end;
    TTable(TDBGrid(TColumn(cbFilterBox.Tag).Grid).Datasource.Dataset).Filtered := True;
  end;
end;

//some Hiding-events
procedure TForm1.cbFilterBoxClick(Sender: TObject);
begin
  cbFilterBox.Hide;
end;

procedure TForm1.cbFilterBoxExit(Sender: TObject);
begin
  cbFilterBox.Hide;
end;

end.

meikl ;-)

meikl

Excellent piece of coding, I can learn from this as well as hopefully solving my request.

As yet I've not got it to run so could you check I'm doing everything I need to get it to work.

It's failing to compile at the following line of code:

ARect := TCDBGrid(Column.Grid).CalcTitleRect(Column,0,DummyTC);

Problem with CalcTitleRect, any ideas?.

I am doing this correctly, placing the following components on form:

TTable (Tablel1)- setting it to DBDemos & OrdersDB Active True
TDataSource (DataSource1) - setting it to Table1
DBGrid1 - setting it to DataSource1
TQuery (Query1) - not set to anything, unsure as you have Query1 Create(self) in code
TComboBox (cbFilterBox) - set as csDropDownList

I'd really like to get this to work could you check I've not missed something obvious like setting part of your code, not stated by yourself because you expected me to know without saying. For instance should I be adding to this piece of code "SQL_Text = 'Select Distinct %s From %s';"

Please treat me like an idiot or at the best a beginner(of which I am).

Thank you for your time.

Rgds Palin

hi palin,

well the tquery is obsolete in my sample and can be deleted,

about the calctitlerect, that is a protected method of TCustomDBGrid (in delphi 5),
to access this method, i've defined a type TCDBGrid for a typecast
this declaration is
type TCDBGrid = Class(TCustomDBGrid);
at the beginning of the implementation part

calctitlerect is needed to get the current postion of the title in the grid,
where to place the combobox instead.

atleast this method is defined in the delphi 5 version and may not available in earlier version
(don't know about d4, but d3 didn't have this method). if you don't have d5,
then the CellRect-Method from TCustomGrid could be used instead.

if you leave your email, then i could send you a better commented running sample based on DBDemos/Orders.DB
including filter-capactity of Date and Currency fields.

about
SQL_Text = 'Select Distinct %s From %s';

%s are placeholders, which are replaced with field- and tablename
with the format-function at runtime,
no changes needed

the query (i use a runtime-created) is needed to collect the unique fieldvalues of one column

tell me what delphi version you use

meikl ;-)

Hi meikl

Thanks again for your reply, I see the problem now as I am in fact still on Delphi 3.

I would appreciate if you would contact me further on this as it has taken my interest as well as solving my request, my e-mail is john.hernan@ntlworld.com

Speak to you soon.

Rgds Palin

well ok,
i transform the sample down to d3 and
send it to you.

this will take a bit time, but you will get it within 24 hours.

meikl ;-)

Thanks meikl look forward to it.

Rgds Palin

Hi Meikl

Thanks again for all your help, I'm off now to play with my new grid.

Please accept the points for this question, hope to speak to you again.

Rgds John