Вернуться   AXForum > Microsoft Dynamics CRM > Dynamics CRM: Blogs
Забыли пароль?
Зарегистрироваться Правила Справка Пользователи Сообщения за день Поиск Все разделы прочитаны

Опции темы Поиск в этой теме Опции просмотра
Старый 09.05.2014, 18:26   #1  
Blog bot is offline
Blog bot
22,216 / 771 (70) +++++++
Регистрация: 28.10.2006
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.


BankAccRecLine@1005 : Record 274;

BankAccLedgerEntry@1006 : Record 271;

BankAccEntrySetReconNo@1007 : Codeunit 375;



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



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


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

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

IF BankAccLedgerEntry.FINDSET THEN


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;


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.


BankRecMatchCandidates@1001 : Query 1252;

BankAccEntrySetReconNo@1007 : Codeunit 375;


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



Read more on NAV Design Patterns Wiki...

Best regards,

Bogdan Sturzoiu, at Microsoft Development Center Copenhagen

Расскажите о новых и интересных блогах по Microsoft Dynamics, напишите личное сообщение администратору.

Опции темы Поиск в этой теме
Поиск в этой теме:

Расширенный поиск
Опции просмотра

Ваши права в разделе
Вы не можете создавать новые темы
Вы не можете отвечать в темах
Вы не можете прикреплять вложения
Вы не можете редактировать свои сообщения

BB коды Вкл.
Смайлы Вкл.
[IMG] код Вкл.
HTML код Выкл.
Быстрый переход

Часовой пояс GMT +3, время: 10:47.
Powered by vBulletin® v3.8.5. Перевод: zCarot
Контактная информация, Реклама.