Sorting on code

anand_sri12

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

MTC

It looks to me as if you are storing your code in a Text field and not a Code field.

kriki

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.

anand_sri12

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

fverkel

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.

Mbad

Krikis temp table is the way to go.

MTC

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

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

Using the temp table code/text values could be first evaluated to int datatype, sorted and then do the actual report processing.

kriki

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).