Expiration Calculation Report

JKoB

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

JKoB

Any suggestions?

Albertvh

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

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

Hi Jakob,

You could try this

DaysToExpiration := CALCDATE(-RLZ,TODAY - MHD);

Hope this helps

Albert