(solved) TDBf and compound filters?

[bill0287]

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. ?

[kpeters58]

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).

[bill0287]

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]

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]

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]

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]

That was it. Thanks again.

[BigChimp]

@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);


[engkin]

Thank you BigChimp for noticing. (*Double embarrassed*)

[bill0287]

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]

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!