Physical Inventory process takes too long

Dynamics 365 Business Central/NAV Technical Forum
#1

Hi,

We are currently on Navision 4. Over the years our company size has grown and currently stands at around 65GB.

Over the last few years we have a problem with the ‘Calculate inventory’ function while doing the Physical inventory. This process when broken up into several batches takes really long to finish. It takes around 24 hours to complete this before the counting process can start.

It seems like Navision goes through the entire Inventory journal table to calculate the current inventory levels and really takes long. Since I am fairly new to Navision, just need advise from anyone who has experienced similar issues and how can this be improved.

We have looked at deleting data (but which data deletion reports would help in this process). Data compression of Inventory journals (but this report expects all the financial years to be closed! Although the end date was selected as 1 Sep 12, the report was asking to close the current period.).

Is there any way to bypass the calculate inventory process (if yes, is there any flip side to this?).

Any other advice to speed up this process would be appreciated.

Thanks

B

#2

I redesiged the report to a new report #50050.

Where instead of reading every item ledger entry ever created to just pull the Quanitity from item table.

So now I have 2 - the original “Calcualte Inventory” & one called “FAST Calculate Inventory”

If you have many locations this might not be the way to go for you.

OnAfterGetRecord()

Window.UPDATE;

Item.CALCFIELDS(“Real QOH”); //the name of our custom qty on hand folwfield

ItemJnlLine.“Phys. Inventory” := FALSE;

ItemJnlLine.“Journal Template Name” := ‘PHYS. INVE’; //set template name

ItemJnlLine.“Journal Batch Name” := ‘CYCLE’; //set batch name

ItemJnlLine.VALIDATE(“Source Code”,‘PHYSINVJNL’); //set source code

ItemJnlLine.“Document No.” := NextDocNo;

ItemJnlLine.“Entry Type” := ItemJnlLine.“Entry Type”::“Positive Adjmt.”; //force pos adj

ItemLedgEntry.“Posting Date” := WORKDATE;

ItemLedgEntry.“Document Date” := WORKDATE;

ItemJnlLine.“Line No.” := ItemJnlLine.“Line No.” + 10000; //increment line no

ItemJnlLine.VALIDATE(“Item No.”,Item.“No.”);

ItemJnlLine.“Shelf/Bin No.” := Item.“Shelf/Bin No.”; //insert shelf bin from item table

ItemJnlLine.VALIDATE(“Location Code”,‘NY’); //force our location code

ItemJnlLine.Description := Item.Description;

ItemJnlLine.“Description 2” := Item.“Description 2”;

ItemLedgEntry.SETCURRENTKEY(

“Item No.”,“Variant Code”,“Drop Shipment”,“Location Code”,“Bin Code”,“Posting Date”);

ItemLedgEntry.SETRANGE(“Item No.”,“No.”);

IF ItemLedgEntry.FIND(’+’) THEN

ItemJnlLine.“Last Item Ledger Entry No.” := ItemLedgEntry.“Entry No.”;

ItemJnlLine.“Phys. Inventory” := TRUE;

ItemJnlLine.“Qty. (Calculated)” := Item.“Real QOH”;

ItemJnlLine.VALIDATE(“Qty. (Phys. Inventory)”,Item.“Real QOH”);

ItemJnlLine.INSERT;

#3
  • do not do this!

A customer made a date-compression on Inventory and got it messed up. Main problem was that NAV lost references to applications.
We had to make a database-restore in order to get back on track.

Otherwise - Savatage’s idea seems to be a good one. But beware the location-issue.

#4

And moreover, if I remember properly, databases with data compression done are not officially supported by MS for a version upgrade.