Solved

How do I filter A table?

Posted on 1998-05-02
13
370 Views
Last Modified: 2010-04-06
I want to make a next button that will find the next occurance of whatever is in Edit1.text and only within the field as chosen in the combobox1.text.
I think what i need to do is set a bookmark then go to the bookmark, filter the data based on what was entered in the edit1.text then go to the next occurance then Free bookmark then un filter the table. e.g
Choose 'finder' from 'combobox1' (Finder is the name of the field I want to search) then enter 'mike' in the 'edit1' box (Mike is what I want to look for)
Press a 'Search' button which uses locate to find the first occurance of 'mike'(I have got this working no problem), if the first occurance is not what I am after the I can Press the 'Next' Button to go to the next occurance of 'Mike'.
I can not get the find next command or the filter to get its information from the Edit1 or the Combobox1 components.
Please help!!! Can you show me the code if possible.
Cheers
Mike Ross.
0
Comment
Question by:pedro1
  • 7
  • 6
13 Comments
 
LVL 8

Accepted Solution

by:
ZifNab earned 40 total points
ID: 1340580
Hi Pedro1,

It seems that you still have problems, I'm sorry if you couldn't get further with my last help. But just say so, or just reject the given answer if you can't get further and you need extra help.

Hope this helps, I tested it on a table of DBDEMOS : EMPLOYEE.DB and it worked...

unit Unit1;

interface

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

type
  TForm1 = class(TForm)
    DataSource1: TDataSource;
    Table1: TTable;
    Edit1: TEdit;
    Button1: TButton;
    DBGrid1: TDBGrid;
    Table1EmpNo: TIntegerField;
    Table1LastName: TStringField;
    Table1FirstName: TStringField;
    Table1PhoneExt: TStringField;
    Table1HireDate: TDateTimeField;
    Table1Salary: TFloatField;
    Button2: TButton;
    procedure Button1Click(Sender: TObject);
    procedure Button2Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.DFM}

procedure TForm1.Button1Click(Sender: TObject);
begin
 Table1.Filtered := false;
 Table1.Filter := '[FirstName]='+''''+Edit1.text+''''; {use '''' to add ' if you filter on text}
 Table1.Filtered := True;
 showmessage('filer')
end;

procedure TForm1.Button2Click(Sender: TObject);
begin
 Table1.FindNext;
end;

end.

Regards, ZiF.


0
 

Author Comment

by:pedro1
ID: 1340581
Im not using a dbgrid. I wanted it to look more like a form.

The following code is what I have for the 'next' button including what you have said.

procedure TForm3.BitBtn1Click(Sender: TObject);
Var
GetTheNext: tBookmark;
Begin
With Table1 do
Begin
DisableControls; {Disable the controls}
GetTheNext := GetBookmark; {Make a bookmark where you are}
Try
First;
While Not EOF Do {As long as its not the end Kepp Going}
Begin
Next;
End;
Finally
SearchField  := ComboBox1.Text; {Combo1.text has the field info}
SearchString := Edit1.text; {Edit1.text has the item to look for}
GoToBookmark(GetTheNext); {Goto the bookmark}
{The following is your Code inserted and changed to work in my code}
Table1.Filtered := false;
 Table1.Filter := '[searchfield]='+''''+searchstring+''''; {use '''' to add ' if you filter on text}
 Table1.Filtered := True;
 showmessage('filter')
//{Table1.}Filter := SearchField;
//{Table1.}Filter := SearchString;
// showmessage(Table1.Filter);
 //Table1.Filtered := True;
 end;
table1.findnext;
EnableControls;
FreeBookmark(GetTheNext);
end;
end;


What I get now is Cant find field 'Searchfield'. If I remove the [] and/or the '' from around it I get the message Field not of type boolean.
That has totally thrown me. I dont want to filter a yes/no 0/1 or other such field???
Please help.
Cheers Mike Ross
0
 
LVL 8

Expert Comment

by:ZifNab
ID: 1340582
Hi Mike,

1. No problem with dbgrid, just give it for the ease. You can use any data-aware component. You only have to be sure that you use the datasource of the filtered table.

2.

  I suppose  :

  you enter the name of the field in the combobox?
  you enter the text to find in the edit?
 PS.  I advise that you make a listbox that displays only these fields of the table.

Do this :

 When pushing search! :


    procedure TForm1.Button1Click(Sender: TObject);
    begin
     Table1.Filtered := false;
     Table1.Filter := Combobox1.text +'='+''''+Edit1.text+'''';
     Table1.Filtered := True;
   end;


LOOK OUT : This only works for text fields!!!

For boolean :

{I guess}
 Filter on true : Filter := Combobox1.text;
 Filter on false : Filter := NOT Combobox1.text

For Values

  Filter := Combobox1.text + '<' + Edit1.Text; (for smaller then)

When pushing Next button :

  procedure .................
  begin
   table1.FindNext;
  end;

Regards, Zif.
0
 

Author Comment

by:pedro1
ID: 1340583
Almost there.

With the following code :-

procedure TForm3.BitBtn1Click(Sender: TObject);
Var
GetTheNext: tBookmark;
Begin
With Table1 do
Begin
DisableControls;
GetTheNext := GetBookmark;
Try
First;
While Not EOF Do
Begin
Next;
End;
Finally
SearchField  := ComboBox1.Text;
SearchString := Edit1.text;
GoToBookmark(GetTheNext);
Table1.Filtered := false;
Table1.Filter := Combobox1.text +'='+''''+Edit1.text+'''';
showmessage(Table1.Filter);
Table1.Filtered := True;
table1.next;
EnableControls;
FreeBookmark(GetTheNext);
end;
end;
end;

Now What happens is a message box comes up saying finder='mike'
I press ok and the fields go blank and although I am asking for the controls to be enabled they disable themselves. So Im left with a blank form with no way to move forward to the next 'Mike'.

i thought that this code would filter the table field finder with only those that begin 'Mike' and move to the next occurance. Am i missing something here.
Cheers Mike Ross
0
 
LVL 8

Expert Comment

by:ZifNab
ID: 1340584
Hi pedro1,

Still, I'm wondering why you use bookmark....,

Because you want to do :

 1. Let the user define a name
 2. Let the user push on a search button
       ---> the table is being filtered (3 lines)
 3. Let the user push on a next button
       ---> the next record (specified with filter) is searched (1 line)

No bookmark needed! Or am I missing something ;-)

OK, now to the problem :

 does the message box says : finder='mike'  or ='mike' ?

AND Is Finder a field name in your table?
AND Can you give me the fieldnames of your table?

SECONDLY Does there exists a MIKE in the table? If not, then it's normal you got blank.

Regards, Zif.

Wainting to you to respond...


   

0
 

Author Comment

by:pedro1
ID: 1340585
I have just thought what the problem is.

This filter is only filtering the field finder and looking for only mike in the field. Whereas I am looking for 'Mike Ross' or 'Mike Anything' can I filter the field for a partial search eg I enter 'Mike' in the edit1.text box and it will filter all
'Mike' plus anything else mentioned after 'Mike'. Case insensitive would be handy too.
Cheers Mike Ross.
0
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.

 
LVL 8

Expert Comment

by:ZifNab
ID: 1340586
Hi pedro1,

Hehe, the things you want can easely be achieved!

Just fill in the filteroptions!

Look at property filteroptions at table1 :

foCaseInsensitive      The filter is processed without regard to case in the dataset's data.
foNoPartialCompare      String matches must be exact over the length of the data in the dataset; partial matches aren't allowed.

Regards, ZiF.
0
 

Author Comment

by:pedro1
ID: 1340587
Got it working at last Excellent.
Only problem now is the filter stays on when I want to navigate the table. I have tried setting 'table1.filtered := false' when I click on the navigator but this just stops it working. Is there a way to turn the filtering of when I try to use the dbnavigator.
Cheers Mike Ross
0
 
LVL 8

Expert Comment

by:ZifNab
ID: 1340588
? Filtered := false should stop filtering the table....

Can you paste your code... ? thanks..
0
 

Author Comment

by:pedro1
ID: 1340589
Is there a way to use the * wildcard at the beginning of lets say 'Mike' In other words *ik* will filter all items that have these two letters within them. I can get it to find 'm*' this filters all items beginning with 'M' but in the field 'Name' The names may all start with 'Mr' I really need to be able to filter based on any letters within the item.

I will paste the code to you next time.

Cheers mike Ross.
0
 
LVL 8

Expert Comment

by:ZifNab
ID: 1340590
Hi pedro1,

I think..... you could better use the search capabilities of RxLib.... Sorry that I didn't come up earlier with it.... It's a good library and it's free, the manual is in Russian....
But I think you are better off with this (more capabilities) than working yourself days on it...

http://rx.demo.ru/
Regards, Zif.
0
 

Author Comment

by:pedro1
ID: 1340591
I have the rxlibs But dont know which part you are reffering to.
Anyway here is the points.
0
 
LVL 8

Expert Comment

by:ZifNab
ID: 1340592
Pedro1, look at there demo's.
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
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…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

706 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

18 Experts available now in Live!

Get 1:1 Help Now