Applying Item Filter

PradeepMindshellInfo · 2012-03-30

When i am applying filter like this on item table.Error Message is pop up:-

Microsoft Dynamics NAV Classic

A value in the filter ''1000|1001|1100|1110|1120|1150|1151|1155|1160|1170|1200|1250'' in the No. field in the Item table is too long for the field type.

Type: Code20

OK

Item.RESET;
Item.SETFILTER("No.",'%1',FilterStringToApply);
Item.SETFILTER("No.",'%1','1000|1001|1100|1110|1120|1150|1151|1155|1160|1170|1200|1250');
Item.CALCFIELDS(Inventory);
IF Item.FINDFIRST THEN
REPEAT
ItemInventory+=Item.Inventory;
UNTIL Item.NEXT=0;

Item.RESET;
Item.SETFILTER("No.",'%1',FilterStringToApply);
Item.SETRANGE("No.",'%1','1000|1001|1100|1110|1120|1150|1151|1155|1160|1170|1200|1250');
Item.CALCFIELDS(Inventory);
IF Item.FINDFIRST THEN
REPEAT
ItemInventory+=Item.Inventory;
UNTIL Item.NEXT=0;

Will someone suggest me what i have to do to achieve the same?

Thanks and Regards
Pradeep Bhardwaj
Mindshell infotech

Belias · 2012-03-30

with this syntax

Item.SETFILTER("No.",'%1','1000|1001|1100|1110|1120|1150|1151|1155|1160|1170|1200|1250');

you mean you want to use '1000|1001|1100|1110|1120|1150|1151|1155|1160|1170|1200|1250' as a single value filter...like if there's a location named '1000|1001|1100|1110|1120|1150|1151|1155|1160|1170|1200|1250'.
you instead want to do this

Item.SETFILTER("No.",'1000|1001|1100|1110|1120|1150|1151|1155|1160|1170|1200|1250')

or this

Item.SETFILTER("No.",'%1|%2|%3|%4',1000,1001,1002,1003);

Yashojit_Pandhare · 2012-03-30

Hi,

The following should work

Item.SETFILTER(Item."No.",'%1|%2|%3|%4|%5|%6|%7|%8|%9|%10',
'001','002','003','004','005','006',
'007','008','009','010');

:thumbsup:

Belias · 2012-03-30

2 remarks:
- there's a limit in the number of replacement characters (%Number)...about 12-15, I don't remember...anyway, you can work around this limit in a lot of ways, just search mibuso
- don't hardcode filters (don't hardcode in general), if possible.

MBerger · 2012-03-30

Another small bug in your code : Put the CalcFields statement INSIDE the loop, because you need to recalculate the flowfield for every record. ALso, why the filter on "FilterStringToApply" just before the other filter ? The second will negate the first anyway.

KYDutchie · 2012-03-31

One other little thing, don't use a FINDFIRST with a repeat loop.

David_Singleton · 2012-03-31

<deleted>

Work out what these locations have in common and add that as a field to the location Item table and possibly to the ILE table (you need to understand the selectivity of those fields to decide the best approach) and then design around that. Hard coding will always come back to bite you.

<deleted>

David_Singleton · 2012-03-31

Just looking now I see its Item No you are putting that filter on, not location.

Why such a strange filter on Item No. ?

David_Singleton · 2012-03-31

so something like this

item.reset;
item.setcurrentkey("New Flag");
if item.findset then begin
  itemfilter := item.no.;
  repeat
    itemfilter += '|' + item.code;
    /// put code in here to check that the itemfilter does not exceed the max length
  until item.next = 0;
end;
ItemLedgerEntry.Reset;
ItemLedgerEntry.setcurrentkey..... work out what's best
ItemLedgerEntry.SETFILTER("item No.",itemfilter);
Itemledgerentry.CALCSUMS("Quantity (Base)"); // < here you have an issue because each item is different and will possibly have different UOMs
ItemInventory := ItemLedgerEntry."quantity (base)";

What a mess. You need to sit with the client and work out why they have apples and oranges and cheesecakes and pieces of string and what they are expecting the total to be.

ssingla · 2012-04-01

David Singleton wrote:

<deleted>

Work out what these locations have in common and add that as a field to the location Item table and possibly to the ILE table (you need to understand the selectivity of those fields to decide the best approach) and then design around that. Hard coding will always come back to bite you.

<deleted>

:thumbsup:

PradeepMindshellInfo · 2012-04-03

Item.RESET;
Item.SETFILTER("No.",'%1|%2|%3|%4|%5|%6|%7|%8|%9|%10','1000','1001','1100','1110','1120','1150','1151','1155','1160','1170') ;

:thumbsup:

Its Working...

but if we have more no of Parameters 12...we have to find alternate........................

PradeepMindshellInfo · 2012-04-03

Thnks to all ................

Belias · 2012-04-03

PradeepMindshellInfo wrote:

Item.RESET;
Item.SETFILTER("No.",'%1|%2|%3|%4|%5|%6|%7|%8|%9|%10','1000','1001','1100','1110','1120','1150','1151','1155','1160','1170') ;

:thumbsup:

Its Working...

but if we have more no of Parameters 12...we have to find alternate........................

](*,) ](*,) ](*,) ](*,)
you really missed the IMPORTANT insights of this thread, given by me, david and remarked by the others...
i'll summirize with this:
the code you've written is CRAP (i know, i'm rude sometimes), and you've to STUDY some basic programing BEFORE programming.
You need a senior developer to give you some serious advices. We can give you the solution to this problem with 1 line of code, but you'll face a lot more problems in the future...
(I'm assuming you're doing this in a customer's database...if instead you're doing it for study/self teaching, just don't mind what i said

)

PradeepMindshellInfo · 2012-04-03

I am doing this, for my learning..................

i did not get your way of reply. Should i mention this i am doing this for my learning or on Client Database.
to get its answer.

You just mention that you can solve this in Single line what you did not suggest any solution.
i am not exactly getting why are you getting so much rude about answering.

:-k #-o #-o

KYDutchie · 2012-04-03

PradeepMindshellInfo wrote:

Item.RESET;
Item.SETFILTER("No.",'%1|%2|%3|%4|%5|%6|%7|%8|%9|%10','1000','1001','1100','1110','1120','1150','1151','1155','1160','1170') ;

:thumbsup:

Its Working...

but if we have more no of Parameters 12...we have to find alternate........................

Please read all the previous posts again and then read the comment below,

First question you need to ask yourself, the business consultant or the customer is: "What is the business reason to have this filter?"
Second question you need to ask is: "Is there a common item property that all these items share?". If the answer is "Yes" then use that property to filter on.
If the answer is "No" to the second question then work with the customer to see if there is a field in the item table, like Item Category code, variant, etc that you can use to filter on. If that is not possible then add a field to the item table to use for filtering these items out. Once you have that figured out, make sure that you convert to the same unit of measure before summing the quantities.

Hardcoding this filter is the worst you can do for you and for the customer.

Someone once said that creating a major bug in NAV is hard to do, but seeing this..........I truly start wondering.... :?

Hope this helps,

Willy

PradeepMindshellInfo · 2012-04-03

Dear Sir,

I have solved this problem on client database, By creating an additional field on Item card.

This is not the requirement of any Business-Process.
This question just belong to learning.

Actually i am creating a string of item with the help of loop.

StringtoApply:=Item1|Item2|Item3|Item4........|ItemN.

After this i am applying it on Item Table.

Like

Item.RESET;
Item.SETFILTER("No.",'%1',StringtoApply);
IF Item.FINDFIRST THEN BEGIN
.......................
......................
END;

But this is not woking. An error popped up.

So i shared it with you all........................................

Belias · 2012-04-03

Sure, I'll try to moderate my reaction a bit:

Item.SETFILTER("No.",'%1|%2|%3|%4|%5|%6|%7|%8|%9|%10','1000','1001','1100','1110','1120','1150','1151','1155','1160','1170') ;

we've solved your initial problem ("applying Item Filter") with one line of code, isn't it? (anyway, "don't take it literally"

)
but this is not really the point of what i said:
a) If this code is for a customer and production database, it is really dangerous(see KYDutchie's suggestion): you shouldn't work for a customer if you're still at your "first steps" in NAV...study, ask senior developer, search/ask mibuso and AFTER ALL OF THIS you can start to work for customers
b) If this code is for personal study etc. then it's ok because you're not hurting anyone...you're experimenting in order to learn something more.

The last insight of the thread is that if we give you the solution you can solve easily today's problem, but you're going to face some other problems in the future...
If you learn from errors and think why a solution is better than another, you'll learn something new and you'll be able to solve your problems alone, and eventually even help someone else in the future!

PradeepMindshellInfo · 2012-04-04

This dos not belongs to any customer or production database. I have already told you.....
and i am not on the initial point of my career.

Thanks for all to get so much worry about my future.

Thanks to all for their suggestion.

=D>

Yashojit_Pandhare · 2012-04-04

Why such a Hue and Cry over this issue....

It is just a very specific solution to the specific error message posted:

PradeepMindshellInfo wrote:

When i am applying filter like this on item table.Error Message is pop up:-
Microsoft Dynamics NAV Classic
A value in the filter ''1000|1001|1100|1110|1120|1150|1151|1155|1160|1170|1200|1250'' in the No. field in the Item table is too long for the field type.

Type: Code20
OK

How can we start analysing or advising how it is to be done without knowing what a person is upto and what he is trying to achieve!!

ssingla · 2012-04-04

Yashojit Pandhare wrote:

Why such a Hue and Cry over this issue....

It is just a very specific solution to the specific error message posted:

PradeepMindshellInfo wrote:

When i am applying filter like this on item table.Error Message is pop up:-
Microsoft Dynamics NAV Classic
A value in the filter ''1000|1001|1100|1110|1120|1150|1151|1155|1160|1170|1200|1250'' in the No. field in the Item table is too long for the field type.

Type: Code20
OK

How can we start analysing or advising how it is to be done without knowing what a person is upto and what he is trying to achieve!!

:thumbsdown:

Everything is point less if you are not ready to understand what others (experienced people of the community) are suggesting.

Sog · 2012-04-04

Yashojit Pandhare wrote:

Why such a Hue and Cry over this issue....

It is just a very specific solution to the specific error message posted:

PradeepMindshellInfo wrote:

Wh...

How can we start analysing or advising how it is to be done without knowing what a person is upto and what he is trying to achieve!!

Indeed it is. But the entire code cries out: "I'm solving a specific case instead of looking at the whole picture".
This code can only handle a rather restricted set of cases. And it doesn't look like it can handle special circumstances.
That's why we are crying about it. (I've wept like a couple of hours after reading this post...)

GRIZZLY · 2012-12-17

Just not to start new thread I suggest to continue existing with my issue. I also searched for familiar topics, but haven't founded, but I remember that I've read some articles on it, but now forget how to find it.
I want to optimize my filter criteria, because, you know, it has the limit for length. So, in general i want to decrease length based on the principle (take a look at examples):
if we've got 3 lines, 10000, 20000, 30000 and all of them should be included, then filter should be 10000..30000.
if it should contain only 10000 and 30000 it should be 10000|30000.
in my situation i think it would always contain starting and ending line, so the string length should not exceed.
Please, help! )

GRIZZLY · 2012-12-17

Founded 2 options:
https://github.com/dmites/NAV/blob/master/Codeunit%2050178.txt

and

SetSelectionFilter trigger in 31 Item List form.

Howdy, Stranger!

Quick Links

Categories

Applying Item Filter

Comments