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:
This will give the following result:
And we can check that this is correct:
Be careful. Mind performance.
Enjoy.
Marije Brummel Blog | Business Central, Azure and more... 
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)
LikeLike