Small and medium business | Business Central, N...

Need Help Retrieving Available Inventory & Gross Requirement per Location

(4) Share

Report

Posted on by CU01100812-0

Hi,

I’m trying to retrieve two key values from Business Central:

Available Inventory (i.e. current stock by location)

Gross Requirement (i.e. qty. on sales order by location)

I can see these values on the Availability by Location page, but I’m unable to pick them correctly through a query or custom API

Can you please guide me on this?

Thanks!

Categories:

Dynamics 365 Business Central

Screenshot 2025-0...

I have the same question (0)

All responses (5)

Answers (0)

Suggested answer

Holly Huffman 6,528 Super User 2025 Season 2 on at

Like (1)

Report
Good morning, afternoon, or evening depending on your location!

To retrieve Available Inventory and Gross Requirement per location in Dynamics 365 Business Central, you can use a combination of queries and custom APIs. Here's how you can approach this:

1. Using Queries in Business Central
You can create a query object to fetch the required data from the relevant tables:

Tables to Use:

Item Ledger Entry: For current stock (Available Inventory).

Sales Line: For Gross Requirement (quantities on sales orders).

Steps:

Create a new query object in AL.

Add Item Ledger Entry and Sales Line as data items.

Use filters to group data by Location Code.

Calculate:

Available Inventory: Sum of Quantity in Item Ledger Entry.

Gross Requirement: Sum of Quantity in Sales Line.
Example AL Code:
query 50100 "Inventory and Requirement Query"
{
     Caption = 'Inventory and Requirement Query';
     DataItem(ItemLedgerEntry; "Item Ledger Entry")
     {
         Column(LocationCode; "Location Code") { }
         Column(AvailableInventory; Sum("Quantity")) { }
     }
     DataItem(SalesLine; "Sales Line")
     {
         DataItemLink = "Location Code" = FIELD("Location Code");
         Column(GrossRequirement; Sum("Quantity")) { }
     }
}

2. Using Custom APIs
If you need to expose this data via an API:

Steps:

Create a new API page in AL.

Use the query created above or directly fetch data from the tables.

Add fields for Available Inventory and Gross Requirement.
Example API Page:
page 50101 "Inventory API"
{
     PageType = API;
     SourceTable = "Item Ledger Entry";
     ApplicationArea = All;
     Layout
     {
         Area(Content)
         {
             Field(LocationCode; "Location Code") { }
             Field(AvailableInventory; Sum("Quantity")) { }
             Field(GrossRequirement; Sum("Quantity")) { }
         }
     }
}

3. Filtering and Optimizing

Use $filter in API calls to retrieve data for specific locations.

Example API Call:
https://api.businesscentral.dynamics.com/v2.0/production/api/v2.0/companies({companyId})/InventoryAPI?$filter=LocationCode eq '90A-NEW'

4. Alternative: Availability by Location Page
If you only need to view the data, the Availability by Location page provides a built-in overview. However, for programmatic access, the above methods are more suitable.

Hope this helps some!

Was this reply helpful? Yes No
Suggested answer

Khushbu Rajvi. 20,234 Super User 2025 Season 2 on at

Like (2)

Report

If this helps:https://community.dynamics.com/forums/thread/details/?threadid=d8dc52d3-8c65-4bf2-bfe6-c496d8641488

https://yzhums.com/54104/

Was this reply helpful? Yes No
Suggested answer

YUN ZHU 95,311 Super User 2025 Season 2 on at

Like (1)

Report

If you need to view it in the API, this requires customization. You can refer to the standard logic on the Availability by Location page to obtain the data.

PS: Remove ability to expose a Microsoft page as a OData endpoint (removal)

https://learn.microsoft.com/en-us/dynamics365/business-central/dev-itpro/upgrade/deprecated-features-platform?wt.mc_id=DX-MVP-5004336

Thanks.

ZHU

Was this reply helpful? Yes No
Suggested answer

Jainam M. Kothari 15,627 Super User 2025 Season 2 on at

Like (1)

Report

May this can help : https://learn.microsoft.com/en-us/dynamics365/business-central/inventory-how-availability-overview

https://learn.microsoft.com/en-us/dynamics365/business-central/reports/report-707

Was this reply helpful? Yes No
Suggested answer

Suresh Kulla 50,241 Super User 2025 Season 2 on at

Like (2)

Report

What you have added to retrieve those values through API, if you can share the code we can suggest, you need to provide us more details. The fields are flowfields and some are calculated fields so you just need to copy over the same logic.

Was this reply helpful? Yes No