incrementing date searches

[wordmuse]

Hi,

There is a clerical task that I perform in URP3 every week, assuming I remember to do it.

I have an advanced search that looks for the following:

date created: date-n
date modeified: date-n
due date: date-n
flag: not completed

I then copy this advanced search repeatedly for a given month, changing date-n to date-n+1, and for the next one, date-n+2, etc. Thus, each week, I have a new batch of advanced searches that will locate all instances of items that were created on date-n+m, that were modified on date-n+m, or that are due on date-n+m, which have not yet been completed.

To create these advanced searches, I have to double-click each of the fields to call up the calendar, then search for the right date, and use reasonably fine motor control to click that date.

What would be nice is if there was a way for URP to "understand" this task and let me increment an advanced search by 1 day (or one week, or one month, etc.) so that I don't have to manually do it.

It's repetitive; it's rule based; seems to me that it should be doable. Of course, I'm not a programmer, so what do I know? But just maybe it could be done?

Regards,
Bal

[quant]

Originally posted by wordmuse
There is a clerical task that I perform in URP3 every week, assuming I remember to do it.

why don't you add reminder to your created advanced search?


Originally posted by wordmuse
I then copy this advanced search repeatedly for a given month, changing date-n to date-n+1, and for the next one, date-n+2, etc. Thus, each week, I have a new batch of advanced searches that will locate all instances of items that were created on date-n+m, that were modified on date-n+m, or that are due on date-n+m, which have not yet been completed.

I'm not sure I understand why you need to create new batch of advanced searches every week. Are you using "relative" dates in your searches?

[wordmuse]

Howdy -

No - I'm not using relative dates.

I'm using static dates as described. Here's a screenshot.
http://balsimon.com/temp/showkinook/datesearch.png

As you can see, I've got advanced searches for every day. That way if I click on a date, I get everything for that date.

This is the only way to do it that I could figure out how to accomplish this.

What would be ideal, in my opinion, is to open the calendar, click a date and see everything for that date. But that's not available, so I've had to do this relatively complex work-around. It's irritating, but the best I can do with what I've got and with what my ingenuity can come up with.

If you have an easier way, I'm very much open to hearing about it.

Regards,
Bal

[quant]

I don't know how far back you need to go. If you always need to look back only certain number of days, one thing would be just to tick in each of those searches "store dates relative" and then rename them to "in x days", ..., "in 2 days", "Tomorrow", "Today", "Yesterday", "2 days ago", ...

In this way, those searches would be always "up to date", and you wouldn't need to create new ones.

Otherwise, we'll need to wait for the callendar pane and some easier implementation to achieve what you need ...

[wordmuse]

I think we're in aggressive agreement on this. :)

Regards,
Bal

[quant]

I can imagine searches having new attribute "calendar", and this would be used instead of value as a current date/month/year selected in the Calendar. Opening search that contains "calendar" attribute would automatically open Calendar pane. Well, in fact, this could work already with the current calendar we have.

Kinook, can we please have "calendar" attribute for the Search Items? ;-)

Later on, when we have a Calendar pane, opening a search with "calendar" attribute could run this search for the current Calendar view. So imagine you have a week view, the search would run with substituting each day in the search and display the results in the calendar. I would be smart enough, so that if you selected say whole month, it wouldn't need to search each day, but would replace "is equal to calendar" in the search with ">= 1st day of selected month AND <= last day of selected month", and then if you wanted to break it into days, it would run it for each day, etc.

Nice, I can't wait ;-)

[wordmuse]

Great idea about how to implement my original suggestion. Yay synergy!

Regards,
Bal

[wordmuse]

I know that this is probably not going to happen, but just in case. :)

Every month, I have to go through the process of creating 28, 30, or 31 saved searches that look like the screenshot below; one for each day of the current month.
http://www.balsimon.com/temp/showkinook/saved-search.jpg

As you can see, this search makes it so that anything created, modified or due on a certain date, but flagged as not yet done will show up in the search results.

It would be sweet indeed if I there was some way to increment relative to a starting date, an entire set of saved searches by specifying a start and end date.

Also - it would be nice if I could tick a box that said, "Do not include this saved search in the search results."

Regards,
Bal

[kinook]

You can advance the dates within searches using SQLite (http://www.kinook.com/Forum/showthread.php?threadid=2825).

First, identify the ItemIDs of your search items:
1) Ensure Tools | Options | Attributes | ItemID is checked
2) Select a search item and determine its ItemID value in the Item Attributes pane (Ctrl+4)

Second, determine the AttributeIDs of the date attributes to be updated:
1) Go to Tools | Attributes and display the ID column (right-click the column header and choose ID)
2) Locate the date attribute(s) and observe their IDs

Then run SQLite3.exe (see http://www.kinook.com/Forum/showthread.php?threadid=2825) and enter a SQL statement like this to advance all the date attributes of the searches by one day:

UPDATE FilterRow SET Value=Value+1 WHERE ItemID IN (1026,1027) AND ObjectID IN (1,2,969);

In the example above, 1026,1027 are the ItemIDs of two advanced search items I tested with, and 1,2,969 are the AttributeIDs of the Date Created, Date Modified, and Due Date attributes.

Change +1 to +7 to advance by one week or +30 (or +31 or +28, depending on the current month) to advance by one month.

[wordmuse]

Hi -

OK - so I just want to understand before I get started, as I've never used SQLite before.

Will this enable me to create a batch of searches to cover an entire month in one fell swoop, or will I be creating the new dates one-at-a-time.

Thanks for working through this with me.

Best Regards,
Bal

[kinook]

This won't create the searches, but once you have created them (it sounded like you already did), you can update several in one step (specifying their ItemIDs as mentioned).

The initial steps outlined above for identifying the items and attributes to update should be a one-time effort, then you would periodically execute the customized SQL command to advance the searches.

You can save the SQL command in a .sql file (i.e., update.sql), then create a batch file (i.e., update.bat) like this:

sqlite3 mydb.urd <update.sql

Put sqlite3.exe, update.sql, and update.bat in the same folder as your database and double-click the .bat file (or set up a scheduled task, etc.) to run the command.

[wordmuse]

OK - I'll give her a try this weekend (I hope).

Thanks a lot.

Regards,
Bal