Update records without looping : AL Code

Business Central/NAV Developers Forum
1

Hi All

I have written code to update records from code but want to know if there is a better (faster) way rather than looping through each record as below. There may be a simpler code:

var

PurchaseHeader: Record “Purchase Header”;

IntLog: Record “Interaction Log Entry”;

begin

PurchaseHeader.SetRange(“Document Type”, PurchaseHeader.“Document Type”::Order);

PurchaseHeader.SetRange(MY_Status2, PurchaseHeader.MY_Status2::" ");

if PurchaseHeader.FindSet() then begin

repeat

IntLog.SetRange(“Document No.”, PurchaseHeader.“No.”);

if IntLog.FindSet() then

repeat

PurchaseHeader.Validate(MY_Status2, PurchaseHeader.MY_Status2::SentToSupplier);

PurchaseHeader.modify(true);

until IntLog.Next() = 0;

until PurchaseHeader.Next() = 0

end

2

Start with using a Query object to join “Purchase Header” and “Interaction Log Entry”. You don’t need to retrieve all the related “Interaction Log Entry” records. You only need to determine there is at least 1 related to the “Purchase Header”.

Then use the Query results to mark the “Purchase Header” records in another record variable. Then use MODIFYALL to update the “MY_Status2” field on those records.

NOTE: Be aware that MODIFYALL will update each record separately if the TRUE parameter is used, or there are event subscribers.

3

thanks this helps…from what I gather we can use MODIFYALL for a user field but better to use Validate for a system field as MODIFYALL can create data integrity issue. On using a query not sure if it works on SaaS version (BC)

4

Query objects run just fine in SaaS version. I use them all the time. They can help reduce the number of server trips. Which can be a major performance factor.

MODIFYALL has no impact of data integrity if the code is properly designed. it provides a way to update multiple records with a single server trip. That advantage can be lost for the reasons I mention. In those cases, the system reverts to updating each record separately.