Using Queries Instead of Record Variables
In scenarios where you want to read records from multiple table, 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 C/AL code that is required to perform the operation.
Code Example Using Record Variables
The following C/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
function.
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);
Corresponding Query Implementation
To get the same results with a query instead of record variables, you create a query and add C/AL code to an object to run the query as follows:
In Query Designer, create a query that has the following characteristics:
Include a data item for table 27 Item with columns for the No. and Description fields of the table.
This corresponds to the
Item
record variable in the record variable example.Include a data item for table 32 ItemLedgerEntry with a filter row for the Entry Type field and columns for the Posting Date and Quantity fields.
This corresponds to the
ItemLedgerEntry
record variable in the record variable example.Note
The field of a filter row is not included in the resulting dataset. For more information, see Understanding Query Filters and How to: Set Up Filter Rows in Query Designer.
Link the data items on the No. field of table 27 Item and Item No. field of table 32 ItemLedgerEntry by setting DataItemLink property. Set the DataItemLinkType property to Exclude If No Matches.
This corresponds to the
ItemLedgerEntry.SETRANGE("Item No.",Item."No.");
statement in the record variable example.Set a SUM method on the Quantity column for calculating the total number of items.
This corresponds to the
TotalQty := TotalQty + ItemLedgerEntry.Quantity;
statement in the record variable example.
The following illustration shows the query in Query Designer.
Add the following code to a codeunit that will run the query.
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);
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
function performs the same operations as the OutputData function in the record variable example.
See Also
Tasks
Reference
SETRANGE Function (Query)
OPEN Function (Query)
TOPNUMBEROFROWS Function
Concepts
Queries
Working with Queries in C/AL
Understanding Query Filters