Mark Brummel - Author of Microsoft Dynamics NAV 2009 Application Design

Mark Brummel - Author of Microsoft Dynamics NAV 2009 Application Design

Recent Posts

Tags

News

  • Published : Microsoft Dynamics NAV 2009 Application Design

Community

Email Notifications

Other Blogs

Archives

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.

 

 

Posted: 2012-12-5 1:02 by Marq | with 4 comment(s)
Bookmark and Share

Comments

Mark Brummel - Microsoft Dynamics NAV Blog said:

In NAV 2013 we have a new command SETAUTOCALCFIELDS. What does this command do. It is used in C/AL like

# December 5, 2012 2:26 AM

Mark Brummel - Microsoft Dynamics NAV Blog said:

In NAV 2013 we have a new command SETAUTOCALCFIELDS. What does this command do. It is used in C/AL like

# December 5, 2012 2:38 AM

Peter said:

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.

# December 5, 2012 7:55 AM

Luc van Vugt said:

@Peter:

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.

@Marq

Thanx for the credits. ;-)

# December 5, 2012 4:00 PM
Leave a Comment

(required) 

(required) 

(optional)

(required)