NAV Team: Using C/AL Query Objects Instead of Nested Loops

[Blog bot]

After a bit of a delay, here is the latest Microsoft Dynamics NAV design pattern, brought to you by the NAV Design Patterns team.


Meet the Pattern
This pattern shows how the new query object type introduced in Microsoft Dynamics NAV 2013 allows you to replace costly loops when inspecting data from two or more tables.


Know the Pattern
One of the core operations in a relational database is joining two or more tables. For example, you might need to extract all sales lines in the database together with information regarding the related sales header. This requires joining the Sales Header and Sales Line tables using Sales Header No. as the connecting field.

The join operation has traditionally been done in C/AL by record looping. When Microsoft Dynamics NAV 2013 introduced the query object, it allowed us to produce a data set that is the result of a join operation between two or more tables. This simplifies the problem of finding related records in two tables linked through a foreign key.


Pattern Elements
1. Two or more tables that contain records linked through a foreign key: Table 1, Table 2, Table n.

2. A query object Query X, that joins Table 1, Table 2, etc. based on the connecting key.

3. A processing codeunit that loops through the query records (or any other code-bearing object).


Pattern Steps
1. Run the query on the connected tables.

2. Loop through the records returned by the query.

3. Process the records.

The following diagram illustrates the elements of the pattern.




Use the Pattern
The Bank Acc. Reconciliation Line table (274) and the Bank Account Ledger Entry table (271) are connected through the Bank Account No. field. Identify the matching pairs of records based on having the same remaining amount and transaction date.


Solution Using Nested Loops
The classic C/AL approach is to:

1. Set the necessary filters on the left table, i.e. table 274.

2. Loop through the filtered records.

3. For each record in the filter, find the related records in the right table (table 271) and set the required filters on it.

4. For each pair of records from the left and right table, decide if they are a solution and if so, apply them to each other.

VAR

BankAccRecLine@1005 : Record 274;

BankAccLedgerEntry@1006 : Record 271;

BankAccEntrySetReconNo@1007 : Codeunit 375;



BEGIN

BankAccRecLine.SETFILTER(Difference,'<>%1',0);

BankAccRecLine.SETRANGE(Type,BankAccRecLine.Type::"Bank Account Ledger Entry");

IF BankAccRecLine.FINDSET THEN

REPEAT

BankAccLedgerEntry.SETRANGE("Bank Account No.",BankAccRecLine."Bank Account No.");

BankAccLedgerEntry.SETRANGE(Open,TRUE);

BankAccLedgerEntry.SETRANGE("Statement Status",BankAccLedgerEntry."Statement Status"::Open);

BankAccLedgerEntry.SETFILTER("Remaining Amount",'<>%1',0);

IF BankAccLedgerEntry.FINDSET THEN

REPEAT

IF (BankAccRecLine.Difference = BankAccLedgerEntry."Remaining Amount") AND (BankAccRecLine."Transaction Date" = BankAccLedgerEntry."Posting Date") THEN BankAccEntrySetReconNo.ApplyEntries(BankAccRecLine,BankAccLedgerEntry, Relation::"One-to-One");

UNTIL BankAccLedgerEntry.NEXT = 0;

UNTIL BankAccRecLine.NEXT = 0;

END;


Solution Using a Query
The new query-based approach involves:

1. Define a query that returns the full filtered join of tables 271 and 274.

2. Loop through the records returned by the query.

3. For each query record, decide if it represents a solution and then connect the two table records that formed it through an application.

VAR

BankRecMatchCandidates@1001 : Query 1252;

BankAccEntrySetReconNo@1007 : Codeunit 375;

BEGIN

BankRecMatchCandidates.SETRANGE(Rec_Line_Bank_Account_No,BankAccReconciliation."Bank Account No.");

BankRecMatchCandidates.SETRANGE(Rec_Line_Statement_No,BankAccReconciliation."Statement No.");



IF NOT BankRecMatchCandidates.OPEN THEN

EXIT;



WHILE ...



Read more on NAV Design Patterns Wiki...

Best regards,

Bogdan Sturzoiu, at Microsoft Development Center Copenhagen



Источник: http://feedproxy.google.com/~r/Micro...ted-loops.aspx