Tip #42 | SetCurrentKey on Flowfields


I’ve already posted a few times about the possibilty of doing SETCURRENTKEY on non key (indexed) fields and doing flowfields and CALCSUMS on non SumIndexFields.

Another option that we have is to do a SETCURRENTKEY on a flowfield.

For example:

2015-01-31_21-07-28

This will give the following result:

2015-01-31_21-09-31

And we can check that this is correct:

2015-01-31_21-09-09

Be careful. Mind performance.

Enjoy.

Advertisements
This entry was posted in Tips and Tricks and tagged , , . Bookmark the permalink.

3 Responses to Tip #42 | SetCurrentKey on Flowfields

  1. Pingback: Tip #42 | SetCurrentKey on Flowfields - Mark Brummel - Author of Microsoft Dynamics NAV 2013 Application Design

  2. Xavier Garonnat says:

    I think performance is pretty good because of smart usage of VSIFT : after running and analyzing your codeunit with SQL Profiler, we can see that Nav is using a “SELECT Balance ORDER BY Balance DESC” to retrieve only one record 🙂

    This SQL code is below, I just had to remove all the extra columns from Customer table (just change Company Name).

    SELECT TOP (1)
    “Customer”.”No_” AS “No_”,
    “SUB$Balance”.”Balance$Detailed Cust_ Ledg_ Entry$SUM$Amount” AS “Balance”
    FROM
    “Demo Database NAV (8-0)”.dbo.”CRONUS France S_A_$Customer” AS “Customer” WITH(READUNCOMMITTED)
    OUTER APPLY
    (SELECT TOP (1) SUM(“Balance$Detailed Cust_ Ledg_ Entry”.”SUM$Amount”) AS “Balance$Detailed Cust_ Ledg_ Entry$SUM$Amount”
    FROM “Demo Database NAV (8-0)”.dbo.”CRONUS France S_A_$Detailed Cust_ Ledg_ Entry$VSIFT$5″ AS “Balance$Detailed Cust_ Ledg_ Entry”
    WITH(READUNCOMMITTED,NOEXPAND)
    WHERE (“Balance$Detailed Cust_ Ledg_ Entry”.”Customer No_”=”Customer”.”No_”))
    AS “SUB$Balance”
    ORDER BY “Balance” DESC,”No_” DESC
    OPTION(OPTIMIZE FOR UNKNOWN, FAST 50)

    Like

  3. Mark Brummel says:

    Xavier. Thanks for the testing. I agree that Microsoft did a good job in making it as smart as possible. However the query is a big one. 😉

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s