(solved) TDBf and compound filters?
lazarus / 16-Databases

----------------------------------------------------------------------
bill0287 · 2013-12-16 23:47

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 · 2013-12-17 00:49

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 · 2013-12-17 02:04

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 · 2013-12-17 02:52

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 · 2013-12-17 03:31

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 · 2013-12-17 03:44

Quote from: bill0287 on December 17, 2013, 04:31:56 ambut 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 · 2013-12-17 04:04

That was it. Thanks again.

----------------------------------------------------------------------
BigChimp · 2013-12-17 09:04

@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 · 2013-12-17 13:27

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

----------------------------------------------------------------------
bill0287 · 2013-12-17 16:28

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 · 2013-12-17 16:29

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!