Sorting problem in NAV running on SQL

marvinq · 2010-01-05

Hi,

I have a sorting problem running NAV on SQL.

I have tried almost anything now on Windows sorting or SQL-sorting. I cannot get the sorting of g/l accounts to get right.

It sorts like this..
1
10
2

I've read that some advices others to change the type, and others to write 0001, 0010 and 0002 to get the correkt sorting. But can this really be true. There must exist some collation which sorts the data as I want e.g. 1,2,10.

Please help me. What to do?

Thanks in advance

Belias · 2010-01-05

that's the way sql works, use codes as suggested or switch to native.
You can also create a new field with the right numbering (001,002,010) and order the table by this code. then show your real field

Marije_Brummel · 2010-01-05

There are some tricks for this, but most useless because this is a core table.

You can change the SQL datatype to integer or variant, but that means changing all tablerelations which is undoable with the G/L.

The best solution is to enable the SourceTableTemporary property and create a function InitGL. A temp table sorts like native.

The GL Account table is small. It will work.

kapamarou · 2010-01-05

There used to be an option in earlier versions (4 SP3) on the field properties called "SQL Data Type". :whistle:

Edit:

Sorry... It's still there but the field is text... :oops:

Belias · 2010-01-05

Mark Brummel wrote:

The best solution is to enable the SourceTableTemporary property and create a function InitGL. A temp table sorts like native.

that's cool, i didn't remember it!! =D>

NaviDeveloper_NL · 2010-03-16

Mark Brummel wrote:

You can change the SQL datatype to integer or variant, but that means changing all tablerelations which is undoable with the G/L.

What do you mean with "undoable"?

I prefer a simple solution, changing the SQL Data Type to Integer works good for me. Before I was able to do that I had to remove a few padding zero's.
This is what the Upgrade Toolkit advises as a possible solution.

After doing this one line was sorted different, but this was not problem for the customer.

Changing the SQL Data type has nothing to do with table relations as table relations are on NAV level and only the data type on SQL level is changed.

tomguan · 2010-05-17

Hi,

I have a table primary key is No. and defined as code, SQL Data type=Integer. This worked for couple of years until last month our customer reported an issue. When they used NAV search from the list for No. field. If they choose
Match......Beginning of Field
Match Case=True
Use the Best Key=False
Find As you Type=True

And when they typed in the 4th character in Find What, the Navision would crash. I tested that and eventually found that if I removed SQL Data Type=Integer, this issue would be solved. Does any one know this issue and if Microsoft has hotfix for that?

I tested by using Cronus G/L Account table, this would not happen, so I assumed that only when a table with a lot of records will have this issue.

Tom

Belias · 2010-05-18

try to remove the Findasyoutype flag, it can cause performance issue (it shouldn't cause the crash, but it's worth a try to remove it!)

Sorting problem in NAV running on SQL

Comments

Categories