Rounding problem

Astinia
Astinia Member Posts: 40
in NAV/Navision Classic Client
Hello Guru,

NAV 2009. The customer have bought an add-on very long ago, it huge and relates to the Warehouse.
The customer made wrong decision and put base Unit of measure Case, which consists of 6 Jars. So, they produce in cases and sell in jars.
That's was wrong, but that's what we have to deal with now.

Now we've ended up with that tiny 0.000xx numbers in Item ledger Entries and Warehouse Ledger Entries.
And even more: if I make totals with a help of SQL server - the amount is correct. If I do it in the NAV Codeunit - I have those differences. NAV calculates the amount inside it, so..

I've tried Phys. Inventory Journal => Calculate Inventory with a filters >-0.001&<0.0001, but not much of a help. It won't find the smallest things.

Could anyone suggest something standard? or the only way - is to select it on my own and fill the Phys. Inventory Journal and then post?

Thanks for your time

Answers

  • vremeni4
    vremeni4 Member Posts: 323
    Hi,

    This is a tricky problem.
    The problem is that values in NAV are usually rounded to 2 decimal places.
    For example :
    we have SQL value 1.5449999999
    NAV will round this to 2 decimal places so it will show 1.54.
    Now if you add this value three times in SQL
    1.5449999999 + 1.5449999999 + 1.5449999999 = 4.6349999997
    then the SQL value will be 4.634989 but NAV will show 4.63
    But if you do the calculation in NAV we will have
    1.54 + 1.54 + 1.54 = 4.62
    So if you add the values in NAV it will show 4.62 instead if 4.63.
    So you have a difference of 0.01.

    Increasing the number of decimal places in NAV will not resolve the problem.
    It will just move it to another decimal place.

    What standard does in this case it inserts and posts an additional correction line.
    Take a look at the "general Journal", there is option to add additional rounding line.

    In other words your suggestion
    or the only way - is to select it on my own and fill the Phys. Inventory Journal and then post?
    is the option to resolve this problem, as far as I know. (even standard NAV would do this)

    I hope this helps.
  • Astinia
    Astinia Member Posts: 40
    thanks for the fast reply. SQL provides correct numbers, but NAV don't. I bet that the problem in how it make it CalcSums...

    When I do cycle like this:

    WarehouseEntry.SETCURRENTKEY("Item No.");
    WarehouseEntry.SETRANGE("Item No.","No.");
    WarehouseEntry.SETFILTER("Location Code",GETFILTER("Location Filter"));
    WarehouseEntry.SETFILTER("Bin Code",GETFILTER("Bin Filter"));
    WarehouseEntry.SETFILTER("Lot No.",GETFILTER("Lot No. Filter"));
    WarehouseEntry.SETFILTER("Registering Date",GETFILTER("Date Filter"));

    WarehouseInventory := 0;
    WarehouseInventoryBase := 0;
    WarehouseRemainingBase := 0;
    IF WarehouseEntry.FINDSET THEN REPEAT
    WarehouseInventory += WarehouseEntry.Quantity;
    WarehouseInventoryBase += WarehouseEntry."Qty. (Base)";
    WarehouseRemainingBase += WarehouseEntry."Remaining Qty. (Base)";
    UNTIL WarehouseEntry.NEXT = 0;

    I found out that I have decimals in places they shouldn't be. If I look in the entries itself - in NAV it will show me only correct number of decimals. Like 14.3333. BUT if I total it - I have 104.1234005
    This 0.0000005 gives a problem when you try to sell or move something in standard SO or any other document.

    Once again, thanks for the answer.
  • vremeni4
    vremeni4 Member Posts: 323
    Hi,

    Well in this case I would suggest to round the things to 2 decimal places, as SO should use 2 decimal places by default.
    This will resolve the issue with 0.0000005.

    You can try to do something like this , at the end of the code

    WarehouseInventory := ROUND( WarehouseInventory,0.01);
    WarehouseInventoryBase := ROUND( WarehouseInventoryBase,0.01);
    WarehouseRemainingBase := ROUND( WarehouseRemainingBase,0.01);

    Run some test in a test database, to make sure it is working fine.

    I hope this helps.
  • Astinia
    Astinia Member Posts: 40
    Thanks for the reply.
    The code I showed is only used in separate page to check those numbers.
    Now I do need to find place which causes the errors. They have a lot off add-ons :(

    My initial thought was that there is some report/codeunit out of the box which will help me with this task. Apparently I need to write it on my one.

    Thanks
  • kriki
    kriki Member, Moderator Posts: 9,121
    [Topic moved from 'Navision Financials' forum to 'NAV/Navision Classic Client' forum]

    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!

Categories