Getting the First Date of a Shortage

CarolynSasser

Hi I am new to Nav.

I need to run a report that shows me what date and SO we will be short for an Item.

example for the item listed below I need to get the date 11/21/2018 and the SO SO100306.
Item No Date SO QTY Inv after Order
F100314S 10/26/2018 SO100296 420 628
11/16/2018 SO100307 420 208
11/21/2018 SO100306 420 -212
11/23/2018 SO100308 420 -632

Can anyone give me some advise?

Thank you

wolfskin

In my opinion, create a report then define Item table to be data source. then recursive into sales order line to find the first date of shortage.

I just show you an example code for guiding (it need to be refined a little bit if you want more complete result).

Item - OnAfterGetRecord() 

CALCFIELDS(Inventory);
CLEAR(ShortageDate);
ShortageQty := Inventory;

SalesLine.RESET;
SalesLine.SETCURRENTKEY("Posting Date");
SalesLine.SETRANGE("Document Type",SalesLine."Document Type"::Order
SalesLine.SETRANGE(Type,SalesLine.Type::Item);
SalesLine.SETRANGE("No.",Item."No.");
IF SalesLine.FIND('-') THEN
REPEAT
  SalesLine.CALCFIELDS("Posting Date");
  ShortageQty := ShortageQty - SalesLine."Outstanding Qty. (Base)";
  IF ShortageQty < 0 THEN
  BEGIN
    ShortageDate := SalesLine."Posting Date";
    SO := SalesLine."Document No.";
  END;
UNTIL (SalesLine.NEXT = 0) OR (ShortageQty < 0);

wolfskin

In my opinion, create a report then define Item table to be data source. then recursive into sales order line to find the first date of shortage.

I just show you an example code for guiding (it need to be refined a little bit if you want more complete result).

Item - OnAfterGetRecord() 

CALCFIELDS(Inventory);
CLEAR(ShortageDate);
ShortageQty := Inventory;

SalesLine.RESET;
SalesLine.SETCURRENTKEY("Posting Date");
SalesLine.SETRANGE("Document Type",SalesLine."Document Type"::Order
SalesLine.SETRANGE(Type,SalesLine.Type::Item);
SalesLine.SETRANGE("No.",Item."No.");
IF SalesLine.FIND('-') THEN
REPEAT
  SalesLine.CALCFIELDS("Posting Date");
  ShortageQty := ShortageQty - SalesLine."Outstanding Qty. (Base)";
  IF ShortageQty < 0 THEN
  BEGIN
    ShortageDate := SalesLine."Posting Date";
    SO := SalesLine."Document No.";
  END;
UNTIL (SalesLine.NEXT = 0) OR (ShortageQty < 0);

CarolynSasser

Thank you for the help. I actually had to place it on Sales Lines - OnAfterGetRecord() as It wasn't assosiating with the correct item. I also has to add a sort on the Shipment Date as I needed to know what shipment date the item was going to be short.

That being said, you code was great in solving the problem.