Sorting on code

anand_sri12
anand_sri12 Member Posts: 71
edited 2007-06-25 in NAV/Navision Classic Client
I want to sort my report based on a key which is a code data type

but the report didnt sort the report as desired

the code value is in sequence of
e.x. - 1,2,3,5,100,101,102,199,213

the report shows like this

code
1
100
101
102
199
2
213
and so

what to do to sort the report correctly
With thanks

Anand Kumar
Navision Technical Consultant

Answers

  • MTC
    MTC Member Posts: 159
    It looks to me as if you are storing your code in a Text field and not a Code field.
  • kriki
    kriki Member, Moderator Posts: 9,121
    MTC wrote:
    It looks to me as if you are storing your code in a Text field and not a Code field.
    Or you are using SQL.
    There is a property "SQLDataType" that you can change to integer so the code-field is sorted as an integer. BUT you CANNOT put codes anymore that are not integers. And everywhere you use that field you should change the SQLDataType.
    Maybe better to read the data, put them in a temptable, put some '0' before them so each code has the same size (001,002,100,...) and then read the temptable to print the records.
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!

  • anand_sri12
    anand_sri12 Member Posts: 71
    kriki wrote:
    MTC wrote:
    Maybe better to read the data, put them in a temptable, put some '0' before them so each code has the same size (001,002,100,...) and then read the temptable to print the records.

    I think It is much better option
    I cant change data type since there is loads of data in the table
    if there is no other way i ll have to make a lot of changes in my report.
    i think the topic should remain open coz there can be other solution for
    the problem
    With thanks

    Anand Kumar
    Navision Technical Consultant
  • fverkel
    fverkel Member Posts: 66
    You could consider this. I haven't tried it, but it makes sense.

    - Create an extra data-item based on Integer, ranging from 1 to the length of your code field.
    - OnAfterGetRecord: build a string containing questionmarks.
    The first time it should be '?', the 2nd time '??', then '???' etc.
    - Use SETFILTER on your other data-item on the codefield with this string.
    If you already have filters there, you could use another FILTERGROUP.

    It might cost to much performance, though.
    Keep It Simple and Stupid (KISS), but never oversimplify.
  • Mbad
    Mbad Member Posts: 344
    Krikis temp table is the way to go.
  • MTC
    MTC Member Posts: 159
    kriki wrote:
    MTC wrote:
    It looks to me as if you are storing your code in a Text field and not a Code field.
    Or you are using SQL.
    There is a property "SQLDataType" that you can change to integer so the code-field is sorted as an integer. BUT you CANNOT put codes anymore that are not integers. And everywhere you use that field you should change the SQLDataType.
    Maybe better to read the data, put them in a temptable, put some '0' before them so each code has the same size (001,002,100,...) and then read the temptable to print the records.

    How does that work with the G/L account then on SQL? Sorry, I don't really touch these things apart from doing upgrades, but for example, in the W1 G/L account, the key is CODE, in the Spanish version it's TEXT, so the sorting is totally different. I only know this from an upgrade of a vertical I did from W1 to ES. Somehow on the first pass it remained as CODE when it should have been TEXT (my fault). Basically the G/L Account didn't work as it should have done.
  • MTC
    MTC Member Posts: 159
    Sorry Kriki, didn't rea you post fully.

    It's a bit of pain in the bum isn't it to have to go and set this SQLDataType thing. I really didn't realise about this, but I suppose I have to study more the differences between W1 and ES. It's not documented very well however where these things are different.
  • fufikk
    fufikk Member Posts: 104
    Using the temp table code/text values could be first evaluated to int datatype, sorted and then do the actual report processing.
  • kriki
    kriki Member, Moderator Posts: 9,121
    MTC wrote:
    kriki wrote:
    MTC wrote:
    It looks to me as if you are storing your code in a Text field and not a Code field.
    Or you are using SQL.
    There is a property "SQLDataType" that you can change to integer so the code-field is sorted as an integer. BUT you CANNOT put codes anymore that are not integers. And everywhere you use that field you should change the SQLDataType.
    Maybe better to read the data, put them in a temptable, put some '0' before them so each code has the same size (001,002,100,...) and then read the temptable to print the records.

    How does that work with the G/L account then on SQL? Sorry, I don't really touch these things apart from doing upgrades, but for example, in the W1 G/L account, the key is CODE, in the Spanish version it's TEXT, so the sorting is totally different. I only know this from an upgrade of a vertical I did from W1 to ES. Somehow on the first pass it remained as CODE when it should have been TEXT (my fault). Basically the G/L Account didn't work as it should have done.
    They probably use something like:
    1
    10
    100
    11
    110
    2
    ...
    Also the Belgian DB used this in 2.XX. (And I suppose it still does).
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!

Categories