Creating Table to Hold Filters

Saving a Filter to the Database

1. Verify that the ftable is filtered

Check for wwFilterDlg..FieldInfo.Count >0

The FIELDINFO property is a TList of type TwwFieldInfo that contains a list of the Filters that are active in a TwwFilterDialog Component.

2. Ensure that the table holding the filters is open

3. Store the Filter FieldInfo in a StringList

4. Store the StringList into a Memo field

5. Insert a new record into the Filter Table

6. Set the Table Blob Field FILTTEXT to the contents of the Memo field

7. Allow the user to Name the filter and set other info

The SaveFilter Procedure

procedure TfrmPeople.SaveFilter1Click(Sender: TObject); var DoOverwrite: boolean; TempStringList: TStrings; curFieldInfo: TwwFieldInfo; NewCaseSensitive: string; NewNonMatching: string; i: integer; begin //if tbl not filtered then exit if dmPeople.filtPeople.FieldInfo.Count = 0 then begin beep; messageDlg('The Table is not filtered!', mtError,[mbOK],0); exit; end; //Ensure table holding filters is active and current if (dmPeople.tblFiltMem.active) then dmPeople.tblFiltMem.refresh else dmPeople.tblFiltMem.active := true; //Load Filter Criteria into Temp StringList TempStringList := TStringList.create; // 1st line representes name of the filter --w dont care though TempStringList.Add('FilterName'); for i:= 0 to dmPeople.filtPeople.FieldInfo.Count-1 do begin curFieldInfo := TwwFieldInfo(dmPeople.filtPeople.FieldInfo.Items[i]); //Store Boolean vlues as Strings if (curFieldInfo.CaseSensitive) then NewCaseSensitive := 'True' else NewCaseSensitive := 'False'; if (curFieldInfo.NonMatching) then NewNonMatching := 'True' else NewNonMatching := 'False'; TempStringList.Add(curFieldInfo.FieldName + #9 + curFieldInfo.DisplayLabel + #9 + IntToStr(integer(curFieldInfo.MatchType)) + #9 + curFieldInfo.FilterValue + #9 + curFieldInfo.MinValue + #9 + curFieldInfo.MaxValue + #9 + NewCaseSensitive + #9 + NewNonMatching); end; //add filter to the table try dmPeople.tblfiltMem.insert; TempMemo.text := TempStringList.text;; dmPeople.tblfiltMem.fieldbyname('FiltText').asString := TempMemo.text; except messageDlg('Error storing Filter!',mterror,[mbOK],0); TempStringList.Free; if dmPeople.tblfiltMem.state in [dsedit, dsinsert] then dmPeople.tblfiltMem.cancel; exit; end; TempStringList.Free; //allow user to name filter rvFiltMemPeople.execute; //TwwRecordView Control end;

Loading a Filter From the Table

Allow use to select desired filter
Assign Filter Info from Table record to temporary TStringList
Clear the current filter
Load the current filter with selected data
Make the filter active

The LoadFilter Procedure

procedure TfrmPeople.LoadFilter1Click(Sender: TObject);
var myFieldInfoStrings, TempStringList: TStrings;
    lineNum: integer;
    curFieldInfo: TwwFieldInfo;

begin
   //Ensure filter Tbl is active
   if dmPeople.tblFiltMem.active then
     dmPeople.tblFiltMem.refresh
   else dmPeople.tblFiltMem.active := true;

// Allow user to select desired filter
   frmLoadMemFilt.showmodal;
   if frmLoadMemFilt.ModalResult <> MrOK then exit;

//You could save current Filter Ingo in case of error
   //See commented code below
   {CurrFilt.readonly := false;
   CurrFilt.text := dmPeople.tblFiltMemFILTNAME.AsString;
   CurrFilt.readonly := true; }

//Assign Filter Info from Table to temp TStringList
  TempStringList := TStringList.create;
  TempStringList.text :=
     dmPeople.tblFiltMemFILTTEXT.AsString;

// Cleatr the current filter
   dmPeople.filtPeople.ClearFilter;

//Load current filter with selected data
   lineNum:=0;
   myFieldInfoStrings := TStringList.create;
   while (lineNum+1<TempStringList.Count) do begin
           inc(lineNum);  //actually starting with 2nd line
           if (TempStringList.Strings[lineNum]= '//') then break;

curFieldInfo := TwwFieldInfo.create;
           strBreakApart(TempStringList.Strings[lineNum], #9, myFieldInfoStrings);
           curFieldInfo.FieldName := myFieldInfoStrings.Strings[0];
           curFieldInfo.DisplayLabel := myFieldInfoStrings.Strings[1];
           curFieldInfo.MatchType := TwwFilterMatchType(StrToInt(myFieldInfoStrings.Strings[2]));
           curFieldInfo.FilterValue := myFieldInfoStrings.Strings[3];
           curFieldInfo.MinValue := myFieldInfoStrings.Strings[4];
           curFieldInfo.MaxValue := myFieldInfoStrings.Strings[5];

if (myFieldInfoStrings.Strings[6] = 'True') then
              curFieldInfo.CaseSensitive := True
           else curFieldInfo.CaseSensitive := False;

if (myFieldInfoStrings.Count-1 > 6) and
              (myFieldInfoStrings.Strings[7] = 'True') then
              curFieldInfo.NonMatching := True
           else curFieldInfo.NonMatching := False;

dmPeople.filtPeople.FieldInfo.Add(curFieldInfo);
   end;

// Clean up
   myFieldInfoStrings.Free;
   dmPeople.tblPeople.filtered := false;

//Make the new filter active
   try
     dmPeople.filtPeople.ApplyFilter;
   except
       messageDlg('Error applying filter!  Filter may be '+
         'corrupted, if failure occurs again, please delete '+
         'and re-create this filter.',mtError,[mbOK],0);
       dmPeople.filtPeople.ClearFilter;
   end;

DisplayRecCount;

end;

Clearing a Filter

The ClearFilter Procedure

procedure TfrmPeople.ClearFilterClick(Sender: TObject); begin dmPeople.filtPeople.ClearFilter; dmPeople.filtPeople.ApplyFilter; DisplayRecCount; CurrFilt.Text := ''; end;

Notes:

Filter Dialog MUST be in same Data Module as DataSource Component

FilterMethod = dbQueryMode gave me the best performance. Remote filtering performed by modifing the query where clause.