Retrieve Inventory Balances by Webservices

Business Central/NAV
1

I have a question regarding inventory balances in Business Central. I am trying to retrieve inventory balances per item per location using OData web services, but I’m concerned that this requires me to pull a lot of data from Business Central in order to calculate the balances myself.

Does anyone know if there is a table or object in Business Central that contains this information already calculated, so that I can avoid pulling unnecessary data and improve performance? Any insights or advice would be greatly appreciated! Thank you."

2

Yes, there is a table in Business Central called “Item Ledger Entry” that contains the inventory balance information for each item per location. This table tracks all inventory transactions, including inventory receipts, shipments, adjustments, and movements, and can be filtered by item and location to get the balance for a specific item at a specific location. You can also use the “Item Availability by Location” report to view the inventory balances for all items at all locations in a single report. Retrieving inventory balances through these methods should improve performance and reduce unnecessary data retrieval.

3

Hi Scarlett

Thanks for your replay - the problem is time - it takes about 2 hourts to get Item Ledger Entry out by webservices, it is possible to retrieve the item Availability by location report by a webservices. -

Klaus

4

Inventory Balance is often needed as a specific “as-of” date. Which is not a simple matter of pulling item Ledger Entries. Thus not well suited to a simple web service call.

5

Hi bbrown5962

Thanks for your Replay

Would it be possible to calculate the inventory balance in BC and display it in a table that can be extracted to BC? Ideally, this table would only calculate the inventory balance from the last stock count. The goal is to speed up the process of getting the inventory balance into BI, so that there is no need to calculate it again in Power BI.

6

Can you define what you mean by “Inventory Balance”? Also how would it be calculated? Are we talking about Quantity or Value or Both? Also do you need to be able to report this as-of a prior date?

I think you are talking about the “Inventory Valuation” here, but I want to be sure.

7

We are only talking Quantity on stock for each item on each lokation Now

8

I got it - we could take this tabel: Stockkeeping Unit List (5701, List) , which have this information that we need (Item, Quantity now, location)

9

Assuming stockkeeping unit was created for the desired item. Regardless a Query object to the Item Ledger Entry table for the desired location called from a Web service should provide the desired results.