Sorting by a Date in a Text Field
2 posts by 2 authors in: Forums > CMS Builder
Last Post: December 24, 2012 (RSS)
By sidcam - December 23, 2012
On my list view page (and if possible my editor) I would like to set the default sorting of my MLS listing data to the record's entry day (newest first) and then price (highest first). Because I do a full refresh of the data nightly, I unfortunately can't use the system's "createdDate" field (because they're all today's date)
Anyway, I do have two fields in the downloaded data for this:
"entry_date" and "listing_price"
My problem is that "entry_date" is imported as a text field and the data as it's imported looks like this.
Record #1: "2012-12-22T21:51:12.363"
Record #2: "2012-12-22T20:36:52.657"
etc
Because it's a text field, the editor's sort is looking at the whole field (not just the year, month and date) which makes every record unique (killing the ability to sub-sort the day's entry by price).
In searching the forum the best answer I could find was to change the field to a date field, but that generated an error message- apparently the data isn't formatted to CMSB's idea of a date.
So questions...
In the sort tab of the section editor, is it possible to limit "entry_date DESC" so that it only looks at the first 10 characters of the field? (i.e. "2012-12-22")
If I give up trying to sort this way in the editor (which is fine), how would I place this sort limit on the list view page (where I really need it)?
If it can't easily be done in CMSB, I am using a Perl script to clean up the data's txt file before it's imported (using simple substitution commands). What would that field's data need to look like in order for CMSB to recognize it as a date? It seems cleaning up the data to make it a CMSB date might be the easiest way to go.
Thanks in advance for your help.
Sid
Hi Sid,
I think that the best approach, assuming it can be done easily enough is to use date fields. They would offer the kind of flexibility that you need now and many other options down the road.
I'm not sure how many records you'd need to convert at this point, or where you get the original data from and what format it's in.
It's a bit over my head, but if it's a number crunching issue and conversion that's too big to handle manually, the gurus at IT might be able to work out an algorythm to handle the format conversions required.
Just my thoughts,
Jerry Kornbluth
Take advantage of a free 3 month trial subscription, only for CMSB users, at: http://www.thecmsbcookbook.com/trial.php