You’re offline. This is a read only version of the page.
Announcements
Hi everyone,
I need to find a table conatin all the Item quantity and location information, similar to "Item by Location" page. I noticed the Item by location page are using "Item Ledger Entry" table. So if I need to get the same inforamtion displays in "Item by Location" page. I have to do some calculation. Is there any other table contains the same information and separate the records by "Location Code". Whcih would be easier to get item-warehouse-qty relation.
Thanks in advance!!
Hi Nissay
I want to complement this report to get the "Total Available Quantities" however I haven't been able to find the Requested Quantities, Planned order Receipts, Requested receipts, etc.
I would kindly appreciate if you could share with me any suggestions on how to get these fields.
Many thanks
Thank you so much. I wouldn't expect the answer to be so detail! Thank you so much. That would be very helpful.
Hi Ruining,
No problem.
Well to do that you should create a Query and join tables you want (the query bellow has an inner join between three tables : Item Ledger Entry, Item and Location tables). Note that when you use a total method on a Query field, the system groups by all other fields automatically,
Share
Report
All responses (
Answers (
query 50010 "Item Qty. By Location Query"
{
elements
{
dataitem(Item_Ledger_Entry; "Item Ledger Entry")
{
column(ItmLdg_Item_No; "Item No.")
{
}
column(ItmLdg_Location_Code; "Location Code")
{
}
column(ItmLdg_Sum_Remaining_Quantity; "Remaining Quantity")
{
Method = Sum;
}
column(ItmLdg_Open; Open)
{
ColumnFilter = ItmLdg_Open = CONST (true);
}
column(ItmLdg_Positive; Positive)
{
ColumnFilter = ItmLdg_Positive = CONST (true);
}
dataitem(Item; Item)
{
DataItemLink = "No." = Item_Ledger_Entry."Item No.";
SqlJoinType = InnerJoin;
column(Itm_Description; Description)
{
}
column(Itm_Unit_Price; "Unit Price")
{
}
dataitem(Location; Location)
{
DataItemLink = Code = Item_Ledger_Entry."Location Code";
SqlJoinType = InnerJoin;
column(Loc_Address; Address)
{
}
column(Loc_City; City)
{
}
}
}
}
}
}
Then you need to add a table (will be temporary table in page) where you will store data and a page where you view from the table. Note that the table and the page have same fields as the Query except the Positive and the Open fields because there is no need to show them. In fact they are used only for filtering purposes.
table 50011 "Item Qty. By Location Table"
{
DataClassification = ToBeClassified;
fields
{
field(1; LineNo; Integer)
{
DataClassification = ToBeClassified;
}
field(2; "Item No."; Code[20])
{
DataClassification = ToBeClassified;
}
field(3; Description; text[50])
{
DataClassification = ToBeClassified;
}
field(4; "Qty."; Decimal)
{
DataClassification = ToBeClassified;
}
field(5; "Unit Price"; Decimal)
{
DataClassification = ToBeClassified;
}
field(6; "Location Code"; Code[10])
{
DataClassification = ToBeClassified;
}
field(7; Address; Text[50])
{
DataClassification = ToBeClassified;
}
field(8; City; Text[30])
{
DataClassification = ToBeClassified;
}
}
keys
{
key(PK; LineNo)
{
Clustered = true;
}
}
}
page 50012 "Item Qty. By Location Page"
{
PageType = List;
ApplicationArea = All;
UsageCategory = Lists;
SourceTable = "Item Qty. By Location Table";
layout
{
area(Content)
{
repeater(Group)
{
field("Item No."; "Item No.")
{
ApplicationArea = All;
}
field(Description; Description)
{
ApplicationArea = All;
}
field("Qty."; "Qty.")
{
ApplicationArea = All;
}
field("Unit Price"; "Unit Price")
{
ApplicationArea = All;
}
field("Location Code"; "Location Code")
{
ApplicationArea = All;
}
field(Address; Address)
{
Caption = 'Location Address';
ApplicationArea = All;
}
field(City; City)
{
Caption = 'Location City';
ApplicationArea = All;
}
}
}
}
}
Now you have everything ready and you still need the action to view the page with filtered data. Let's suggest you wanna view, on the Item Card page, your item Qty by Location page. Here is the code to add an action to the Item Card page where you filter the query by Item No. and insert filtered data in a temporary record of the table created above. Then you run the page with the "filtered temporary" table.
pageextension 50013 MyExtension extends "Item Card"
{
actions
{
// Add changes to page actions here
addlast(Processing)
{
action("Item Qty. By Loaction Query")
{
ApplicationArea = All;
trigger OnAction()
var
ItemQtyByLoactionRec: Record "Item Qty. By Loaction Table" temporary;
ItemQtyByLoactionQuery: Query "Item Qty. By Loaction Query";
LineNo_Counter: Integer;
begin
LineNo_Counter := 10000;
ItemQtyByLoactionQuery.SetFilter(ItmLdg_Item_No, Rec."No.");
ItemQtyByLoactionQuery.Open();
while ItemQtyByLoactionQuery.READ do begin
ItemQtyByLoactionRec.LineNo := LineNo_Counter;
ItemQtyByLoactionRec."Item No." := ItemQtyByLoactionQuery.ItmLdg_Item_No;
ItemQtyByLoactionRec.Description := ItemQtyByLoactionQuery.Itm_Description;
ItemQtyByLoactionRec."Qty." := ItemQtyByLoactionQuery.ItmLdg_Sum_Remaining_Quantity;
ItemQtyByLoactionRec."Unit Price" := ItemQtyByLoactionQuery.Itm_Unit_Price;
ItemQtyByLoactionRec."Location Code" := ItemQtyByLoactionQuery.ItmLdg_Location_Code;
ItemQtyByLoactionRec.Address := ItemQtyByLoactionQuery.Loc_Address;
ItemQtyByLoactionRec.City := ItemQtyByLoactionQuery.Loc_City;
ItemQtyByLoactionRec.Insert;
LineNo_Counter := LineNo_Counter + 10000;
end;
if ItemQtyByLoactionRec.FindSet then;
if Page.RUNMODAL(50012, ItemQtyByLoactionRec) = Action::LookupOK then;
end;
}
}
}
}
--
Don't hesitate If you need more details or explanations.
-------------
Please verify my answer if you find my answer helpful. Doing so you'll show other community members that there was found a solution and you credit my help.
-------------
Best Regards,
The Query Object is exactly what I am looking for. However, this is the first time to use the query object. Could you kindly provide some sample code about that? I am not quite sure how to do that. What I suppose to do is query out "Item Ledger Entry" and "group by" the item No. and location code. Then I just joint the item to have the rest of Item Info. Because I want to show the item qty for one location in one record. Could you provide some sample or you may have a better idea to do that. Thank you so much.
By the way, all the information about the Item Quantities - by location are stored in the Item Ledger Entry table. Every time you post a change to your inventory quantity (receipt or shipment) a record is added to the Item Ledger Entry table and depending of if it is a positive or negative adjustment, the quantity could be >0 (Inbound documents) or <0 (outbound documents). Therefore, the table Item Ledger Entry holds what you need.
Best Regards,
Hi Ruining,
Items available quantities could be calculated from the Item Ledger Entry (Filter by Positive field and Open field where you can SUM the "Remaining Quantity" field by Location). I suggest you to use a Query Object for that (it's really much easier).
In fact it depends on your needs :
1) You wanna store info on a table (you can create another table where you add info you like from both Item Ledger Entry and Location tables).
2) You wanna just get info on real time like displaying data on a page (example Items by location) --> (for this you should get the Total Available Quantity that excludes the Current Requested Quantities: Quantities on Outbound documents that are not posted yet).
--
Don't hesitate If you need more details or explanations.
-------------
Please verify my answer if you find my answer helpful. Doing so you'll show other community members that there was found a solution and you credit my help.
-------------
Best Regards,
Have a look at the 'bin content' page, it shows this data based on the warehouse entries.
Maybe Warehouse Entry table could help you...
#1
André Arnaud de Cal...
294,217
Super User 2025 Season 1
#2
Martin Dráb
232,978
Most Valuable Professional
#3
nmaenpaa
101,158
Moderator
