Random Inventory

Rikarddo · 2018-10-17

Hi all, i'm using Dynamics NAV 2016 and my goal is to use Report 790 (Calculate Inventory) to make monthly inventories.

It should generate a random maximum number of products for the user to check.

In the InsertItemJnlLine function I created a variable that counts the number of rows after the journal entry:
...
INSERT (TRUE);
Count Lines: = Count Lines + 1;
...
Then at the end I put the following code:

IF (Count Lines> NumberProducts) AND (NumberProducts <> 0) THEN
BEGIN
CurrReport.BREAK;
END;

Where NumberProducts is the variable that chooses the quantity of products to be inventoried.

But this will only show the number of products I've bound, and does not traverse the table item randomly, this is my first difficulty.
Another difficulty is:
Imagine that 50 products are chosen each month. How do I avoid these 50 products being "chosen" next month.
I thought about creating a variable in the item table, where whenever this report runs, that field is populated with the date and then only apply the filter if that field is empty. Is this acceptable?

Rikarddo · 2018-10-18

Got it working by creating a new Report and modify the code in this function.
Thanks for the tips!!

LOCAL CalcPhysInvQtyAndInsertItemJnlLine()
// ++++++++++++++ start new code #1
QuantityOnHandBuffer.RESET;
IF NOT ZeroQty THEN // user input
  QuantityOnHandBuffer.SETFILTER(Quantity, '<>0');

// get all items, which have been processed by the report before
// this will also respect any given filter by the user on the RequestPage
// ItemBuffer is a temporary variable
IF QuantityOnHandBuffer.FINDSET THEN
  REPEAT
    IF NOT ItemBuffer.GET(QuantityOnHandBuffer."Item No.") THEN
      BEGIN
        ItemBuffer.INIT;
        ItemBuffer."No." := QuantityOnHandBuffer."Item No.";
        ItemBuffer.INSERT;
      END;
  UNTIL QuantityOnHandBuffer.NEXT = 0;

// now randomize
MaxStepsPossible := ItemBuffer.COUNT;
MaxItems := 10; // this could be a new option on the RequestPage
FOR i := 1 TO MaxItems DO
  BEGIN
    ItemBuffer.FINDFIRST; // always start from first record
    ItemBuffer.NEXT(RANDOM(MaxStepsPossible));
    ItemBuffer.MARK(TRUE);
  END;
// ---------------- end new code #1

WITH QuantityOnHandBuffer DO BEGIN
  RESET;
// ++++++++++++++ start new code #2
  // loop through my randomized result of the items which have been processed by the report
  ItemBuffer.MARKEDONLY(TRUE);
  IF ItemBuffer.FINDSET THEN
    REPEAT
      SETRANGE("Item No.", ItemBuffer."No.");
      IF FINDSET THEN
        REPEAT
          MARK(TRUE); // mark the corresponding records in the QuantityOnHandBuffer
        UNTIL NEXT = 0;
    UNTIL ItemBuffer.NEXT = 0;
    SETRANGE("Item No.");
    MARKEDONLY(TRUE); 
// ---------------- end new code #2
  IF FINDSET THEN BEGIN
    REPEAT
      PosQty := 0;
      NegQty := 0;

Thanks a lot!

Miklos_Hollender · 2018-10-17

Ugh. Random counting is not a good idea for this reason. Firms typically do cycle counting, like fast moving products every month, slow moving every quarter, or rolling counting, like putting items into 12 groups and count the first group in Jan, the second in Feb. They could implement the later without any programming, basically just looking at a list in January sorted by no. or name and say OK guys let's count everything starting with A, B or C this month.

At any rate, NAV has cycle counting, and if you look at the Item Card, Warehouse tab, there are fields like next counting period start, end etc. date. This is uneditable and normally written automatically. Make it editable and play with it. If you set two products to be counted next week, does it work, does it take them into accounting when running it?

Suppose that works. If they insist on randomness you will probably need to write code that when an inventory list is generated, or when the inventory is posted, the next counting period of these items is bumped up into next year. And another code that that as a batch job updates the ones that are in this year randomly.

This really looks like an ugly thing. Just convince them to use rolling counting

Rikarddo · 2018-10-17

Ok the code is working and it gets back a number of random items.

Now my purpose on Report 790 is to limit the function , to my random items?
...
Item - OnPostDataItem()
CalcPhysInvQtyAndInsertItemJnlLine;
...

Rikarddo · 2018-10-18

Got it working by creating a new Report and modify the code in this function.
Thanks for the tips!!

LOCAL CalcPhysInvQtyAndInsertItemJnlLine()
// ++++++++++++++ start new code #1
QuantityOnHandBuffer.RESET;
IF NOT ZeroQty THEN // user input
  QuantityOnHandBuffer.SETFILTER(Quantity, '<>0');

// get all items, which have been processed by the report before
// this will also respect any given filter by the user on the RequestPage
// ItemBuffer is a temporary variable
IF QuantityOnHandBuffer.FINDSET THEN
  REPEAT
    IF NOT ItemBuffer.GET(QuantityOnHandBuffer."Item No.") THEN
      BEGIN
        ItemBuffer.INIT;
        ItemBuffer."No." := QuantityOnHandBuffer."Item No.";
        ItemBuffer.INSERT;
      END;
  UNTIL QuantityOnHandBuffer.NEXT = 0;

// now randomize
MaxStepsPossible := ItemBuffer.COUNT;
MaxItems := 10; // this could be a new option on the RequestPage
FOR i := 1 TO MaxItems DO
  BEGIN
    ItemBuffer.FINDFIRST; // always start from first record
    ItemBuffer.NEXT(RANDOM(MaxStepsPossible));
    ItemBuffer.MARK(TRUE);
  END;
// ---------------- end new code #1

WITH QuantityOnHandBuffer DO BEGIN
  RESET;
// ++++++++++++++ start new code #2
  // loop through my randomized result of the items which have been processed by the report
  ItemBuffer.MARKEDONLY(TRUE);
  IF ItemBuffer.FINDSET THEN
    REPEAT
      SETRANGE("Item No.", ItemBuffer."No.");
      IF FINDSET THEN
        REPEAT
          MARK(TRUE); // mark the corresponding records in the QuantityOnHandBuffer
        UNTIL NEXT = 0;
    UNTIL ItemBuffer.NEXT = 0;
    SETRANGE("Item No.");
    MARKEDONLY(TRUE); 
// ---------------- end new code #2
  IF FINDSET THEN BEGIN
    REPEAT
      PosQty := 0;
      NegQty := 0;

Thanks a lot!

Random Inventory

Best Answer

Answers

Categories