Using Queries Instead of Record Variables
In scenarios where you want to read records from multiple tables, it can be a good idea to use a query instead of implementing code with record variables. Using a query can improve performance and also simplify the AL code that is required to perform the operation.
Code Example Using Record Variables
The following AL code shows an example of using record variables to retrieve and handle records from two tables. You could potentially use this code to track item movement. The code uses two record variables, Item
and ItemLedgerEntry
, to retrieve the first five records from table 27 Item and table 32 Item Ledger Entry where the Entry Type field equals Sale. The retrieved records are passed to and handled by the OutputData
method.
begin
count := 0;
if Item.FINDSET then
repeat
PrevDate := 0D;
TotalQty := 0;
ItemLedgerEntry.SETCURRENTKEY("Item No.", "Posting Date");
ItemLedgerEntry.SETRANGE("Item No.", Item."No.");
ItemLedgerEntry.SETRANGE("Entry Type",
ItemLedgerEntry."Entry Type"::Sale);
if ItemLedgerEntry.FINDSET then
repeat
if (ItemLedgerEntry."Posting Date" <> PrevDate) and (PrevDate <> 0D) then begin
OutputData(1, Item."No.", Item.Description, PrevDate, -TotalQty);
TotalQty := 0;
count := count + 1;
end;
PrevDate := ItemLedgerEntry."Posting Date";
TotalQty := TotalQty + ItemLedgerEntry.Quantity;
until (ItemLedgerEntry.NEXT = 0) or (count >= 4);
if PrevDate <> 0D then begin
OutputData(1, Item."No.", Item.Description, PrevDate, -TotalQty);
count := count + 1;
end;
until (Item.NEXT = 0) or (count >= 4);
end;
Corresponding Query Implementation
The following AL code represents a query object and additional code that will return the same results as the previous example that uses record variables.
query 50100 "Item Movements Query"
{
QueryType = Normal;
elements
{
// This dataitem corresponds to the `Item` record variable in the record variable example.
dataitem(Item; Item)
{
column(No_; "No.")
{
}
column(Description; Description)
{
}
// This dataitem corresponds to the `ItemLedgerEntry` record variable in the record variable example.
dataitem(Item_Ledger_Entry; "Item Ledger Entry")
{
// The DataItemLink and SqlJoinType settings correspond to the `ItemLedgerEntry.SETRANGE("Item No.",Item."No.");` statement in the record variable example.
DataItemLink = "Item No." = Item."No.";
SqlJoinType = InnerJoin;
filter(Entry_Type; "Entry Type")
{
}
column(Posting_Date; "Posting Date")
{
}
// The SUM corresponds to the `TotalQty := TotalQty + ItemLedgerEntry.Quantity;` statement in the record variable example.
column(Sum_Quantity; Quantity)
{
Method = Sum;
}
}
}
}
}
Add the following code to a codeunit that will run the query.
var
ItemMovements: Query "Item Movements"
begin
ItemMovements.TopNumberOfRows(5);
ItemMovements.SetRange(Entry_Type,ItemMovements.Entry_Type::Sale);
ItemMovements.Open;
while ItemMovements.Read do
OutputData(2, ItemMovements.Item_No,ItemMovements.Description,ItemMovements.Posting_Date,ItemMovements.Sum_Quantity);
end;
The ItemMovements.TOPNUMBEROFROWS(5);
statement will include only the first 5 records in the resulting dataset and corresponds to implementing the count
variable in the record-based code example.
The OutputData
method performs the same operations as the OutputData
method in the record variable example.
See Also
Query Object
Linking and Joining Data Items
Aggregating Data in Query Objects
Filtering Data in Query Objects
SETRANGE Method
OPEN Method
TOPNUMBEROFROWS Method
API Query Type
Developing Extensions
AL Development Environment
피드백
https://aka.ms/ContentUserFeedback
출시 예정: 2024년 내내 콘텐츠에 대한 피드백 메커니즘으로 GitHub 문제를 단계적으로 폐지하고 이를 새로운 피드백 시스템으로 바꿀 예정입니다. 자세한 내용은 다음을 참조하세요.다음에 대한 사용자 의견 제출 및 보기