Performance

SETAUTOCALCFIELDS Function – in Nav 2015

Sets the FlowFields that you specify to be automatically calculated when the record is retrieved from the database.

[OK :=] Record.SETAUTOCALCFIELDS([Field1,Field2, …])

To remove the automatic calculation setting on a field that you previously set with the SETAUTOCALCFIELDS function, you must call SETAUTOCALCFIELDS without parameters.

If you omit the optional return value and if the record cannot be set to automatically calculate, then a run-time error occurs.

If you include a return value, then you must handle any errors.

You can improve performance by using the SETAUTOCALCFIELDS function before looping through records with FlowFields instead of calling the CALCFIELDS function on each record in the loop.

The automatic calculation setting applies to the fields that you specify until the Record parameter goes out of scope or until you call SETAUTOCALCFIELDS without any parameters.

The fields that you specify in the Field parameters are added to any fields that you previously set with the SETAUTOCALCFIELDS function. Previously set fields are not overwritten when you call SETAUTOCALCFIELDS with different parameters. To clear the list of fields that are automatically calculated, call the SETAUTOCALCFIELDS function without parameters.

If you assign a record to another record variable, then the automatic calculation setting on FlowFields is not transferred with the record.

If you copy a record by using the COPY Function (Record), then the automatic calculation setting on FlowFields is copied with the record.

The Rec and xRec system variables are set to always automatically calculate FlowFields. If you call the NEXT function on Rec or xRec, then FlowFields in the record are automatically calculated.

Below example show how to use the CALCFIELDS function.

// Using CALCFIELDS

IF (Customer.FIND(’-’)=true) THEN

REPEAT

Customer.CALCFIELDS(Balance,”Net Change”);

… // your other codes for processing.

UNTIL (Customer.NEXT=0)

Below example show how you can use the SETAUTOCALCFIELDS function instead of the CALCFIELDS function to improve performance.

// Using SETAUTOCALCFIELDS

Customer.SETAUTOCALCFIELDS(Balance,”Net Change”);

IF (Customer.FIND(’-’)=true) THEN

REPEAT

// Customer.Balance and Customer.”Net Change” have been auto calculated.

… //your rest of the code for processing.

UNTIL (Customer.NEXT=0)

The following example shows how to add fields to the list of automatically calculated FlowFields and how to clear the list of automatically calculated FlowFields

Customer.SETAUTOCALCFIELDS(Balance);

Customer.SETAUTOCALCFIELDS(“Net Change”);

// Above lines are equivalent to the following:

Customer.SETAUTOCALCFIELDS(Balance,”Net Change”);

// To clear the list of automatically calculated FlowFields so that no fields are calculated automatically, use the following code.

Customer.SETAUTOCALCFIELDS();

It is a common task to retrieve data and request calculation of associated FlowFields. The following example traverses customer records, calculates the balance, and marks the customer as blocked if the customer exceeds the maximum credit limit.

Note the below Customer record and associated fields are imaginary.

IF Customer.FINDSET() THEN

REPEAT

Customer.CALCFIELDS(Customer.Balance)

IF (Customer.Balance > MaxCreditLimit) THEN BEGIN

Customer.Blocked = True;

Customer.MODIFY();

END  ELSE IF (Customer.Balance > LargeCredit) THEN BEGIN

Customer.Caution = True;

Customer.MODIFY();

END;

UNTIL Customer.NEXT = 0;

In Microsoft Dynamics NAV 2015, you can do this much faster. First, we set a filter on the customer.

This could also be done in Microsoft Dynamics NAV 2009, but behind the scenes the same code as mentioned earlier would be executed.

In Microsoft Dynamics NAV 2015, setting a filter on a record is translated into a single SQL statement.

Customer.SETFILTER(Customer.Balance,’>%1’, LargeCredit);

IF Customer.FINDSET() THEN REPEAT

Customer.CALCFIELDS(Customer.Balance)

IF (Customer.Balance > MaxCreditLimit) THEN BEGIN

Customer.Blocked = True;

Customer.MODIFY();

END   ELSE IF (Customer.Balance > LargeCredit) THEN BEGIN

Customer.Caution = True;

Customer.MODIFY();

END;

UNTIL Customer.NEXT = 0;

In the previous example, an extra call to CALCFIELDS still must be issued for the code to be able to check the value of Customer.Balance.

In Microsoft Dynamics NAV 2015, you can optimize this further by using the new SETAUTOCALCFIELDS function.

Customer.SETFILTER(Customer.Balance,’>%1’, LargeCredit);

Customer.SETAUTOCALCFIELDS(Customer.Balance)

IF Customer.FINDSET() THEN REPEAT

IF (Customer.Balance > MaxCreditLimit) THEN BEGIN

Customer.Blocked = True;

Customer.MODIFY();

END   ELSE IF (Customer.Balance > LargeCredit) THEN BEGIN

Customer.Caution = True;

Customer.MODIFY();

END;

UNTIL Customer.NEXT = 0;