Filter ClientDataSet by more than one Field

Question

The following code allows me to filter a clientdataset by one field:

begin
dmodule.cds.DisableControls;
try
  dmodule.cds.Filtered := False;

  dmodule.cds.FilterOptions := [foCaseInsensitive,foNoPartialCompare];
  dmodule.cds.Filter := 'Field LIKE '+ QuotedStr('%'+ editSearch.Text + '%');

  dmodule.cds.Filtered := True;
finally
  dmodule.cds.EnableControls;
end;

I have added in another line after the first 'cds.Filter' line which I thought would allow me to filter by another field but it doesn't bring back any records.

The line is:

dmodule.cds.Filter := 'Field2 LIKE '+ QuotedStr('%'+ editSearch.Text + '%');

Do I need to do something else to allow multiple filtering?

Thanks,

score 3 · Accepted Answer · answered Jul 22 '15 at 12:55

You just combine them with and (you can also use or if need be):

begin
  dmodule.cds.DisableControls;
  try
    dmodule.cds.Filtered := False;

    dmodule.cds.FilterOptions := [foCaseInsensitive,foNoPartialCompare];
    dmodule.cds.Filter := 'Field LIKE '+ QuotedStr('%'+ editSearch.Text + '%') +
                          'AND Field2 LIKE ' + QuotedStr(editSearch2.Text) + '%');


    dmodule.cds.Filtered := True;
  finally
    dmodule.cds.EnableControls;
  end;

score 3 · Answer 2 · answered Jul 22 '15 at 12:58

Your second line basically replaces the filter in the first line. You probably want:

dmodule.cds.Filter := 
         'Field LIKE '+ QuotedStr('%'+ editSearch.Text + '%') + 
     ' OR Field2 LIKE '+ QuotedStr('%'+ editSearch.Text + '%');

Note: I've assumed you want OR because you were surprised about not returning any data. Of course if you require both conditions to be True to return data, then use AND; but you'll still get no records.

Filter ClientDataSet by more than one Field

2 Answers2