Kinook Software Forum

Kinook Software Forum (https://www.kinook.com/Forum/index.php)
-   [UR] General Discussion (https://www.kinook.com/Forum/forumdisplay.php?f=23)
-   -   Using title expressions for chronological sorting (https://www.kinook.com/Forum/showthread.php?t=5323)

aaronjsolomon 01-14-2015 01:25 AM

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

kinook 01-14-2015 01:13 PM

1.
a) Download and install the latest build (UltraRecall.exe 5.1.0.9 in Help | About after installing).
b) Download reg.zip from http://www.kinook.com/Forum/showthread.php?t=5097 and extract EnableDateTruncateDelimiters.reg. Modify the file to include the desired delimiter(s) and double-click the file in Explorer to update the registry.
c) Restart UR.

2. To format an Appointment title with the begin date, time and title, use a Title Expression like

[Begin Date] || ' ' || [Begin Time] || ' – ' || [Item Title]

You can't use the Custom insert date/time option to format these dates/times -- you would need to modify your date/time formatting options in Regional settings in the Windows Control Panel.

aaronjsolomon 01-14-2015 03:05 PM

Holy cow, you are awesome. I'll try this out ASAP. Thank you!!


All times are GMT -5. The time now is 06:45 PM.


Copyright © 1999-2023 Kinook Software, Inc.