Sorting numbers in a text field

[trackmanpete]

I am hoping someone can enlighten me on how Alpha does sorts of numbers with letters attached in a text field.
I am sending along a sample file and a photo of what I would like to do. The database on the right shows the sequence I would like. I am able to get this by right justifying the Performance field and doing a descending sort. When I try this in Alpha, I get the order as shown in the left Alpha database sample.

Input as shown is required as there are some races that use auto timing, accurate to a hundreth of a second, while other times are recorded by using hand times, as designated with the h after the hand time. Some times in an event will be less than 10 seconds, while other times will be greater than 10 seconds,thus the wide range of times.

I have worked with the database on the right for many years, so I just expected Alpha to do sorts in a similar fashion, but I am finding this is not the case (or at least it is not working for me).

Any suggestions would be helpful. (Please ignore the sort order field in the second database).

Thanks....

[Stan Mathews]

Re: Sorting numbers in a text field

Alpha sorts any character field from left to right by character ascii value, no matter whether the field contains digits, no matter the length of any digit sequence. The ascii values can be investigated using ? a5_ascii_table() in the interactive editor. Digits have lower ascii values than "letters", capitalization is not considered.

If you want to get the desired sort use an expression like

padl(alltrim(performance),13,"0")

padl(alltrim(performance),13," ")


substituting the actual defined field width of the performance field in place of 13.

(To be very technical, there are no numbers in a text field.)

[trackmanpete]

Re: Sorting numbers in a text field

Hi Stan:
I am trying to understand the process and why it is so difficult in Alpha compared to the other database I use. The help file info for padl() could be written in Latin and I wouldn't be able to follow the logic, unfortunately.

Here is what I tried and I get back an error, saying too few parameters:

'Default comment text.
DIM records_found as N
records_found = topparent.queryrun("",padl(alltrim("Performance")),12,"0","","No","",.f.)

I also tried it with " ", as in your second suggestion with the same results.

I can see where the alltrim() gets rid of all the spaces, but I can't grasp what padl does. The rest of the expression I developed using the custom query Action Script.

[Al Buchholz]

Re: Sorting numbers in a text field

Hi Stan:
I am trying to understand the process and why it is so difficult in Alpha compared to the other database I use. The help file info for padl() could be written in Latin and I wouldn't be able to follow the logic, unfortunately.

Here is what I tried and I get back an error, saying too few parameters:

'Default comment text.
DIM records_found as N
records_found = topparent.queryrun("",padl(alltrim("Performance"[SIZE=4][COLOR=red][B]))[/B][/COLOR][/SIZE],12,"0","","No","",.f.)

I also tried it with " ", as in your second suggestion with the same results.

I can see where the alltrim() gets rid of all the spaces, but I can't grasp what padl does. The rest of the expression I developed using the custom query Action Script.

Pete

Try this instead.

padl(alltrim("Performance"),12,"0")

I suggest that you calculate a value like that into a variable and get the results, then use the variable in the function.. (In your case the topparent.queryrun..)

As far as what the padl() does for you, consider this...

 
 
dim al as n
al = 12
?str(al)
= "        12"
?alltrim(str(al))
= "12"
?padl(alltrim(str(al)),5,"0")
= "00012"
?padl(alltrim(str(al)),5,"x")
= "xxx12"
?padl(alltrim(str(al)),5,"-")
= "---12"
?padl(alltrim(str(al)),11,"0")
= "00000000012"

[Tim Kiebert]

Re: Sorting numbers in a text field

or try,

"padl(alltrim(Performance),12,\"0\")"

[Stan Mathews]

Re: Sorting numbers in a text field

I can see where the alltrim() gets rid of all the spaces, but I can't grasp what padl does.

Padl is shorthand for padleft which pads/fills a string with the specified character to specified length. This a physical right justify rather than a cosmetic right justify.

The rest of the expression I developed using the custom query Action Script.

The rest of the expression is your issue which I think Tim solved. Any time you use a function which contains quote marks inside another function requiring a quoted expression, you have to manage the "interior" quotes.

padl(alltrim(Performance),12,"0") would be proper to use for your sort order.

Placed inside the
topparent.queryrun() function where the order expression is indicated as Order_Expression as C, you must quote the original.

"padl(alltrim(Performance),12,"0") " but that leaves us with the two interior quotes (in red). Not doing anything confuses Alpha because we would have two strings (orange and blue) with a character in between (green).

"padl(alltrim(Performance),12,"0")"

Alpha has the escape character \ to let us tell it that the next quote mark is an interior quote and that it should keep on interpreting the string rather than thinking the string has ended.

"padl(alltrim(Performance),12,\"0\")"

[trackmanpete]

Re: Sorting numbers in a text field

Stan, Al and Tim:
Thank you so much for this excellent lesson. the "L" in "padl" was confusing me, as I kept pronouncing it as "paddle"...

I've been away so haven't tried the formula yet, but Stan's explanation gives me a very concrete and clear understanding of what is taking place.

My other database doesn't use fixed size of fields, so the issue with sorting doesn't occur as it does with Alpha, one reason why I never thought in Alpha terms prior to this.

This has been very helpful and useful knowledge. Thanks again.