Probably most of the partners have already noticed this and changed the C/AL code accordingly in order to let their customized application works properly.
In ALL versions of NAV up to now (NAV 2009 R2) the SORTING of Temporary Tables / Temporary record variables has always been determined using a C/AL sorting “collation” type. Even in SQL Server environment, where it is possible to select the proper collation (File > Database > Alter > Collation) for the database, Temporary Tables / Temporary record variables use always C/AL sorting order. This is very important to know since it may lead to a wrong business processing of data when using e.g. record looping (REPEAT .. UNTIL cycles).
This behavior has been maintained like this for 2 main reasons:
1) Temporary Tables / Temporary record variables get created independent on the server, so they cannot read the collation and sorting from SQL Server and use this.
2) If MS NAV Development team change the way that Temporary Tables / Temporary record variables are sorted, then this might break compatibility with a lot of existing solutions that would suddenly sort differently.
Below you will find a simple demonstration of this assumption. For completeness, I have made the example both for Classic Client (Forms) and RoleTailored Client (Pages) but the behavior is the same.
1. Install a CRONUS database (W1 or whatever localized version)
2. Add these records in Table 6 “Customer Price Group”
|
Code |
Description |
| 1 | Code 1 |
| 2 | Code 2 |
| 2.12 | Code 2.12 |
| 200 | Code 200 |
| 3 | Code 3 |
| 300 | Code 300 |
| C3 | Code C3 |
3. Add and enable one key to table 18 Customer with “Customer Price Group” field (if this is not already active). Save and compile (CTRL+S) table 18 Customer.
4. Add those codes to Customers.
|
No. |
Name | Customer Price Group |
| 01121212 | Spotsmeyer’s Furnishings | 1 |
| 01445544 | Progressive Home Furnishings | 2 |
| 01454545 | New Concepts Furniture | 2.12 |
| 01905893 | Candoxy Canada Inc. | 200 |
| 01905899 | Elkhorn Airport | 3 |
| 01905902 | London Candoxy Storage Campus | 300 |
| 10000 | Cannon Group SpA | C3 |
5. Create a New Codeunit, e.g. Codeunit 50000 “Test TempTable” with these global variables and code snippet:
Global variables
|
Name |
DataType | Subtype | Length |
| CustTemp | Record | Customer | |
| Cust | Record | Customer |
Set the Temporary property of the CustTemp variable to Yes.
C/AL code snippet:
// Copyright © Microsoft Corporation. All Rights Reserved.
// This code released under the terms of the
// Microsoft Public License (MS-PL, http://opensource.org/licenses/ms-pl.html.)
CustTemp.DELETEALL;
Cust.RESET; // Populate CustTemp Table
Cust.SETCURRENTKEY(“Customer Price Group”);
Cust.SETFILTER(“Customer Price Group”,'<>%1′,”);
IF Cust.FINDSET THEN REPEAT
CustTemp.INIT;
CustTemp.TRANSFERFIELDS(Cust);
CustTemp.Name := ‘TEMP ‘ + COPYSTR(CustTemp.Name,1,25);
CustTemp.INSERT;
UNTIL Cust.NEXT = 0;
Cust.RESET; // Run Page/Form – Normal table
Cust.SETCURRENTKEY(“Customer Price Group”);
Cust.SETFILTER(“Customer Price Group”,'<>%1′,”);
Cust.FIND(‘-‘);
IF ISSERVICETIER THEN
PAGE.RUN(PAGE::”Customer List”,Cust)
ELSE
FORM.RUN(FORM::”Customer List”,Cust);
CustTemp.RESET; // Run Page/Form – Temporary table
CustTemp.SETCURRENTKEY(“Customer Price Group”);
CustTemp.FIND(‘-‘);
IF ISSERVICETIER THEN
PAGE.RUN(PAGE::”Customer List”,CustTemp)
ELSE
FORM.RUN(FORM::”Customer List”,CustTemp);
6. Save and compile (CTRL+S) the codeunit.
7. Run the Codeunit. (You can also add this Codeunit as an action in RTC. The results within Forms and Pages is the same).
8. Now compare the 2 Forms opened (they are one up in front the other) and their different sort order (show column “Customer Price Group” to clearly see the difference in sorting order):
|
Temporary (C/AL type dependent) |
Normal (SQL Server collation dependent) |
| 1 | 1 |
| 2 | 2 |
| 3 | 2.12 |
| 200 | 200 |
| 300 | 3 |
| 2.12 | 300 |
| C3 | C3 |
Since from the next version there will not be any support for Native database (where this C/AL sorting coming from) there have been speculations about changing this behavior in order to have the SORTING for Temporary Tables / Temporary record variables equal to the SQL Sorting (in short, for SQL Server based environments to have the same sorting order for normal tables and temporary tables).
If you think this is a remarkable feature that could / should to be changed in a future version, please log your request into MSCONNECT:
https://connect.microsoft.com/dynamics
These postings are provided “AS IS” with no warranties and confer no rights. You assume all risk for your use.
Best Regards,
Duilio Tacconi (dtacconi)
Microsoft Dynamics Italy
Microsoft Customer Service and Support (CSS) EMEA