how to use setfilters

Business Central/NAV User Forum
1

Could someone explain me how can i use Set Filters to filter OptionFields (ie.,i need to filter ItemLedgerEntryType = sale )…

2

ItemLedgerEntry.SETRANGE(Type,ItemLedgerEntry.Type::Sale);

ItemLedgerEntry.SETFILTER(Type,’%1|%2’,ItemLedgerEntry.Type::Sale,ItemLedgerEntry.Type::Purchase);

3

k fine … what is the diff between using SetFilters and SetRange???

4

onnum illai thayavu saithu close pannunga…

by urs collegue…

5

Difference beween SetFilters and SetRange - - -

  • With SETFILTER, you can specify a range eg
    SETFILTER("Result ",’%1…%2’,‘a’,‘b’);
    but with SETRANGE statement, you cannot give a range of values.you have to enter exact value for comparison and filtering. SETRANGE(“Result Value”,‘a’);

  • SETFILTERS are always low on performance than SETRANGE.as they generate more disk I/O than SETFILTER for the same operation. so always try to use SETRANGE in place of SETFILTER , if possible.

6

SETRANGE is called SETRANGE because you can specify a range, not just one value. I don’t know where you got that, but it is not correct.

This will have the same results for a range:

  • MyRec.SETFILTER(MyField,’%1…%2’,FromValue,ToValue);

  • MyRec.SETRANGE(MyField,FromValue,ToValue);

This will have the same results for a single value:

  • MyRec.SETFILTER(MyField,’=%1’,SingleValue);

  • MyRec.SETRANGE(MyField,SingleValue);

The difference between FILTER and RANGE is that with RANGE you don’t have to worry about the filter string, and it is a little better at type conversion. They say that it is faster, but I am not convinced that it is. If there is a difference then it is very marginal, you won’t see a 50% performance increase just by using SETRANGE. I like SETRANGE because it is easy to use. If oyu only have one value, or indeed a range, then I suggest you use SETRANGE, just because it is more friendly to use.

So for instance, you want to do an OR query:

  • MyRec.SETFILTER(MyField,’%1|%2’,EitherValue,OrValue);

  • There is no way to do this with SETRANGE

7

Thanks a lot…

8

I don’t know who “they” are. [^o)]

But when I explain it to people, I say that it has the POSSIBILITY to be faster. To be honest, I think that for SQL it doesn’t make a lot of a differrence. But on the Navision-DB, I have seen cases where it made the difference between a tablescan and a scan of the range of records. But it was always when I had a range of records and not only 1 value.

But in short : it is still better to use SETRANGE because it is easier/faster to write the code but also to read it.