times sorting incorrectly

Discussion of the version of HanDBase that runs on the iPhone and iPod touch devices. This includes the synchronization conduits as well.

times sorting incorrectly

Postby robertmac » Tue Aug 20, 2013 5:43 pm

I've set a database to sort by date (primary sort) and then time (secondary). It seems to be sorting the time numerically only. A 2pm event is listed before an 11am event. Is there a way to get a true chronological sort?
robertmac
 
Posts: 9
Joined: Thu May 21, 2009 10:39 am

Re: times sorting incorrectly

Postby dhaupert » Wed Aug 21, 2013 6:22 am

Hi Robert,

Thanks for your post. We don't even consider the formatting of the field when the sort is performed, it's based on the binary value which is 24 hour time, so it should sort correctly. Are you sure you have Ascending selected as your secondary sort? If so, perhaps you can send me the database so I can take a look? My email is below..
dhaupert
 
Posts: 4111
Joined: Tue May 26, 2009 11:51 am

Re: times sorting incorrectly

Postby robertmac » Wed Aug 21, 2013 7:02 am

Thanks for the reply. Yes, I've double-checked the filters. It turns out one database is working properly and one is not. I'll send them to you.
robertmac
 
Posts: 9
Joined: Thu May 21, 2009 10:39 am

Re: times sorting incorrectly

Postby dhaupert » Wed Aug 21, 2013 9:23 am

Hi Robert,

Thanks for sending the databases. Responding here for the benefit of future searchers everywhere ;)

The database that seemed to sort incorrectly was one where the time field was not actually a time field. It was a DB Popup field, which is treated as a text value for the sake of calculations/sorting/etc. This comes up every now and then and is important to note- a DB Popup and a Relationship field both are treated as text fields in the case of sorting, calculations, filters, etc.

That said, the relationship and DB Popup fields both are treated as text values for calculations, but if you use them in a calculation they could work if their value is wholly numeric. For example, 1234 is a numeric value and could be brought over as a DB Popup and Relationship field type both and be used just as it was in the main database. But 11:00 am is going to be brought over as Text into the DB Popup and when used in a calculation the value of 11 would be the numeric value (that is, the numeric portion of the text up until the first non-numeric (ie, : ) was encountered.

Here's a workaround for your sorting needs:
Add another field to your Upcoming tours database - a calculated field. Have the calculation be the time field + 0 and outputted as an integer value. Now hide this from the list view and edit record screens.

In your reservations database, add another field called 'Time As Int' and make it a DB popup pointing to the field you made above. Set it to match the group of the Time field, so that when you bring over the real Time field, you'll also bring over this one. Now you have a numeric value (the number of seconds into the day) that you can use for sorting purposes. No need to show it here either- so set it to hidden but just keep it in mind for future sorting needs!

Hope this helps!
dhaupert
 
Posts: 4111
Joined: Tue May 26, 2009 11:51 am


Return to HanDBase for iPhone and iPod touch

Who is online

Users browsing this forum: No registered users and 1 guest