View Single Post
  #1  
Old 01-14-2015, 01:25 AM
aaronjsolomon aaronjsolomon is online now
Registered User
 
Join Date: 07-06-2011
Posts: 30
Using title expressions for chronological sorting

Hi,

I’m trying to use title expressions to prepend a date/time to my item titles, so that they sort chronologically in the Data Explorer tree.

QUESTION 1

Currently, items in the tree can be sorted chronologically if their titles can be parsed by Windows as a date or date+time (see http://www.kinook.com/Forum/showthread.php?t=4106).

Is there a way to get items to sort in the tree, if the first part of the title is a date or date+time, followed by some descriptive text?

For example, say I keep journal items, and use title expressions to prepend a journal date (attribute type: date+time) to the item title, so that the titles in the tree looks like:
  • 1/13/2015 10:15 PM – Phone call to Dave
  • 1/14/2015 9:00 AM – Made sales calls to new clients
  • 1/14/2015 1:30 PM – Sent TPS report to Mary

Chronologically sorting this list would require UR/Windows to parse the date or date+time from the title while discarding the following text. Predictably, appending the title apparently causes the date parsing to fail, reverting back to a simple string sort. Is there a trick to making this work? Maybe a particular delimiting charater between the date/date+time string and the rest of the title that will silently drop the rest and parse successfully?

QUESTION 2 (Related)

Since I couldn’t get the sorting described above to work, I tried using the “Custom Insert date/time as:” feature to get a date that is string-sortable, per the help file (see chm::/custom_item_titles.htm). This results in item titles like the following:
  • 2015-01-13 22:15 – Phone call to Dave
  • 2015-01-14 09:00 – Made sales calls to new clients
  • 2015-01-14 13:30 – Sent TPS report to Mary

However, I also want to mix appointments in there, and have them all sort together.
  • 2015-01-15 11:00 – Dentist appointment

Since appointments use separate fields for Begin Date and Begin Time, their formatting in title expressions do not follow convention set in the “Custom insert date/time as:” setting, as it only applies to date+time attributes, not date(-only) or time(-only) attributes,

I tried using some SQLite functions in the title expression field, and although I got a few to work that were undocumented in UR help, like length() and lower(), I couldn’t get any date/time functions to work, and so couldn’t format the date attributes or time attributes to mimic the custom formatting of the date+time attribute. Nor could I figure out how to somehow “add” an appointment’s Begin Date and Begin Time together to make a single date+time. (When I tried to add a Begin Date and Begin Time together in a title expression, they evaluated to “11”.)

SUMMARY

In short, I’d like to:
  1. Use title expressions to prepend a date/time value to the beginning of item titles, so that they can be chronologically sorted in the tree, as well as in searches that aggregate them together;
  2. The date/time value can be drawn from multiple sources, including an email’s Message Date, a task’s Date Completed, or an appointment’s Begin Date and Begin Time.

Please advise! I’ve been trying to solve this problem for years, and I feel like I’m soooooo close to figuring it out. UR provides so much insane, awesome functionality, but I just can’t get over this hump.

Thanks!
Aaron
Reply With Quote