SQL and sorting

Roelof

Hi,

Probably this question has been asked many times, but here it is again: One of our clients is running Navision 4.00 in a SQL environment. We upgraded from 3.01 to 4.00. It seems to be that sorting orders are not quiet the same as in Native Navision. I.e 1..20 is in SQL 1,10,20,3..9. Is there any way to change that?

Thanks!
Roelof de Jong.

Savatage

http://www.mibuso.com/forum/viewtopic.p ... t=sql+sort

mestre

I don't believe that you should read the post in the link.

You must search for one property somewhat SQL data Type, I'm sorry but I don't have the Navision in front of me.

Roelof

Well, if I looked at the links, I was afraid of that. I know that you can solve the problem by using leading zeros, but that's not a solution. It would mean that we have to do this 'trick' in many many tables and to rename every single record. An other solution I read would be an additional field and fill that with leading zeros and add that to the sorting keys. Not an easy solution and very very time consuming.

Sure there is no other solution?

Roelof de Jong.

kine

I think no. Your numbering does not follow basic recommendations... if you are using only numbers, you can retype the field (but you need to somehow transfer the data out and in) and change all references... but it is still big job... I recommend you to change the numbering to follow the rules for numbering...

Savatage

mestre wrote:

I don't believe that you should read the post in the link.

:?:

PaddyMullaney

The SQL Data Type mestre was talking about is SQL Variant.

I could not get it to work properly, and when I spoke to MBS they mentioned it might not always be that great.

If you re-number you are best to use a character rather than trailing zeros to create the number.
e.g
a001
a021

instead of

0001
0021


Also the only place in Navision this is a major issues as opposed to just a display issue is when you use a Chart Of Accounts as the Sums in that are based on referenetial rather than absolute references. If you re-number within Navision, Navision can update all related links for you. I have not tested it anywhere else, so you would have to tread a little carefully there. It can take some time, especially in SQL.