Quantity on hand of an item in a location

  1. In Dynamics Nav, how do I find current quantity on hand of each item at each location using an sql query? Is it the Remaining Quantity with the max Entry No_ for each item and location from the Item Ledger Entry table ?

  2. The column [Entry Type] in table Item Ledger Entry has numbers - which table has the description for these number codes?

Thanks.

You can write a query on Item Ledger Entry table and get the required output

In Sql Table it will show the number and description will be shown in Navision client .There is no separate table for description

Have you check Item by Location report in Navision ?

See the sql query below. this will give you the on hand quantity by location code.

SELECT
sum(Quantity), [Location Code]
FROM [WYCOM Corp_$Item Ledger Entry] where [Item No_] =‘xxxxxxxx’
group by [Location Code]

[mention:a75f6c3a96644d2c8fb83a0bb67a2f22:e9ed411860ed4f2ba0265705b8793d05] did these suggestions answer your questions? If yes, please mark which one with “This helped me”. If not, then please tell us what the problem then is.