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
0
Comments
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
"Never memorize what you can easily find in a book".....Or Mibuso
My Blog
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.
Edit:
Sorry... It's still there but the field is text... :oops:
"Never memorize what you can easily find in a book".....Or Mibuso
My Blog
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.
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
"Never memorize what you can easily find in a book".....Or Mibuso
My Blog