Recent

Author Topic: filtering grid using editbox while using more than one keyword  (Read 290 times)

iamtheatorres2408

  • New member
  • *
  • Posts: 8
filtering grid using editbox while using more than one keyword
« on: December 06, 2018, 03:59:54 am »
Good Day, im using this code to filter my searchbox, what other codes can i use to set more than one keyword in the searchbox? thanks guys :) :) :) :)





Code: Pascal  [Select]
  1.  Accept := Pos(UpperCase(edtSearch.Text), UpperCase(DataSet.FieldByName('CommittedBy').AsString)) > 0;

HeavyUser

  • Full Member
  • ***
  • Posts: 206
Re: filtering grid using editbox while using more than one keyword
« Reply #1 on: December 06, 2018, 05:15:16 am »
Good Day, im using this code to filter my searchbox, what other codes can i use to set more than one keyword in the searchbox? thanks guys :) :) :) :)





Code: Pascal  [Select]
  1.  Accept := Pos(UpperCase(edtSearch.Text), UpperCase(DataSet.FieldByName('CommittedBy').AsString)) > 0;
sorry I'm lost are asking how to create a a parser and a small interpreter from string expression to boolean checks or you are looking to learn about the dataset.filter and filtered properties? Are you going to have a single editor for your filter or are you ok with having multiple editors one for each word? How about splitting the string typed in an array of words using the space as a delimeter and checking all the words one by one until you either find one or all return false?

Please provide us with a specific use case so we can focus on your design.

GetMem

  • Hero Member
  • *****
  • Posts: 3255
Re: filtering grid using editbox while using more than one keyword
« Reply #2 on: December 06, 2018, 07:00:21 am »
If you wish to filter by multiple keywords, just add more searchboxes:
Code: Pascal  [Select]
  1. procedure TForm1.SQLQuery1FilterRecord(DataSet: TDataSet; var Accept: Boolean);
  2. begin
  3.   Accept := (Pos(UpperCase(edCommitedBy.Text), UpperCase(DataSet.FieldByName('COMMITTEDBY').AsString)) > 0) and
  4.             (Pos(UpperCase(edCategory.Text), UpperCase(DataSet.FieldByName('CATEGORY').AsString)) > 0);
  5. end;
  6.  

Of course in this case you must force the user to enter the text both for edCommitBy and edCategory. Adding more filtering options is also possible:
Code: Pascal  [Select]
  1. procedure TForm1.SQLQuery1FilterRecord(DataSet: TDataSet; var Accept: Boolean);
  2. begin
  3.   if UserFilterOption1 then //filter only by category for example
  4.     Accept := Filter1
  5.   else if UserFileterOption2 then //filter both for category and commitby
  6.     Accept := Filter2
  7.  //...
  8. end;
I would put a checkbox in front of the searchboxes. When the checkbox is checked the searchbox becomes enabled otherwise is disabled. By knowing which checkbox is checked you can set up the filter(s).
 

For more then 3-4 filters though, you better use the sql where clause and pass the values as params, like this:
Code: MySQL  [Select]
  1. select * from tablename
  2. where (Field1 = :pField1 or :pField1 = -1) and/*-1 means don't filter by Filter1, this depends of the Field type, obviously you cannot use -1 for varchars*/  
  3.       (Field2 = :pField2 or :pField1 = 'buhaha')
  4.  
         
Then from Lazarus:
Code: Pascal  [Select]
  1.   SQLQuery1.Params.BeginUpdate;
  2.   SQLQuery1.ParamByName('pFIELD1').AsInteger = -1;
  3.   SQLQuery1.ParamByName('pFIELD2').AsString = 'something';
  4.   SQLQuery1.Params.ParamValues['pField3'] := null; //passing null if needed
  5.   SQLQuery1.Params.EndUpdate;
  6.  
« Last Edit: December 06, 2018, 07:41:41 am by GetMem »

iamtheatorres2408

  • New member
  • *
  • Posts: 8
Re: filtering grid using editbox while using more than one keyword
« Reply #3 on: December 12, 2018, 03:13:01 am »
i tried using the first code, but it doesn't work..when i try to search the keyword in the editbox, the dbgrid becomes empty, what do you think is the problem.?? thanks

 :D :D


If you wish to filter by multiple keywords, just add more searchboxes:
Code: Pascal  [Select]
  1. procedure TForm1.SQLQuery1FilterRecord(DataSet: TDataSet; var Accept: Boolean);
  2. begin
  3.   Accept := (Pos(UpperCase(edCommitedBy.Text), UpperCase(DataSet.FieldByName('COMMITTEDBY').AsString)) > 0) and
  4.             (Pos(UpperCase(edCategory.Text), UpperCase(DataSet.FieldByName('CATEGORY').AsString)) > 0);
  5. end;
  6.  

Of course in this case you must force the user to enter the text both for edCommitBy and edCategory. Adding more filtering options is also possible:
Code: Pascal  [Select]
  1. procedure TForm1.SQLQuery1FilterRecord(DataSet: TDataSet; var Accept: Boolean);
  2. begin
  3.   if UserFilterOption1 then //filter only by category for example
  4.     Accept := Filter1
  5.   else if UserFileterOption2 then //filter both for category and commitby
  6.     Accept := Filter2
  7.  //...
  8. end;
I would put a checkbox in front of the searchboxes. When the checkbox is checked the searchbox becomes enabled otherwise is disabled. By knowing which checkbox is checked you can set up the filter(s).
 

For more then 3-4 filters though, you better use the sql where clause and pass the values as params, like this:
Code: MySQL  [Select]
  1. select * from tablename
  2. where (Field1 = :pField1 or :pField1 = -1) and/*-1 means don't filter by Filter1, this depends of the Field type, obviously you cannot use -1 for varchars*/  
  3.       (Field2 = :pField2 or :pField1 = 'buhaha')
  4.  
         
Then from Lazarus:
Code: Pascal  [Select]
  1.   SQLQuery1.Params.BeginUpdate;
  2.   SQLQuery1.ParamByName('pFIELD1').AsInteger = -1;
  3.   SQLQuery1.ParamByName('pFIELD2').AsString = 'something';
  4.   SQLQuery1.Params.ParamValues['pField3'] := null; //passing null if needed
  5.   SQLQuery1.Params.EndUpdate;
  6.  

GetMem

  • Hero Member
  • *****
  • Posts: 3255
Re: filtering grid using editbox while using more than one keyword
« Reply #4 on: December 12, 2018, 07:15:51 am »
what do you think is the problem.??
The problem is that you don't listen. As I said in my previous post you have to force the user to enter the text both for edCommitBy and edCategory, otherwise it won't work. Another solution is to ingore empty searchboxes, like this:
Code: Pascal  [Select]
  1. procedure TForm1.SQLQuery1FilterRecord(DataSet: TDataSet; var Accept: Boolean);
  2. begin
  3.   Accept := (
  4.                (Pos(UpperCase(edCommitedBy.Text), UpperCase(DataSet.FieldByName('COMMITTEDBY').AsString)) > 0) or
  5.                (Trim(edCommitedBy.Text) = '')
  6.             )
  7.             and
  8.             (
  9.               (Pos(UpperCase(edCategory.Text.Text), UpperCase(DataSet.FieldByName('CATEGORY').AsString)) > 0 ) or
  10.               (Trim(edCategory.Text) = '')
  11.             );
  12. end;

Link both searchbox to the same Keypress event:
Code: Pascal  [Select]
  1. procedure TForm1.edCommitedByKeyPress(Sender: TObject; var Key: char);
  2. begin
  3.   if Key = #13 then
  4.   begin
  5.      SQLQuery1.Filtered := False;
  6.      SQLQuery1.Filtered := True;
  7.   end;
  8. end;

Now enter something in the first searchbox(you can leave the second one empty) then press enter. You also need a reset button where you set filtered property to false and empty the serachboxes.

PS: If still not works then attach a sample project because it works fine here.
« Last Edit: December 12, 2018, 07:33:00 am by GetMem »