Van Vugt: How-to: Sort Columns - SETASCENDING

[Blog bot]

Since NAV 2013 we are more and more getting the full potential out SQL Server.

In NAV 2009 (and before) it was, for example, unimaginable to setup "your own" sorting for your data retrieval as SETCURRENTKEY only allowed you to use the keys defined on the table you were going to query. Or not possible for users to sort their lists by any column, like they had been used to in, for example, Excel long time.

So now we get, more and more, the power of SQL directly accessible.

Now with NAV 2016 another step is made with SETASCENDING even though it only seems to be applicable on data processed by code (didn't get it working on data displayed in a page, temporary or not).

The next code example shows what that we can sort on individual columns (in this case Currency Code and Name).

OnRun()
ShowSetAscending(TRUE);
ShowSetAscending(FALSE)

LOCAL ShowSetAscending(IsAscending: Boolean)
MESSAGE('Ascending = %1',FORMAT(IsAscending));
WITH Customer DOBEGIN
SETCURRENTKEY("Currency Code",Name,Contact);
SETFILTER("Currency Code",'SEK|USD|ZAR');
SETASCENDING("Currency Code",IsAscending);
SETASCENDING(Name,NOT IsAscending);
IF FINDSET THEN
REPEAT
MESSAGE(
'Customer %1 %2 %3',
"No.",
Name,
"Currency Code"
UNTIL NEXT =0;
END






Note
The field SETASCENDING is applied to, should be part of the sorting as defined by the current active SETCURRENTKEY. If not a runtime error will be thrown:

Cannot call SetAscending on field <field name> because it is not part of the current sorting.



Читать дальше