Sorting Nav Dynamics 2009r2 on Sql 2008St

ngunnarsson

Hi
I have a sorting problem in Nav Dynamics 2009 r2 on Sql 2008St

We recently upgraded from nav4 on c-side to nav2009r2 running on sql2008

When looking at the item list in the old system sorting was 1, 2, 3, 4, 5, 6, 7, 8, 9, 10...

when looking at the item list in the new system sorting is 1, 10, 2...

what can I do to fix this ??
please advise

David_Singleton

This should explain it

http://wiki.dynamicsbook.com/index.php? ... NAV_vs_SQL

David_Singleton

By the way, resolving sorting issues is one of the important steps of a Native to SQL upgrade. Your partner should have addressed this when they did the upgrade, so it might be good to "remind" them of this and see what they have to say.

Marije_Brummel

I hope it's not the steps of one of your MRP runs.

I had a case where

10
40
80
100
120

became

10
100
120
40
80

8)

Good thing we found out during testing.

There are different ways of solving this and the right way depens on where in the application you have the issue.

ngunnarsson

I was not able to catch the problem during testing and our 'partner' does not have a solution.

What are my options??

the issue is mainly in the item cart list view.

Marije_Brummel

The best option is to move to a system with an alphanumeric character in front of the numeric character.

If that is not possible you can do two things

1. Make the Itemlist based on temporary data. This will sort as Native

2. Convert the SQL varchar datatype to integer or variant. This is dangerous!

ngunnarsson

is there any kind of sorting that can be done on the SQL side?

David_Singleton

ngunnarsson wrote:

is there any kind of sorting that can be done on the SQL side?

What did your partner say? Did you ask them why they didn't address this as apart of the upgrade?

davmac1

Suggest you add an extra field.
If you can guarantee the item is always numeric, you can make it integer, otherwise either use C/AL to adjust right or zero fill to the left.
Write a report program to populate the new field with the item number.
Add code to the insert trigger to populate the new field.
Make the new field a key.
Change item list form sourcetableview to use the new key for the sort order.