Solved

DBGrid filtering

Posted on 2001-06-07
10
2,293 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
  • 5
  • 4
10 Comments
 
LVL 3

Expert Comment

by:nnbbb09
Comment Utility

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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:palin2000
Comment Utility
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
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:palin2000
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks meikl look forward to it.

Rgds Palin
0
 
LVL 27

Accepted Solution

by:
kretzschmar earned 200 total points
Comment Utility
d3 version on the way :-)
0
 

Author Comment

by:palin2000
Comment Utility
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 Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

The uses clause is one of those things that just tends to grow and grow. Most of the time this is in the main form, as it's from this form that all others are called. If you have a big application (including many forms), the uses clause in the in…
Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now