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,
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,
