Column Sort (Numeric?)

[bkay]

Hi there,

New user here...
Got things setup and working... made my first Browse and Edit form, and loaded in some data into the database... all is displaying well...

Curious though... I have a column that displays "Price" information, and when I click the column header to sort this info, (I did format the field with nuNumber $...", as currency)

Currently sorted items look like this...(
$995.35
$99.81
$99.63
$99.51
$975.25
$975.05
$972.50
$95.35
$92.52
...etc

Is there a way to get it to sort numerically?

Thanks!

[toms]

Hi and welcome !

Cast your field to float/int format when you select that field in your query and then order the result set by that column.

eg.

Code: Select all

SELECT CAST(your_field as decimal(10,5)) AS your_field FROM table_name 

if it doesn't work, try this:

Code: Select all

SELECT field1, field2, field3, field4
 FROM ( 
 SELECT field1, field2, field3, CAST(field4 as decimal(10,5)) as field4 FROM table_name  
) as T

[bkay]

Thank you for the response toms,

I've changed it to:

Code: Select all

SELECT CAST(components_price as decimal(10,5)) AS components_price FROM components

I believe I've substituted in my field and table names correctly.

However, I've tried putting it in the "Form Properties"->Browse->SQL, and doesn't seem to make a difference.
Also tried putting it in the "Form Object List"->components_price->Display (as well as 'Select'),and didn't make a difference their either.
I would suspect I'm not trying in the right places. (Apologies, I'm still learning how this all fits together).

Can you be a little more specific in where I put the SQL code?

Cheers!
Bill

[toms]

Yes, in the Form Properties"->Browse->SQL

I've tried it again with a new form and the sorting works all fine, without changing the sql at all. Maybe you see a difference to your settings.

Recording:
https://vimeo.com/272243699

Database field:
numertest_number: decimal(12,4)

Input Type:
nuNumber
$ 1000.00

[bkay]

Thanks toms!

Database field: numertest_number: decimal(12,4)

Looks like my field-type was the problem.

#newbieissues#
Now I know!

Cheers
Bill

[toms]

Great!

[admin]

.