Hi All,

Is it possible to filter a dataset on the client side? I have a staff list and one of the fields is Active which is Boolean.
I'd like to get all staff records from the server, and then allow the user to check or uncheck a check box to say if they want to see only active staff or not.

Cheers,
Paul

Paul,

<<
Is it possible to filter a dataset on the client side?
>>

No, just use a dataset with a param "Active" and reload this dataset using the boolean value of the checkbox.

Uli

Uli Becker wrote:

> No, just use a dataset with a param "Active" and reload this dataset using the boolean value of the checkbox.

Hi Uli,

Thanks mate.

Cheers,
Paul

<<Paul Coshott wrote:

Is it possible to filter a dataset on the client side? I have a staff list and one of the fields is Active which is Boolean.
I'd like to get all staff records from the server, and then allow the user to check or uncheck a check box to say if they want to see only active staff or not.>>

I looked into this once a long time ago. From memory I was trying to subclass the dataset so I could override the JSON loading of the data rows. I wanted to apply a filter expression at this point and ignore unwanted rows. I got stuck with protected/private stuff and gave up. I wanted to avoid another server hit seeing as all the JSON was already there.

This is not exactly answering your question, but I did end up making a useful generic search dialog that doesn't require another round trip to the server. As you already have the full dataset loaded, you can just make a new subset dataset the brute force way (it is fast enough) for the user to select from, then reposition the cursor of the original dataset.

Some snippets of code below (let me know if you want more):

You might be able to do something similar using a parallel 'filtered' dataset that you can switch in and out.

Steve

procedure TfrmSearch.btnSearchClick(Sender: TObject);
var
 i, j : integer;
 //found : boolean;              
 s : string;
begin
 // search all fields in the grid, looking in dsOriginal table
 if edSearch.Text = '' then exit;
 dsFiltered.Empty;
 with dsOriginal do begin
   DisableControls;
   first;
   //found := false;
   s := LowerCase(edSearch.Text);
   while not eof do begin
     for i:=0 to gridResults.ColumnCount-1 do begin
       if pos(s, LowerCase(dsOriginal.Columns[gridResults.Columns[i].DataColumn].asString)) > 0 then begin
         dsFiltered.Insert;
         for j:=0 to gridResults.ColumnCount-1 do
           dsFiltered.Columns[gridResults.Columns[j].DataColumn].asString := dsOriginal.Columns[gridResults.Columns[j].DataColumn].asString;
         dsFiltered.Update;
         //found := true;
         break; // this record qualifies so no need to check remaining columns
       end;
     end;
     //if found then break; removed this so all matching records will result
     next;
   end;
   first;
   EnableControls;
 end;
 dsFiltered.first;
end;

procedure TfrmSearch.btnOKClick(Sender: TObject);
begin
 if dsFiltered.RowID > 0 then begin
   dsOriginal.InitFind;
   if KeyField = '' then // default is field 'Code'
     KeyField := 'Code';
     dsOriginal.Columns[KeyField].asString := dsFiltered.Columns[KeyField].asString;
   async dsOriginal.Find(false, false);
 end;
 Cancelled := false;
 async Close;
end;

procedure TfrmSearch.gridResultsDblClick(Sender: TObject);
begin
 btnOKClick(nil);
end;

procedure TfrmSearch.frmSearchShow(Sender: TObject);
begin
 async dsFiltered.LoadColumns(dsOriginal.GetColumns);  // dsOriginal is a public property set before calling
 async dsFiltered.Open;
end;

function TfrmSearch.edSearchKeyDown(Sender: TObject; Key: Integer; ShiftKey, CtrlKey, AltKey: Boolean): Boolean;
begin
 if key = VK_RETURN then begin
   Result := false;
   btnSearchClick(nil);
 end else
   Result := true;
end;