How do I filter A table?

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.
pedro1Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ZifNabCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pedro1Author Commented:
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
ZifNabCommented:
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
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

pedro1Author Commented:
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
ZifNabCommented:
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
pedro1Author Commented:
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
ZifNabCommented:
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
pedro1Author Commented:
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
ZifNabCommented:
? Filtered := false should stop filtering the table....

Can you paste your code... ? thanks..
0
pedro1Author Commented:
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
ZifNabCommented:
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
pedro1Author Commented:
I have the rxlibs But dont know which part you are reffering to.
Anyway here is the points.
0
ZifNabCommented:
Pedro1, look at there demo's.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Delphi

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.