In NAV 2013 we have a new command SETAUTOCALCFIELDS.
What does this command do.
It is used in C/AL like this:
IF Cust.FINDSET THEN REPEAT
UNTIL Cust.NEXT = 0;
This leads to this query in SQL Server:
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.
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.
Thanx for this KB link. I had been investigating SETAUTOCALCFIELD and in my experiment CALCFIELDS was always faster on my onebox installatioon of NAV 2013.
So I am going to run my tests again against this new build.
Thanx for the credits. ;-)
Please notice, that the new function has some performance issues in the NAV 2013 RTM build: mbs.microsoft.com/.../KBDisplay.aspx
Let's hope they have changed the T-SQL statement to use the the sift index.