Apply multiple filters from multiple comboboxes

Question

I have an adotable and i want to apply multiple filter from multiple coboboxes, each combobox applies a filter to that table. My problem is whenever i choose an item from combobox1 for example it applies its filter but when i choose another item from the next combobox which is combobox2 it deletes the previous filter(combobox1) and applies the filter from combobox2. My question is: how can i make the adotable keep the previous filters from combobox1 or combobox2 or even both but also adds the 3rd filter from combobox3 for more in-depth sorting? Thanks in advance. This is what i got to so far:

procedure TForm1.ComboBox1Change(Sender: TObject);
begin
ADOTable1.Filtered:=false;
ADOTable1.filter:='`enter code here`field1='+ QuotedStr(ComboBox1.Text);
ADOTable1.Filtered:=true;
end;

procedure TForm1.ComboBox2Change(Sender: TObject);
begin
ADOTable1.Filtered:=false;
ADOTable1.filter:=adotable1.Filter+'and field2='+ QuotedStr(ComboBox1.Text);
ADOTable1.Filtered:=true;
end;

procedure TForm1.ComboBox3Change(Sender: TObject);
begin
ADOTable1.Filtered:=false;
ADOTable1.filter:=adotable1.Filter+'and field3='+ QuotedStr(ComboBox1.Text);
ADOTable1.Filtered:=true;
end;

Answer 1

above code is error when you changed many times,so you need to re-combin the filter text every times

procedure TForm1.ComboBox1Change(Sender: TObject);
begin
    ComboBoxChange();
end;

procedure TForm1.ComboBox2Change(Sender: TObject);
begin
    ComboBoxChange();
end;

procedure TForm1.ComboBox3Change(Sender: TObject);
begin
    ComboBoxChange();
end;


procedure TForm1.ComboBoxChange();
var
    count: integer;
    sFilter: string;
begin
    count := 0;
    sFilter := '`enter code here`';
    
    if(ComboBox1.Text <> '') then
    begin
        sFilter := 'field1=' + ComboBox1.Text;
        Inc(count);
    end;
    if(ComboBox2.Text <> '')then
    begin
        if count > 0 then
            sFilter := sFilter + 'and field2=' + ComboBox2.Text
        else
            sFilter := sFilter + ' field2=' + ComboBox2.Text
        Inc(count);
    end;
    if(ComboBox3.Text <> '')then
    begin
        if count > 0 then
            sFilter := sFilter + 'and field3=' + ComboBox3.Text
        else
            sFilter := sFilter + ' field3=' + ComboBox3.Text
        Inc(count);
    end;
    ADOTable1.Filtered:=false;
        ADOTable1.filter:=sFilter;
    ADOTable1.Filtered:=true;
end;

Answer 2

Disclaimer: Solution in this answer requires support of Generics so it won't work in old versions of Delphi (and you shouldn't).

1. You can apply Tag property to store index of field which you'll use to get field name in filter generation.

2. To store value of each filter you can use private TDictionary field of your Form.

   uses 
     { here all other modules }, System.Generics.Collections;
   
   type
     TForm1 = class(TForm)
       { here all components declaration }
     strict private
       Filters: TDictionary<Integer, string>;
   

   We should initialize Filters on the App start. You can do it in any place where you perform your initial setup (OnCreate, OnShow or any other event of Form; initialization section; etc.). I'll use OnCreate event handler just as example. You can generate empty handler and attach it to Form automatically in Event tab of Object Inspector by double clicking on empty cell near OnCreate:

   So now we should initialize Filters in this event handler:

   procedure TForm1.FormCreate(Sender: TObject);
   begin
     { here all your previous setup (if exist) }
     Filters := TDictionary<Integer, string>.Create;
   end;
   

   Also there you should initialize default values of your filters if you need them. It's necessary to do this if you set default values of Text in Object Inspector and want them to be used in filter query, because such as values won't be saved automatically into our Filters as none of OnChange events triggered.

3. Now let's define universal OnChange event handler for TComboBox which will store value into Filters:

   Firstly we should add signature of our event handler to public section of Form class. Since public is the default access modifier in classes, you can add your function right under component listing or another handlers (if exist):

   type
     TForm1 = class(TForm)
       { here all components declaration }
       procedure ComboBoxChange(Sender: TObject);
   

   Then you can just point cursor on this declaration and press ShiftCtrlC which should let IDE generate empty function like this:

   procedure TForm1.ComboBoxChange(Sender: TObject);
   begin
   
   end;
   

   We've already defined unique Tag value for each ComboBox, so we could use it as a key. Event handler is pretty simple:

   procedure TForm1.ComboBoxChange(Sender: TObject);
   begin
     with Sender as TComboBox do
       Filters.AddOrSetValue(Tag, Text);
   end;
   

   Finally you should add this event handler to each ComboBox. Copy name of function and paste it into an empty cell near OnChange in Events tab of Object Inspector, repeat this for each ComboBox you want to use for filtering:

4. Final step is to write function which will generate filter query and update our event handler to work with ADOTable. Let's define our function in private section of Form class:

   type
   TForm1 = class(TForm)
     { here all components declaration }
   strict private
     Filters: TDictionary<Integer, string>;
     function GetFilterQuery(SkipEmptyValues: Boolean = False): string;
   

   Press ShiftCtrlC to generate function. In function we will iterate over Filters using for ... in loop, use string.Format to create filter string for each element of Filters and string.Join to join all created strings (I've used TList<string> as temporary data container):

   function TForm1.GetFilterQuery(SkipEmptyValues: Boolean): string;
   var
     Pair: TPair<Integer, string>;
     Conditions: TList<string>;
   begin
     result := string.Empty;
     Conditions := TList<string>.Create;
   
     for Pair in Filters do
       with Pair do
         if (not SkipEmptyValues) or (not Value.IsEmpty) then
           Conditions.Add(string.Format('field%d=%s', [Key, Value.QuotedString]));
   
     if Conditions.Count > 0 then
       result := string.Join(' and ', Conditions.ToArray);
   
     Conditions.Free;
   end;
   

   At last, we should call this function in our OnChange event handler:

   procedure TForm1.ComboBoxChange(Sender: TObject);
   var
     Query: string;
   begin
     with Sender as TComboBox do
       Filters.AddOrSetValue(Tag, Text);
   
     Query := GetFilterQuery(True);
     if not Query.IsEmpty then
     begin
       ADOTable1.Filtered := False;
       ADOTable1.Filter := Query;
       ADOTable1.Filtered := True;
     end;
   end;