Set Filter VS SetRange

brijlesh.p

hi,

Sometimes we use Setfilter to filter few data and sometime we use SetRange.
Can you explain the basic difference of SetFilter and SetRange in detail?

thanks

kine

In format of parameters...

SetRange - setting range (or filtering to one specific value if just one parameter entered)
SetFilter - setting any filter you can enter in NAV.

it means that SetRange can do subset of fuinctionality of SetFitler and is easier for use for easy fitlering. SetFilter can do all but the "syntax" is more complex.

rajpatelbca

i this you have to read developer 1 and 2 first.

SETRANGE: The SETRANGE function provides a quick way to set a simple
filter on a field. If you call this function with a field that already has a filter, the
system removes that filter before it sets the new one. A range in C/SIDE is of the
form "FromValue ToValue." This is the only type of filter that SETRANGE can
perform. Once a filter is applied to the record variable, the record set is changed
so that it only includes those records that meet the filter criteria. FIND or NEXT
functions on a filtered record set will only retrieve records in that record set. The
GET function, however, ignores all filters and gets the record from the database
if it can.


SETFILTER: The SETFILTER function provides a way to set a complex filter
on a field. If you call this function with a field that already has a filter, the system
removes that filter before it sets the new one or you can use the RESET function
to remove filters. You can construct filters using the following operators: (), .., &,
|, <, <=, >, <>, *. You can also use replaceable parameters (%1, %2, and so on)
just like the MESSAGE function.

Note that setting a filter with SETFILTER will remove a previous one set by
SETRANGE and vice versa.

bappaditya44

Setrange is used for simple filter . But setfilter is used for complex filters .
You can use &,|,>,<,* etc in setfilter but you can not use them in setrange.

fverkel

The basic difference is that SETRANGE does not interpret the filter.
Wildcards with SETRANGE are treated as normal characters.


Use this filter on the table Country:

SETFILTER(Code, 'A*');

Then you would get all countries starting with A.

Use this filter on the table Country:

SETRANGE(Code, 'A*');

Then you would only get the country with the code A*. Which normally does not exist.


--> When searching with foreign fields you should always use SETRANGE, because SETFILTER could return a different record if the foreign field containts wildcards.
This is one of the reasons why I advise people not to use wildcards in primairy fields.

Suppose you would have a record in tabel Country with the code ?A,
and a customer with Country=?A.
If you would have a report based on the customers, and wanted in the C/AL-code to find the country, and if you would use SETFILTER instead of SETRANGE, then it would fail.

Of course, this doesn't sound like a probable situation with the table Country, but I could imagine using special characters in other tables.

Karenh

When I took the developer training, we were told by the Microsoft instructor that we should always use SETRANGE instead of SETFILTER when possible because SETRANGE is significantly faster.


I don't know how much faster SETRANGE is, but I have followed that advice.

DenSter

I would not say "significantly" faster, because ultimately they will cause the same filtering statement, the code internally just has to make two translation steps instead of one.

It is still good practice to use SETRANGE though, but I always say that's because it is much easier to use.

David_Singleton

brijlesh.p wrote:

hi,

Sometimes we use Setfilter to filter few data and sometime we use SetRange.
Can you explain the basic difference of SetFilter and SetRange in detail?

thanks

This absolutely depends on who you speak to. I once asked one of the developers at PC&C that was part of the team that wrote the compiler and he told me that SETRANGE does not exist in the compiler, and basically at compile time a SETRANGE command is converted and compiled as the equivalent SETFILTER command. i.e.

SETRANGE(MyField,'A','Z');  
becomes
SETFILTER(MyField,'%1..%2','A','Z');

But since then I have heard people say in training classes that they were told that Setrange is faster.

My information comes from Native days (before SQL), so there is a possibility that this changed in SQL, and that in SQL SETRANGE is faster. But that should be easy to see by just looking at the T-SQL generated by a setrange compared to a setfilter.

And of course maybe its changed since then.

kriki

On the native database, SETRANGE can be faster than SETFILTER.
Years ago, I have had a query that was slow with SETFILTER and fast with SETRANGE.
I also have had other instances where there was no difference between SETRANGE and SETFILTER.

On SQL, it doesn't matter.

But it is still more readable using SETRANGE!

David_Singleton

kriki wrote:

...
Years ago, I have had a query that was slow with SETFILTER and fast with SETRANGE....

Are you able to replicate this?

kriki

David Singleton wrote:

kriki wrote:

...
Years ago, I have had a query that was slow with SETFILTER and fast with SETRANGE....

Are you able to replicate this?

With that particular query, I could replicate it all the time.
Then with an other (comparable one) I thought to fix it, but it didn't work.

And now you probably want to example, I suppose.
I wish I still had it. I was still in Belgium when I had the problem.

David_Singleton

kriki wrote:

David Singleton wrote:

kriki wrote:

...
Years ago, I have had a query that was slow with SETFILTER and fast with SETRANGE....

Are you able to replicate this?

With that particular query, I could replicate it all the time.
Then with an other (comparable one) I thought to fix it, but it didn't work.

And now you probably want to example, I suppose.
I wish I still had it. I was still in Belgium when I had the problem.

Maybe it was a data related issue.

for example lets say you have a routine that scans item and then filters/setrange on Item Ledger Entries. Then lets say you have an Item no.: '*A*' setrange and setfilter will interpret this very differently and that would explain the performance difference.

Was it a huge difference, or barely measurable?

kriki

David Singleton wrote:

Was it a huge difference, or barely measurable?

It went from some minutes to seconds.
I changed the command several times from SETRANGE to SETFILTER and back to be sure it wasn't some cache-related thing and each time it was fast with SETRANGE and slow with SETFILTER.