Use of SETFILTER in Report to calculate Purchase Qty

neellotus · 2012-07-20

Hi Navision Experts!

I have a report that contains following filter, according to user report is displaying wrong data so i want to understand this filter.

ILE1.SETFILTER(ILE1."Posting Date",'%1..%2',CALCDATE('-1M',EndDate),EndDate);

My whole code of report to calculate PurchaesQty

ILE1.RESET;
ILE1.SETCURRENTKEY("Item No.","Posting Date","Location Code","Remaining Quantity");
ILE1.SETRANGE(ILE1."Item No.","No.");
ILE1.SETFILTER(ILE1."Posting Date",'%1..%2',CALCDATE('-1M',EndDate),EndDate);
IF LocationFilter <>'' THEN
ILE1.SETFILTER(ILE1."Location Code",LocationFilter);
ILE1.SETFILTER(ILE1."Remaining Quantity",'>%1',0);
IF ILE1.FIND('-') THEN
REPEAT
PurchaseQty+=ILE1.Quantity;
ILE1.CALCFIELDS("Cost Amount (Actual)");
PurchaseValue+=ILE1."Cost Amount (Actual)";
UNTIL recILE1.NEXT = 0;

Kindly suggest me how purchase qty is calculating.

Thanx.

David_Singleton · 2012-07-20

There are many minor errors in that code, so without some idea of what error you are looking for it would be pure guess work. Start the debugger and trace through to see where it is going wrong. But don't start untill you know what the results are supposed to be and where the results should come from. Copy paste the ILE and Value entries to excel and compare them to your actual and expected results.

vijay_g · 2012-07-20

ILE1.SETFILTER(ILE1."Posting Date",'%1..%2',CALCDATE('-1M',EndDate),EndDate);

This is applying filter on "Posting Date" field whatever the enddate value.
if enddate value is 20-07-2012(DD-MM-YYYY) then

ILE1.SETFILTER(ILE1."Posting Date",'%1..%2',20-06-2012,20-07-2012);

Hope it's clear now....!!!

mohana_cse06 · 2012-07-20

You can run the form with filters to know what entries are you getting

ILE1.RESET;
ILE1.SETCURRENTKEY("Item No.","Posting Date","Location Code","Remaining Quantity");
ILE1.SETRANGE(ILE1."Item No.","No.");
ILE1.SETFILTER(ILE1."Posting Date",'%1..%2',CALCDATE('-1M',EndDate),EndDate);
IF LocationFilter <>'' THEN
  ILE1.SETFILTER(ILE1."Location Code",LocationFilter);
ILE1.SETFILTER(ILE1."Remaining Quantity",'>%1',0);
FORM.RUN(FORM::"Item Ledger Entries",ILE1);

neellotus · 2012-07-20

Hi Vijay Gupta,

Firstly thanx for your reply,

As per your example what will be the range of posting date to select data.

Thanx

Use of SETFILTER in Report to calculate Purchase Qty

Comments

Categories