I'm working on a report that sorts by item code. Many of the codes include numbers and sort like "A1,A10,A2,A20"
What I would like is..."A1,A2,A10,A20". Any suggestions?

Thanks,
Ev

Dear evertsen,
For item A1 use A01 as the search key, for item A2 use A02 as search key and so on. Then you can sort the report by search key. There are many other way, including using other unused field beside search key or even using alternative item code

Rgds,
Paul

Ev,

normally the item code is "aligned" either left or right when setting up Baan to avoid exactly that problem. It seems like your item logic would require a right aligned code (" A1", " A2", "A10" ... watch out for the space at the beginning of first two item codes!). However you are using left aligned codes: "A1 ", "A10", "A2 ". Now the spaces are on the right and the sorting changes.

If you are already in a productive environment please DO NOT CHANGE the code alignment now, since that will corrupt your database. However, if you are still in the implementation phase, you can use the session "modify code alignments" to change the item code domain "tcitem" to right aligned. THat will give you the correct sorting.

In a productive environment you only can do the following: Modify the session script which gives the base data for your report to produce another variable (myitem) from eg. tiitm001.item. Make this variable right aligned by standard string manipulation functions. Then use this new variable to sort your report.

Uli

Thanks for the suggestions but...I would like to avoid having to maintain another field and also keep the left justified sort (most of the items on the report are not like the example codes). I only wish to change the method in which numbers are sorted, to be precise I am looking to duplicate the sort that exists in Excel (for example).

Hi evertsen,

I've done something similar recently, where I needed to have a report sorted first on the first 3 numbers of the employee-number (representing employee-groups) and secondly on the employee names.

I decided to read in the employee numbers and names from the employees table from within my program script, attach one string to the next (as "nnnnnnAaaaaaaaaaaBbbbbbbbbbbbbbbbbbbbbbb", where the "nnnnnn" is the number, and the rest is the name) in each case, then add this long string to a dynamically-growing array which can subsequently be sorted on distinct string subsections. This sorted array is then used as the basis for doing selects and other processing for each employee, before sending the data to the report.

Here's an extract from the code:
| We need first to get the names and Personal-Nummer (PNRs) for each person between
| the first and last names chosen by the user. This list is placed in an array, and
| then sorted as follows:
|
| 1) firstly on the first three numbers of the PNR ("100", "200", "300", etc.,
| representing the groups Angestellte, Arbeiter, etc.);
| 2) subsequently on the names in alphabetical order.
|
| So, we end up with a listing like this:
|
| 100222 Anthofer
| 100666 Braun
| 100444 Dolmetscher
| 100111 Zwilling
| 200777 Bertelsmann
| 200333 Grafing, etc.
|

string a.per.details(1, 1) based

| First get the names...................
set.mem(a.per.details, 49, " ")
per.count = 0


select llwas017.name, llwas017.persnr, llwas017.stammkst
from llwas017
where llwas017._index4 inrange {:name.f} and {:name.t}
and llwas017.persnr not like "[A-Z].*"
and llwas017.name not like "[1-9].*"
and llwas017.name not like "Test.*"
and llwas017.edat < :zero.date | => end.date not yet fixed => this person still active.....
order by llwas017._index4
selectdo

per.count = per.count + 1
per.string = llwas017.persnr & llwas017.name & llwas017.stammkst
alloc.mem(a.per.details, 49, per.count)
a.per.details(1, per.count) = per.string

endselect


| Next, sort this array as described...............

| First sort field - 6 characters from position 1...............
qss.start(a.sort.def, 1, 1)
qss.way(a.sort.def, 1, QSS.UP)
qss.type(a.sort.def, 1, DB.STRING)
qss.length(a.sort.def, 2, 6)

| Second sort field - 43 characters from position 7..............
qss.start(a.sort.def, 2, 7)
qss.way(a.sort.def, 2, QSS.UP)
qss.type(a.sort.def, 2, DB.STRING)
qss.length(a.sort.def, 2, 43)

| Do the sort..................
ignore.val = qss.sort(a.per.details, a.sort.def)

| Do some processing....................
for ii = 1 to per.count
select something
from sometable
where somenumber = a.per.details(1, ii; 6)
selectdo

blah.blah
more.blah

| Finally send the data to the report......
report.send()

endselect
endfor


Of course, one could play around with "hidden" report fields and the like, but I wanted to try out the qss functions.

So, in your case, you could set your strings to a "ABnnnnn" format to cover all Excel-table addressing issues, and do your sort accordingly.

I've no doubt somebody will come up with a one-liner that does what you want, and makes you a nice hot cup of tea into the bargain;), but this has been my one cents' worth.....

HTH,
Robertb