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!
Yes, this is a funny one.
The problem comes when including the operators and the filter values all in one go. NAV then sees the whole thing as a literal string and doesn’t return a record.
However, if you know the structure of the filter you want to apply beforehand then it is a lot easier to include the operators within the SETFILTER command itself rather than assigning them to text variables. Then, you only have to substitute the values, thus avoiding the issue:
SETFILTER(“No.”,’%1..%2&=%3′,MinValue,MaxValue,ActValue);
Yes, that’s true, but only if you are always setting the same filter, such as in simple examples both you and me provided. But sometimes it won’t just be a single range, it might be three separate ranges.
If you e.g. have a setup table, where you have a field where you can set a filter which is to be applied to a table, and then try to apply this filter programmaticallycombined with another operator and another argument to the filter, then you can not be 100% sure it would work, unless you user STRSUBSTNO.
Gaspode’s example is great at showing this scenario.