NAV 2013 | SETAUTOCALCFIELDS

In NAV 2013 we have a new command SETAUTOCALCFIELDS.

What does this command do.

It is used in C/AL like this:

Cust.SETAUTOCALCFIELDS(Balance)

IF Cust.FINDSET THEN REPEAT

Some Code

UNTIL Cust.NEXT = 0;

This leads to this query in SQL Server:

SELECT

ISNULL(“Customer”.”timestamp”,@0) AS ISNULL(“Customer”.”No_”,@1) AS “No_”,…,

ISNULL(“SUB$Balance”.”Balance$Detailed Cust_ Ledg_ Entry$SUM$Amount”,@77) AS “Balance”

FROM “CRONUS Nederland BV$Customer” AS “Customer” WITH(READUNCOMMITTED)

OUTER APPLY (SELECT TOP (1) ISNULL(SUM(“Balance$Detailed Cust_ Ledg_ Entry”.”Amount”),@76) AS “Balance$Detailed Cust_ Ledg_ Entry$SUM$Amount”

FROM “CRONUS Nederland BV$Detailed Cust_ Ledg_ Entry” AS “Balance$Detailed Cust_ Ledg_ Entry” WITH(READUNCOMMITTED)

WHERE (“Balance$Detailed Cust_ Ledg_ Entry”.”Customer No_”=”Customer”.”No_”)) AS “SUB$Balance”

Hence, a single SQL Statement that joins two tables.

This dramatically changes the teaching that has been done about T-SQL generation in NAV from the last few years.

Also, notice that the SIFT view is not used.

BE AWARE

Like a filter,  SETAUTOCALCFIELDS keeps being active on a variable until it is reset.

To reset this command Cust.SETAUTOCALCFIELDS should be used. So without any fields, just like resetting a filter with SETRANGE.

Thanks to Luc van Vugt to bring this to my attention.