Sorting List Views by Integer Values

8 posts by 3 authors in: Forums > CMS Builder
Last Post: July 13, 2012   (RSS)

By InHouse - July 11, 2012

A few clients are wondering about an issue in editor List Views in the CMS. If a client sorts by a numeric field (such as "Item Quantity") the ordering that comes back puts '11' before '1' in the list. Is there a way for force leading zero's without displaying them? That was how we used to fix this issue programmatically in the old days. I'm sure there's a better option now.

J.

Re: [gkornbluth] Sorting List Views by Integer Values

By InHouse - July 12, 2012

You're right Jerry. Bu the issue here also applies to section list views within the admin portion of the CMS. So let's say you have a products table with an inventory count displayed in the list view. The user naturally will re-sort that table by the inventory column to see what they have lots/few of in stock. However, the result is that when ordered ascendingly, the product with 11 units will display before products with 1 unit.

i.e.:
Ordered QTY Column
11
12
1
2
3
etc...

Not quite what was expected - though very familiar to people born before 1984. ;-)

J.

Re: [InHouse] Sorting List Views by Integer Values

By gkornbluth - July 12, 2012

Interesting.

Hope this will prompt a fix.

Jerry
The first CMS Builder reference book is now available on-line!







Take advantage of a free 3 month trial subscription, only for CMSB users, at: http://www.thecmsbcookbook.com/trial.php

Re: [InHouse] Sorting List Views by Integer Values

By Jason - July 12, 2012

Hi,

The best solution here is to actually make the field a numeric field. By default all text fields in CMS Builder are stored as mySQL strings. If you edit a field, under Advanced Options, you'll see "MySQL Column Type". In your example, you can give your field a mysql field type of "INT". This should take care of the sorting issues. (Remember to backup first).

For more information about mysql numeric types, see here:
http://dev.mysql.com/doc/refman/5.5/en/numeric-types.html

Hope this helps
---------------------------------------------------
Jason Sauchuk - Project Manager
interactivetools.com

Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/

Re: [Jason] Sorting List Views by Integer Values

By InHouse - July 13, 2012

This sounds very sensible, Jason. Thank you.

Sadly though, when I tried it (after backing up of course ;-) ) the following error gets thrown:

There was an error changing the MySQL Column, the error was:

Incorrect integer value: '' for column 'qty' at row 133

Not sure where to take it from there. Running CMSB v.2.15.

Re: [InHouse] Sorting List Views by Integer Values

By Jason - July 13, 2012

Hi,

What's happening is that when you change the column type, mySQL attempts to convert each value in that field to the new data type. In your case, there is one (or more) records where qty has an empty string as a value, which cannot be converted. For any record without a value for qty, trying putting in a 0. Once all qty fields have an integer value, try changing the column again.

Hope this helps.
---------------------------------------------------
Jason Sauchuk - Project Manager
interactivetools.com

Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/

Re: [Jason] Sorting List Views by Integer Values

By InHouse - July 13, 2012

Your are correct, of course. That did the trick. We're now sorting with gusto.

Many thanks, Jason.