Recent

Author Topic: (solved) TDBf and compound filters?  (Read 5425 times)

bill0287

  • Full Member
  • ***
  • Posts: 146
(solved) TDBf and compound filters?
« on: December 17, 2013, 12:47:11 am »
I am trying to filter a DBf by two fields. I checked the documentation and the wikis and couldn't find anything that said it could not be done (or could be done), but I have tried to find an expression that would work for this and have failed. 

I have something like this right now which works for one lookup field:

Code: [Select]
      with DBf do begin
        Filter := 'NAME=' + QuotedStr(DBLookupcomboBox1.Text);
        // I need something like this to be added to the Filter expression: 'TYPE=' + QuotedStr(TypeComboBox.Text);
        Filtered := True;
        end

I need the filter statement to handle two fields (if possible). Nothing I have tried works.

Does anyone know a way to structure a dbf filter statements such that you can use two comboboxes for the filter record values. ?
« Last Edit: December 17, 2013, 05:04:33 am by bill0287 »

kpeters58

  • Sr. Member
  • ****
  • Posts: 267
Re: TDBf and compound filters?
« Reply #1 on: December 17, 2013, 01:49:22 am »
Works for me - though I 'd have to say that I find the filter expression parser a bit 'ticklish'...
in that its error messages can be quite misleading.

Code: [Select]
  dbf1.filtered := False;
  dbf1.Filter := 'TRACK_ID<"8" and NTAXYEAR=2001';
  dbf1.filtered := True; 


The above works on a sample DBF I just downloaded from somewhere (TRACK_ID is of ftString and NTAXYEAR of ftSmallInt type).
Lazarus 2.0.4/FPC 3.0.4/Win 64

bill0287

  • Full Member
  • ***
  • Posts: 146
Re: TDBf and compound filters?
« Reply #2 on: December 17, 2013, 03:04:32 am »
I was hoping the AND would work, but I can't figure out how to write this expression so it passes the parser:

Code: [Select]
with DBf do begin
        Filter := 'NAME=' + QuotedStr(DBLookupcomboBox1.Text) and 'TYPE=' + QuotedStr(TypeComboBox.Text);
        Filtered := True;
        end

As written, I get this error on the AND:

show_report.pas(91,66) Error: Operation "and" not supported for types "AnsiString" and "Constant String"

engkin

  • Hero Member
  • *****
  • Posts: 2513
Re: TDBf and compound filters?
« Reply #3 on: December 17, 2013, 03:52:57 am »
You made a small mistake. and should be inside the string. Otherwise the compiler expects Boolean values. Try:

Code: [Select]
  Filter := 'NAME=' + QuotedStr(DBLookupcomboBox1.Text) 'and TYPE=' + QuotedStr(TypeComboBox.Text);

It might be easier to use Format:

Code: [Select]
  Filter := Format('NAME="%s" and TYPE="%s"',[DBLookupcomboBox1.Text, TypeComboBox.Text]);

bill0287

  • Full Member
  • ***
  • Posts: 146
Re: TDBf and compound filters?
« Reply #4 on: December 17, 2013, 04:31:56 am »
engkin... thank you!!

The second one worked just fine, but the first option didn't compile. I got this error on the change:

Fatal: Syntax error, ";" expected but "const string" found

engkin

  • Hero Member
  • *****
  • Posts: 2513
Re: TDBf and compound filters?
« Reply #5 on: December 17, 2013, 04:44:45 am »
but the first option didn't compile.

I forgot to add + to connect the two strings.

Code: [Select]
  Filter := 'NAME=' + QuotedStr(DBLookupcomboBox1.Text) + 'and TYPE=' + QuotedStr(TypeComboBox.Text);

Sorry!  :-[

bill0287

  • Full Member
  • ***
  • Posts: 146
Re: TDBf and compound filters?
« Reply #6 on: December 17, 2013, 05:04:15 am »
That was it. Thanks again.

BigChimp

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Re: (solved) TDBf and compound filters?
« Reply #7 on: December 17, 2013, 10:04:51 am »
@bill: are you sure the example you added to the wiki actually works? I would think
Code: [Select]
Filter := 'NAME=' + QuotedStr(DBLookupcomboBox1.Text) + 'and TYPE=' + QuotedStr(TypeComboBox.Text);
misses a space before and:
Code: [Select]
Filter := 'NAME=' + QuotedStr(DBLookupcomboBox1.Text) + ' and TYPE=' + QuotedStr(TypeComboBox.Text);
Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

Open source including papertiger OCR/PDF scanning:
https://bitbucket.org/reiniero

Lazarus trunk+FPC trunk x86, Windows x64 unless otherwise specified

engkin

  • Hero Member
  • *****
  • Posts: 2513
Re: (solved) TDBf and compound filters?
« Reply #8 on: December 17, 2013, 02:27:05 pm »
Thank you BigChimp for noticing. (*Double embarrassed*)

bill0287

  • Full Member
  • ***
  • Posts: 146
Re: (solved) TDBf and compound filters?
« Reply #9 on: December 17, 2013, 05:28:01 pm »
FWIW, it appeared to work on my simple dataset - but I could have been tired. I tried your update and it looks fine, of course.

I figured since I am not skilled enough to contribute to the Lazarus code project, I could at least kick the tires a bunch, try to uncover some bugs, and share some working examples in the wiki that might help the next person that comes along and save them some time. You can't underestimate the value of example IMHO.


BigChimp

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Re: (solved) TDBf and compound filters?
« Reply #10 on: December 17, 2013, 05:29:32 pm »
You're completely right. It's much better to have examples in the wiki that may not be perfect.. but that the next guy coming along can improve rather than nothing.

Thanks for your contributions!
Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

Open source including papertiger OCR/PDF scanning:
https://bitbucket.org/reiniero

Lazarus trunk+FPC trunk x86, Windows x64 unless otherwise specified

 

TinyPortal © 2005-2018