Solved

DBGrid filtering

Posted on 2001-06-07
10
2,548 Views
Last Modified: 2008-02-01
Hi

Is there a component or code available that could be applied to Delphi's DBGrid to emulate the way MS Excel "Auto Filters" its spreadsheet. i.e. produces a list of values in a drop-down box for each colunm in the first row?.

All comments welcome.

Rgds Palin

0
Comment
Question by:palin2000
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
10 Comments
 
LVL 3

Expert Comment

by:nnbbb09
ID: 6165348

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
0
 

Author Comment

by:palin2000
ID: 6165862
Hi nnbbb09

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

Any other suggestions?

Rgds Palin

0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 6171539
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 ;-)
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:palin2000
ID: 6172100
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

0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 6172987
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 ;-)
0
 

Author Comment

by:palin2000
ID: 6174245
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
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 6175886
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 ;-)
0
 

Author Comment

by:palin2000
ID: 6176509
Thanks meikl look forward to it.

Rgds Palin
0
 
LVL 27

Accepted Solution

by:
kretzschmar earned 200 total points
ID: 6179108
d3 version on the way :-)
0
 

Author Comment

by:palin2000
ID: 6183735
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
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction The parallel port is a very commonly known port, it was widely used to connect a printer to the PC, if you look at the back of your computer, for those who don't have newer computers, there will be a port with 25 pins and a small print…
Introduction Raise your hands if you were as upset with FireMonkey as I was when I discovered that there was no TListview.  I use TListView in almost all of my applications I've written, and I was not going to compromise by resorting to TStringGrid…

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question