Expiration Calculation Report

JKoB
JKoB Member Posts: 40
edited 2009-01-15 in NAV/Navision Classic Client
Hey Experts!

I created a Report (Item Ledger Entry) which should show me following:

Lot No. – Remaining Qty – Best Before
-> Sum per Lot

---> Works pretty fine until here.

OnPreDataItem
"Item Ledger Entry".SETFILTER("Remaining Quantity",'>1');

In Addition I created a Rec. on the Item Table (ItemRec).

OnAfterGetRecord
ItemRec.SETRANGE("No.","Item No.");

What I want now is a field in my Report which is filled with the “Expiration Calculation”

-> created Field with srcExpr.: ItemRec."Expiration Calculation"

My problem is that this Field stays empty.
Where is my failure?


The next step would be following calculation:

Expiration Date – Expiration Calculation – Today = x Days


How can I calc that?

Greets Jakob

Comments

  • JKoB
    JKoB Member Posts: 40
    Any suggestions?
  • Albertvh
    Albertvh Member Posts: 516
    Hi Jakob,
    As you only do a SETRANGE the item record is never retrieved.

    You should do this 
    OnAfterGetRecord
ItemRec.GET("Item No.");

    Hope this helps.

    Albert
  • JKoB
    JKoB Member Posts: 40
    Thx... That worked better... But the performance of the report was to bad.

    I tried it with the table "Lot No. Information" wich was much easier.
    "Lot No. Information".SETFILTER(Inventory,'<>0');

ItemRec.RESET;
ItemRec.GET("Item No.");
ItemRec.SETRANGE("No.","Item No.");
   IF ItemRec.FINDFIRST THEN
       RLZ := ItemRec."Expiration Calculation";

EP.RESET;
EP.SETRANGE("Lot No.","Lot No.");
   IF EP.FINDFIRST THEN
       MHD := EP."Expiration Date";

       DaysToExpiration := (TODAY - MHD)

    The correct calculation would be (TODAY - MHD - RLZ)
    Prob is that the type of "RLZ" is "DateFormula" how can i subtract it?

    Today - MHD is (e.g.) -366.
    The variabel RLZ has the value "1M" (1 month) - how can i convert the 1M into the Integer "30" (30Days) ?

    My next question would be how i can sort the whole report by those calculated fields. Possible?

    Thx in advance, Jakob
  • Albertvh
    Albertvh Member Posts: 516
    Hi Jakob,

    You could try this
    DaysToExpiration := CALCDATE(-RLZ,TODAY - MHD);

    Hope this helps

    Albert

Categories