Why doesn’t my filter work?

Today I got a comment from another soul out there, spending their time blogging about Microsoft Dynamics NAV. I’ve immediately put the link to my blogroll, and it really deserves it, because it is the best blog on the topic I’ve seen so far.

Few days back, on that blog, there was a post about setting filters, which didn’t work as expected, and you had to write a wordy workaround for a thing that should work as expected, but surprisingly it doesn’t.

The problem is with SETFILTER function which works well in two situations: when you set hardcoded filters; and when you set variable filters with a single text variable as a parameter.

For example, this is going to work well:

SETFILTER("No.", '1000..1999&1222');

And this is going to work well as well:

txtFilterValue := '1000..1999&1222');
SETFILTER("No.",txtFilterValue);

However, this little bastard is going to have you want to go on a Friday 13th style chainsaw massacre happy hour:

txtFilter1 := '1000..1999';
txtFilter2 := '1222';
SETFILTER("No.", '%1&=%2', txtFilter1, txtFilter2);

This doesn’t work as expected, because instead of returning 1000..1999&=1222 it returns a don’t-call-me-stupid value of ‘1000..1999’&=1222. Why, oh why?

Well, I don’t have a clue. It’s not documented anywhere, and there is no single (reasonable) reason why there should be any single quotation marks around the first argument value, and not around the second one, or why should there be any at all in any given situation.

And I remember my frustration when I first stumbled upon a similar problem with SETFILTER, which simply doesn’t work as described in documentation, at least not when you use it with more than 1 argument and the value arguments are of the type text or code. Curious by nature, and stubborn by definition, I didn’t want just to give in. So I wanted to check out the STRSUBSTNO function which had almost the same syntax as SETFILTER, and declaratively it should also work the same.

And guess what? The result of the following:

STRSUBSTNO('%1&=%2', txtFilter1, txtFilter2)

is 1000..1999&=1222, just as expected. Scary. Or thankfully not very much so, because at least there is some light at the end of the tunnel.

In the end, this means that whenever you have problems with SETFILTER function, you can always get your way around it just by putting STRSUBSTNO to work:

SETFILTER("No.", STRSUBSTNO('%1&=%2', txtFilter1, txtFilter2));

You wouldn’t believe it, but now, for some reason or other, it works.

Happy filtering!