A couple of years ago I talked about a way to have date-based views without any of the worrisome indexing issues. A nightly agent runs — as shortly after midnight as is possible — and updates the selection formula of the view to include the new day's date:

Dim TodayDateTime As New NotesDateTime(Now)
Set view = db.GetView("FutureEvents")
selection={Form="Event" & EventDate>[}+TodayDateTime.DateOnly+{]}
view.SelectionFormula = selection
Call view.Refresh

Thus there's no need for including the troublesome @Now in the selection formula and so we can avoid the problems that can bring about.

For the past two years I've been using this method all over the place, wondering how I ever managed without it. There are, however, two problems little niggling problems I have with it that I want to share solutions to.

Hard-Coded Reproduction of the Selection Formula

Because we need to reproduce the selection formula in the agent we have, in effect, ended up with two selection formulas — one in the view itself and then the other in the agent. The problem arises when you make a change to the view's selection formula. You have to remember to update the code in the agent too. If you forget then when you come in to work the following day you'll find the view has returned to it's previous state, which can be a bit embarassing if your client notices before you do.

To get round this I came up with the following alternative. It should be self-explanatory, but I'll explain anyway. All the agent really needs to do is take the view's current selection formula and replace the old date (yesterday's) with today's. It can do that like so:

view.SelectionFormula = Replace(
        view.SelectionFormula, 
        YesterdayDateTime.DateOnly,
        TodayDateTime.DateOnly
)

No need for the rest of the formula to be in the agent and no need to update it in two places or to risk forgetting!

Updating Scheduled Agents from a Template

Another problem, although addressed by the above fix, can still be troublesome and the following "fix" is relevant in its own right. Whenever you changed the scheduled "Update Views" agent in the template and then refreshed the live copy it would invariably stop running. Yet more embarrassment when same client notices the "future" view is showing things from last week.

The problem arises when you develop the template on a different server to the live site. The scheduled agent in the template is set to run on one server and the live version on another. When you update from the template the live version's agent is set to run on the dev server, where there is no replica of it and so the agent never runs. This is true of all scheduled agents.

The solution to this is simple -- move all the code in to a Script Library and set the agent to not inherit design changes from the template. The agent now becomes a one-liner which calls a sub routine in the Script Library. The agent never need change again.

I now tend to have a Script Library called "ScheduledAgentTasks" which contains as many sub routines as there are scheduled agents. Each sub has the name of the agent which calls it. To edit an agent you simply edit the sub of the same name.

This gets round another problem I often come across whereby, if you edit a scheduled agent directly it runs when you save it (or at least that's my understanding). This is often not what you want to happen and can be the source of more embarrassment. By moving to a Script Library approach you get round this problem.

So, there you go. A couple of simple but useful tips. You'll be able to see the approach in action when I get round to making DEXT available for download.