Why doesn’t my filter work?

I was going through some code for an upgrade and I came across some code that I thought contained an error. The code was looking at some setup fields that contained a range expression (in the format 1000..1999) and was trying to determine if a given G/L Account No. was within the range.

The code had a comment that said “cannot use SETRANGE as this does not work!” The code then went on to find the position of the two dots in the filter string “..” and then take the minimum and maximum parts of the range and use a set range function in order to determine if the record was within the range. Sadly it was also fetching each record that matched the range and comparing that to the current G/L Account No. to see whether the number matched.

“Aha!”, I thought. “Aha! – They need to use a SETFILTER and not a SETRANGE – the fools!”

So in order to prove my point (to no one in particular), I created a quick test form on which I tried to determine if a G/L Account No. was within some range fields I had created. I intended to use the SETFILTER function on a new record variable for the G/L Account table. My plan was to use this with the FINDFIRST command to see if I could find any records that matched the filter and were equal to my current G/L Account.

I got a real shock when my filter did not work.

My filter looked something like this:

SETFILTER(“No.”, ‘%1&=%2’, “Rev. Acct Range”, “No.”);

It is using the string substitution capabilities of the SETFILTER command together with my revenue account filter variable and my current account number field. If my “Rev. Acct Range” variable is set to 1000..1999 and my current G/L Account No. field is 1222 then I would expect my filter to be 1000..1999&=1222 which means “is between 1000 and 1999 and is also equal to 1222”. This, I hope you agree, should be true and I can certainly find a record that matches this filter, therefore I know that my record is within range.

So why doesn’t this work?

In order to find out, I needed to enable the debugger and look at the filter that was actually being set on my table record. When I debugged it I saw that the string substitution was putting single quotes around my range so my filter looked like this:

‘1000..1999’&=1222

This filter string means “is equal to the string ‘1000..1999’ and is equal to 1222.” This can never be true.

I couldn’t believe it! The person that had written the original code was right – it doesn’t work. But then I had a cunning idea. I could build a string containing my combined filters and use that instead of the %1 and %2 substitution parameters.

The following code is so simple but works and is much more efficient than the original code. It is also more robust since any valid filter can be used in my setup fields and not just one that contains two dots.

GLAcc.RESET;
TempFilterString := "Rev. Acct Range" + '&=' + "No.";

GLAcc.RESET;
GLAcc.SETFILTER("No.", TempFilterString);
IF GLAcc.FINDFIRST THEN
  "Revenue Acct." := TRUE
ELSE
  "Revenue Acct." := FALSE;