Sorting numbers

[Ed Barley]

Hello Everyone,

When you want to sort numbers in order, 1,2,3,4,5 etc. instead of them sorting 1,10,11,12,2,20,22,3,30,33, etc., is there a function, that will force them to do so.

Instead of using 01,02,03,04, to keep 1 thru 9 in order??

I have a report, and a form, that need to sort the details this way. I would have sorn, that when I made them origionally, they sorted correctly, but now they sort, 1,10,2,20,22,3,30 etc.............

Thanks Ed

[Stan Mathews]

RE: Sorting numbers

"Numbers" will sort in numerical order. If you have character fields consisting of "digits", they will sort in character order, beginning from the leftmost position.

You will have to sort on Val(charvalue).

[Ed Barley]

RE: Sorting numbers

Thank You Stan.

That is exactly what it is, and Val() works great. Thanks I learned a new function.

I forgot, that this one field is a Char field.

Have a nice night.


Ed

[Tom Cone Jr]

RE: Sorting numbers

Ed,

There's an easy visual cue to distinguish numeric fields from character fields. While browsing the table with the default browser, if the data in a field is left justified, the field is char type. If it's right justified then it's numeric. -- tom

[forskare]

RE: Sorting numbers

Tom's point is good but a calc field with just numbers is also left justified by default.

kenn

[Tom Cone Jr]

RE: Sorting numbers

Ken,

If you are referring to numeric fields that are defined in field rules to be 'calculated fields' then you are mistaken. These will appear right justified in the default browse for your table.

If you are referring to a calculated display field in a form, then you are talking about something other than what I was describing.

-- tom

[forskare]

RE: Sorting numbers

Tom,

I was talking about the latter, a calc field on a form.

kenn

[Ed Barley]

RE: Sorting numbers

Hi Everyone,

Appreciate all the tips. A few months ago, I was really scared to run into problems. Now I look forward to finding out alternate ways to get to the correct end. I see that in the database world, you can sure do a few zig, zags to make thing work.

Ed

[Ed Barley]

RE: Sorting numbers

Hello Everyone,

When I designed my application, this particular field should have been numeric(hind sight).

I have tried to filter a form, and a report with VAL(), on this CHAR field, but it is still sorting as such 1,10,11,12,2,3,4,5,6,7,etc.

Is there a way to help VAL make the 10,11,12, sort after 9 etc.

Thanks Ed

[Ed Barley]

RE: Sorting numbers

Hello Again,

Could someone help, with inserting VAL(), into this script?

I cannot seem to get it working right in the query.order part of the script. The field that needs to sort by number order is the char field "Dock_Receipt_No". It is a script, that goes with a Variable, and Branching.

'Open a Form or Browse layout, displaying all, or selected records in the layout.
query.filter = "(Job_number = Var->Job ) "
query.order = "Job_number+Port_Code+Dock_Receipt_No "
'replace variables in the filter with their actual values
query.filter = convert_expression(query.filter,"V")


'Open the layout_type showing just the records that satisfy the filter
DIM Shared varP_Main_Table2 as P
DIM layout_name as c
layout_name = "Main Table2"
varP_Main_Table2 = :Form.viewqueried(layout_name,query.filter, query.order)
Dim object_name as C
object_name=":"+"Main_Table2"
DIM varP_Object as p
'Get a pointer to the specified object
VarP_Object=obj(object_name)
'Check if gthe specified object exists
if .not. is_object(varP_Object)then

'Displays a message box (style:'OK, Cancel Buttons', image: 'Question symbol', id: 'msgbox1').
DIM SHARED msgbox1_result as N
DIM SHARED msgbox1_OK_Button as L
DIM SHARED msgbox1_CANCEL_Button as L
msgbox1_OK_Button = .F.
msgbox1_CANCEL_Button = .F.

title_var = "Vessel Job Number not found!"
message_text_var =

[Ronald Anusiewicz]

RE: Sorting numbers

I don't know if this will help you, but this is what I do to sort numbers in character fields.

I design an index which includes the field. Let's say the field is 5 characters long.

field character 5

I then use the padl function.

padl(alltrim(field),5,"0")

1
11
2
3
33
4

becomes

1
2
3
4
11
33

the index looks like

00001
00002
00003
00004
00011
00033

Ron

[CALocklin]

RE: Sorting numbers

Adding to Ron's comments, you could change your query order to:
query.order = "Job_number+Port_Code+padl(alltrim(Dock_Receipt_No),5,'0')"

Note that the zero at the end is enclosed in single quotes because (a) the whole order expression must be enclosed in double quotes and (b) in this case it is really just a character.

You can also use double quotes preceded with the backslash but I find that method more difficult to read:
query.order = "Job_number+Port_Code+padl(alltrim(Dock_Receipt_No),5,\"0\")"

FYI: Adding Trim() is adequate IF your data is entered correctly; i.e., without preceding blank spaces. However, the use of Alltrim() is much safer. The reason a trim operation is needed is that a character field STARTS with the characters entered and is filled with blanks at the end. Therefore, using PADL(FieldName,5,"0") for a 5 character field with the value "98" in the field would simply result in "98 " - because the field already has 5 characters (spaces are characters as far as a computer is concerned), no zeroes are added.

[Ed Barley]

RE: Sorting numbers

Thanks to both of you for the help. I really appreciate the explination, it helps me to understand what you both have done.

I chose to use:

"Job_number+Port_Code+padl(alltrim(Dock_Receipt_No),5,'0')"

and it worked great in the script. I also used it without the quotes for an order expression in a report, that esentially needed the same number sorting.

Have a nice night..........Ed