Rounding problem

Astinia

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

vremeni4

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

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

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

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

[Topic moved from 'Navision Financials' forum to 'NAV/Navision Classic Client' forum]