Filter on Customer "Salesperson Code" - giving wrong results.

jonesks

Hi

I have
Customer.SETRANGE("Salesperson Code",'1','2');

to pick up only those customers with a salesperson code of 1 or 2 - when I run the report it gives me numbers 1 2 and 11?

What am I missing?

Thanks

AK

My bad, I misread your post somehow. You have to combine the two conditions you want to filter into a single filter string ('<%1&<>%2'´).

AK

customer.setfilter("Salesperson Code",'%1|%2','1','2');

Your code doesn't work because you filter from 1 to 2, and with the sorting of your DB the salesperson records look like this:

1
11
2
21
22
.
.

jonesks

I understand the logic but now it is only showing number 1

ErictP

In this case you are using the wrong filter.
The filter your using means set a filter on the "Salesperson Code" from '1' until '2'.
And because the "Salesperson Code"-field is datatype CODE everything starting with '1' is also in the filter.

The filter you can use is:
Customer.SETFILTER( "Salesperson Code",'%1|%2','1','2');

jonesks

Ok - Now I need to change it to filter only salesperson code 1 - 8. Any clues gratefully received!

ErictP

Ony 1 and 8 :
Customer.SETFILTER( "Salesperson Code",'%1|%2','1','8');

jonesks

Sorry - I meant between 1 and 8 as in 1,2,3,4,5,6,7,8 Thanks

ErictP

You can construct filters using the following operators.
Operator Description
.. Range
& And
| Or
< Less than
<= Less than or equal to
> Greater than
>= Greater than or equal to
<> Different from
* Forms a part of value
@ Case-insensitive

jonesks

I keep trying but struggle with the correct result. I have tried 1..8 but it keeps picking up 11.

have tried


Customer.SETFILTER("Salesperson Code",'%1..%8','1','8'); which only gives me the first page!

I am guessing I am missing something really silly, but I have only been working with NAV for a month!

nick_robbo

I think this is what you are looking for...

Customer.SETFILTER( "Salesperson Code",'%1..%2','1','8');

jonesks

Hi Nick - Tried that and it is still picking up those with a code of 11 - not sure why.

nick_robbo

can you try

Customer.SETRANGE("Salesperson Code", '1', '8');

Alternatively you could just use..

Customer.SETFILTER( "Salesperson Code", '<=%1', '8');

jonesks

Both of those pick up code 11

ErictP

%1 = first parameter
%2 = second parameter

Now you used %8 and that is the eightst parameter. Only you never used a 8st parameter in your statement.

Customer.SETFILTER( "Salesperson Code",'%1|%2','1','8');
Translation:

Set a filter on the customer-tabel ( Customer.SETFILTER)
on the field "Salesperson Code" ( "Salesperson Code" )
Use as filtervalue parameter 1 OR parameter 2 ( ,'%1|%2' )
use for the first parameter the value '1' ( ,'1' )
use for the second parameter the value '8' ( ,'8')

nick_robbo

I think it is because "1" appears in the code for "11"

AK

Try to understand what I wrote about sorting, then you will know why none of the suggestions will work.

jonesks

Customer.SETFILTER( "Salesperson Code", '<=8', '<>11'); tried this as well - which to me logically should work.
Smaller or equal to 8 and not 11.

jonesks

AK - what you wrote is making sense to me, 1,11, 2 ,22 etc. but for the life of me I cannot see a way of doing it other than to select it based on 1 - 8 and then exclude 11?

AK

Smaller 8 includes 11. Look at the sorting, I don't know what else to say exept giving you the solution. But that way it wouldn't klick, right?

jonesks

I see that 11 is smaller than 8 and I understand that, that is why I went for <=8 but <>11. Would I have to do this in two separate stages?

jonesks

Ok, so my next theory is SETFILTER <=8 then use an if statement ? if = 11 then CurrReport.SKIP? Am I on the right lines?

AK

My bad, I misread your post somehow. You have to combine the two conditions you want to filter into a single filter string ('<%1&<>%2'´).

jonesks

Thanks AK - it's been a bit of a curve but one I am sure is worth doing!

jonesks

Just wanted to say thanks everyone - my logic was right but I was trying to put the > or <> in the wrong place rather than setting parameters! As I said very new to this but finding the forums really helpful for learning

Slawek_Guzek

you can also try

SETFILTER('?&%1..%2', '1', '8');

which translates to 'give me all codes exactly one character long, and between '1' and '8' inclusive.

jonesks

Thanks, I managed to get the correct Syntax for codes 1-8 exclude 11 which covers what i needed.