Page 1 of 1
Column Sort (Numeric?)
Posted: Mon May 28, 2018 2:58 pm
by 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!
Re: Column Sort (Numeric?)
Posted: Mon May 28, 2018 3:36 pm
by 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
Re: Column Sort (Numeric?)
Posted: Mon May 28, 2018 7:58 pm
by 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
Re: Column Sort (Numeric?)
Posted: Mon May 28, 2018 8:36 pm
by 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
Re: Column Sort (Numeric?)
Posted: Tue May 29, 2018 12:10 pm
by bkay
Thanks toms!
Database field: numertest_number: decimal(12,4)
Looks like my field-type was the problem.
#newbieissues#
Now I know!
Cheers
Bill
Re: Column Sort (Numeric?)
Posted: Tue May 29, 2018 12:47 pm
by toms
Great!
Re: Column Sort (Numeric?)
Posted: Sat Jun 02, 2018 4:54 am
by admin
.