Hi, I am an abs(begginer) in Navision, so please don’t laugh. I am trying to sort “GL Account” table by “No.” and “Budget” table by “Code”, where it should be sorted like text. For example: 1000 11 1100 12 … and not 11 12 1000 1100 … So, I changed table 15’s primary key to: “accountNo, No.” (or just “accountNo”), where “accountNo” is a text field, copy of the field “No.”. It worked fine with GL Account table (15), but then i had problems with generating the Budget table (367), because I got value 1000 in the field “Code” (taken from field GLAcc.“No.”). What am I missing? Thank you, Zoran Stalevski
Zoran, make a secondary-key “AccountNo.” instead of changing the primary-key. You can sort on a secondary-key whenever necessary. Reijer.
Hi! Well, I think the reason for your problem is the Datatype “Code” of your primary-key-fields: Code-Fields a sorted by “value”, same as integer, so the order would be 11 12 1000 1100 … If you want an alphabetical-order, you’ll need Text-Fields, so the sorting would be 1000 11 1100 12 … But by the way: I do absolutely not recommend to change primary-key-fiels in navison! This causes a lot of trouble within the whole system and makes updates more difficult! Regards, Jörg Joerg A. Stryk Apollo-Optik, IT/ERP
Thank you, Reijer and Joerg Now my primary key in “GL Account” table (15) is “No.” (like it was before), I added “accountNo” as secondary key, and it works well in this table (I have the appropriate sorting method). But, how can I now sort the “Budget” table (367) in the same manner (like text fields). If I do the same in this table (add text field “codeToText” of type Text and make it secondary key), I don’t get the results I expect (the table remains sorted by the field “Code” of type Code, and not by my new column “codeToText” of type text). Should I somewhere (and where) call SETCURRENTKEY(codeToText) function, or similar one? [programatically change the keys?]. Also, I metion here that when user “drills down” in the “Code” column, the new opened “GL Account” table is again sorted by it’s primary key “No.”, and I have to solve that too. To be more specific: I want the columns showing GL Account entries (numbers) to be sorted like text EVERYWHERE in the program. Never like numbers. That means: “… 1000; 11; 1100; 12; …” is OK. That’s why I thought that changing “GL Account”'s primary key would be a good idea (fast and good solution). Sorry to bother you again with this problem. Actually, I have great experience in development, databases and many tehnologies, but my first steps in Navision are confusing and slow. Navision is resetting my prior knowledge and asks for another logic. Is it personal :)? Ha, ha. Thank you again, Zoran Stalevski GoodBytes
In that case, if you want to sort it like text everywhere in the database, it might be an option to change the codes so Navision will see them as text. E.g. place an L after each “G/L Account No.”. PS: Chances are big that you love working with Navision soon.
Hi again, (this takes too long, I am sorry and thank you for you patience) Thank you Reijer for your quick answer. You see, I have to use numbers only in the column “No.”, but they have to be sorted as text (Macedonian specific rules, I think). I think that I only need to know: how to define sorting of “Budget” table (367) by another column. It does not sort any other way but by using “GL Account” table’s (table 15) primary key (which is field “No.”). Even if I set another key through Sort button, the table still stays sorted by “GL Account” table’s primary key. So again: how to define sorting of “Budget” table (367) by another column. Thank you, Zoran Stalevski GoodBytes
I am going back a fair way now, and I may be completely wrong, but… I vaigly remember that Sweden had a similar problem to this when they first released Navision (character version 93 or 94), and they did something to allow this type of sorting. Maybe someone from Sweden that remembers the DOS version could comment. Or at least tell me that I am wrong [:o] PS maybe you could convert to SQL _________________________ David Singleton Navision Consultant since 1991 dmks22@home.com___________
As a new user you should be warrned to NEVER change a primary key in a standard Navision table. It can have unexpected results! Always create secondary keys insted. Bill Benefiel Manager of Information Systems Overhead Door Company billb@ohdindy.com (317) 842-7444 ext 117
Hi and thank you for your help. Let me please specify my question again: How can I sort the Budget table in other order then the given one (by GL Account’s primary key). Thank you again, Zoran Stalevski GoodBytes
Well, it depends on what you are doing. If you are simply viewing the table then you can use the sort button to select which key to view your data by. If you are writting a report, you can either select the sort implicitly by changing the DataItemTableView property or if left undefined you can select the view at run-time. Same goes for Dataports. Forms are similar. In codeunits, you have to use the SETCURRENTKEY command. This command will return an error if you specify a key that does not exist. Bill Benefiel Manager of Information Systems Overhead Door Company billb@ohdindy.com (317) 842-7444 ext 117
quote:
Originally posted by David Singleton: I am going back a fair way now, and I may be completely wrong, but… I vaigly remember that Sweden had a similar problem to this when they first released Navision (character version 93 or 94), and they did something to allow this type of sorting. Maybe someone from Sweden that remembers the DOS version could comment. Or at least tell me that I am wrong [:o]
In the localization of Finacial/Atain for the swedish market have our NTR change No. field in G/L Account from CODE to TEXT. (And of course in some more object). So we still have this modification 
Maby from the old DOS version. Per.Bay@navigera.com Product Manager www.navigera.com Edited by - per bay on 2001 Nov 20 13:56:52
Zoran, To sort Table 367 (Dimension Code Buffer) you will have to add some code to the form that uses this table (e.g. Analysis by Dimension). But be careful that your total accounts still make sense.
**FindRec(DimOption : 'G/L Account,Period,...**
CASE DimOption OF
DimOption::"G/L Account" :
BEGIN
// Modification start
GLAcc.SETCURRENTKEY(CodeToText);
// Modification end
GLAcc."No." := DimCodeBuf.Code;
**NextRec(DimOption : 'G/L Account,Period,...**
CASE DimOption OF
DimOption::"G/L Account" :
BEGIN
// Modification start
GLAcc.SETCURRENTKEY(CodeToText);
GLAcc.CodeAsText := DimCodeBuf.Code;
// Modification end
GLAcc."No." := DimCodeBuf.Code;
IF GLAccFilter <> '' THEN
Regards, Reijer
Hi, Thank you Reijer (and others, of course), for you answer. I will try this tomorrow and feedback you. Zoran Stalevski GoodBytes
Hi, This was the right solution, Reijer. Thank you very much and thanks for all of you for your help. I think that I already like Navision, Reijer. 
Zoran Stalevski GoodBytes