Code Field Sorting WEIRD

markborges

Hello friends,

I have a weird sorting situation in NAV NATIVE Database format.

In Purchase Header table, when sorting by Primary Key (Document Type, No.), I have the following Sorted items:

Blanket Order, BPO06-00271
Blanket Order, BPO06-00272
Blanket Order, BPO06-00273
Blanket Order, BP07-00001
Blanket Order, BP07-00002
Blanket Order, BP07-00003

How come "BP07" comes after "BPO06" ??? ](*,) :-k ](*,) :-k

See that it's not descending, otherwise it would show 00003, 00002, 00001 and so on...

Does anybody know why??? Has anybody ever faced it?

Thanks a lot and best regards,

Savatage

the letter O is less than the # zero

matttrax

It shouldn't be, though, should it?

http://www.asciitable.com/

Letter O is 79, Number 0 is 48. I always thought numbers came before letters in any sort.

Savatage

Sql & Native might sort this differently

markborges

Really, I don't get Navision Database sometimes.

I've never ever seen O being less than 0 in my whole life.... There's always a first time...

](*,) ](*,) ](*,) ](*,) ](*,) ](*,) ](*,) ](*,) ](*,)

Even though that was something that I concluded before, thanks for the info, Savatage.

Best regards,

Belias

I don't remember where it's documented (i'm sure i've read it somewhere), but nav native sorting puts letters before numbers, and it's just the way it works.
sql sorting orders numbers before letters, instead.
p.s.: if you are on sql, but you're using a temporary table, the sorting is the "native way" (but i'm wondering what will happen to this behaviour when the fdb will get "fired") :-k

Sog

Belias wrote:

p.s.: if you are on sql, but you're using a temporary table, the sorting is the "native way" (but i'm wondering what will happen to this behaviour when the fdb will get "fired") :-k

An excellent question for the NAV techdays

dmccrae

Take a look at your fin.stx file, you will find a sorttable entry similar to this:

// sorttable
00043-00110-200-1: " ",a …ƒÆ¦Aµ·¶Ç,bB,c‡C€,dÐDÑ,e‚Šˆ‰EÔÒÓ,fF,gG,hH,i¡Œ‹IÖÞ×Ø,\
00043-00111-200-1: jJ,kK,lL,mM,n¤N¥,o¢•“ä§Oàãâå,pP,qQ,rR,sáS,tçTè,u£—–Uéëê,vV,wW,\
00043-00112-200-1: xX,y영Yíš,zZ,‘„’Ž,›”™,†,0,1,2,3,4,5,6,7,8,9,<60>0>,<1>,<2>,<3>,\
00043-00113-200-1: <4>,<5>,<6>,<7>,<8>,<9>,<10>,<11>,<12>,<13>,<14>,<15>,<16>,<17>,\
00043-00114-200-1: <18>,<19>,<20>,<21>,<22>,<23>,<24>,<25>,<26>,<27>,<28>,<29>,<30>,\
00043-00115-200-1: <31>,!,"""",#,$,%,&,',(,),*,+,",",-,.,/,:,";",<,=,>,?,<64>,[,\,],^,\
00043-00116-200-1: _,`,"{",|,"}",~,<127>,œ,ž,Ÿ,¨,©,ª,¬,«,­,®,¯,°,±,²,³,´,¸,¹,º,»,¼,\
00043-00117-200-1: ½,¾,¿,À,Á,,Ã,Ä,Å,È,É,Ê,Ë,Ì,Í,Î,Ï,Õ,Ù,Ú,Û,Ü,Ý,ß,æ,î,ï,ð,ñ,ò,ó,ô,\
00043-00118-200-1: õ,ö,÷,ø,ù,ú,û,ý,ü,þ,<255>

This determines the ordering for the Native database, and you can see from mine that letter O is placed before digit 0, I expect this is the same for you. Now, why this was chosen historically is unknown to me too, but it explains what is going on. SQL will sort based on the database collation instead, which is a more expected behavior.

Regarding the Temporary table question; even without the native database Temporary tables continue to work as normal because they use a separate indexing implementation to that used by the .fdb itself - they sit on top of a generic client-side indexer which also takes care of other indexing tasks such as virtual tables, the help file topic lookups, STX/ETX lookups and so on.