Date-Based Views: A Couple of Scheduled Agent Tips

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(

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.


    • avatar
    • Richard Shergold
    • Tue 11 Mar 2008 05:38 AM

    I like the approach of calling the script library code from the agent - I guess that makes sense. For some reason (I don't really know why) I've avoided having some elements inherit from a template while others don't - I prefer either all elements to inherit from a template or none at all. Still, if I need a view that might use dates in the selection formula I may try your approach. Thanks, as always.

    • avatar
    • René Scheening
    • Tue 11 Mar 2008 05:41 AM

    Hi Jake,

    The danger of your 1st tip is that if an agent doesn't run one day -for whatever reason- the date will never be adjusted automatically again.

    But I like the idea. Maybe an alternative is to put the date of today always at the end of the selection formula and then replace the date based on its position..



    • avatar
    • Jake Howlett
    • Tue 11 Mar 2008 05:52 AM

    He René. That thought occurred to me too and I couldn't decide on the best solution. One might be to use Like() to look for whatever's inside square brackets [] and replace that, but that assume only one date being used. I guess you just have to make sure that the agent doesn't NOT run on any day.

    • avatar
    • Tim
    • Tue 11 Mar 2008 05:58 AM

    Now that is a really good tip about having the scheduled agents in a LS Library. Never thought about it, even when it actually is so simple and looking at the numerous times I have cursed over the agent running again.... Thanks...!!

    • avatar
    • Ferdy
    • Tue 11 Mar 2008 07:30 AM

    hmmm, I'm struggling to remember here, but wasn't there a much easier way to beat the date-indexing issue, one that does not require an agent or other dependency?

    I recall it had something to do with converting the date field to text in the column, but there is probably more to it. I'll post details if I can find them.

    -- Ferdy

    • avatar
    • Duncan Bradley
    • Tue 11 Mar 2008 07:35 AM

    I always make sure the date selection is at the end of the view formula, then I can just knock off the last 8 characters and replace them with the current date. So even if the agent fails to run, it will always update, unless someone changes the view formula. I also have a button on the view to do the same thing, just in case.

    • avatar
    • Ferdy
    • Tue 11 Mar 2008 07:38 AM

    Forget my previous comment, after looking into the details it does mean you require an agent. Apologies.

    • avatar
    • Martin
    • Tue 11 Mar 2008 07:57 AM

    Another way to bypass the usage of @today in view selection is:

    1. Create a scheduled agent that runs once a day (0:01) that writes @Now to the server's NOTES.INI i.e.

    ENVIRONMENT CurrentDateTime:=@Text(@Now);

    2. In every selection formula that uses '@Now' use the code:


    3. be sure that all view indexes rebuild every day after 0:01

    One of the advantages is that no changes are made to the production templates by an nightly agent. Obiously this option also has a few disadvantages, such as using NOTES.INI variables etc.

    • avatar
    • Jake Howlett
    • Tue 11 Mar 2008 08:09 AM

    Martin. One advantage of the approach I outlined above is that it's extendible. My example was simple for the purpose of a demo. In practice I find myself creating views such as "Events this month" or "Events last year" where there are sometimes more than one date in each view's selection formula. Obviously this makes the above Replace() call a little more complicated but it can be done.

  1. @Martin: The problem of your solution is that you cannot be sure, that the view is rebuildt on every server and on the local replicas.


    I have a general solution which works for selection formulas as well as for view columns.

    In the selection formulas I start with

    vToday := @Date(2005; 9; 9);

    REM {END vToday};

    end use the variable vToday instead of @Today.

    My nightly agents is:

    Sub Initialize

    Dim session As New NotesSession

    Dim db As NotesDatabase

    Dim org As String, changed

    Dim flag

    Set db = session.CurrentDatabase

    Forall view In db.Views

    flag = False

    org = view.SelectionFormula

    changed = SetToday(org)

    If Not Isnull(changed) Then

    view.SelectionFormula = changed

    flag = True

    End If

    If Isarray(view.Columns) Then

    Forall column In view.Columns

    org = column.Formula

    changed = SetToday(org)

    If Not Isnull(changed) Then

    column.Formula = changed

    flag = True

    End If

    End Forall

    End If

    ' refresh view

    If flag Then view.Refresh

    End Forall

    End Sub

    Function SetToday(Byval pStr)

    Dim pos1 As Integer

    Dim pos2 As Integer

    pos1 = Instr(pStr, "vToday :=")

    pos2 = Instr(pStr, |REM {END vToday};|)

    If pos1 = 1 And pos2 > 0 Then

    SetToday = |vToday := @Date(| & Format(Today, "yyyy\; mm\; dd") & |);

    | &Mid(pStr, pos2 - 1)


    SetToday = Null

    End If

    End Function

  2. This just may be to simple, but for me the easiest way to manage date based views is to have an agent in the database that updates all documents with the current date, runs once a night...

    The contents of the agent are:

    FIELD tim_CurrentDate := @Today ;


    I simply include this agent in any database that needs date based views and reference tim_CurrentDate in the selection formula... If for some unknown reason the agent doesn't run in a db, I just re-run it manually and everything is fine. That has only happened 1 or 2 times in over 5 years of using this technique...

    • avatar
    • Jake Howlett
    • Tue 11 Mar 2008 10:12 AM

    Simple is some times best Lance! It's horses for courses as always. Your approach might not be the best way in a database with 10s of 1000s of documents and replicas here and there. Sounds like it works for you though, so no problems there.

    It's good that all these solutions are coming out -- lets people choose which is best for them...

    • avatar
    • Rens
    • Tue 11 Mar 2008 10:17 AM

    Interesting approach but it will stuck if you have database with hidden design. In that case its kinda not possible to change selection formulas.

  3. You can turn off the scheduled agent auto-run "feature":

    Amgr_SkipPriorDailyScheduledRuns = 1

    I asked Julie Kadashevich about it after Lotupshere 2006 and was told it was added in 6.something: {Link}

    • avatar
    • veer
    • Tue 11 Mar 2008 10:22 AM


    Couple of things:

    1. Make code change in one server and pushing the agent to another where the server name to run the scheduled agent gets changed.

    My solution is to make the server name as the Production server in your development server. In development, you wouldn't run on schedule, so, no issues. Moving them to Production keeps the servername intact.

    Alternatively, you could also make the agent ask for a servername when enabled, but I have not used this technique. Might work.

    2. Agent runs when enabled. I know this is a hassle. But I read that there is a notes.ini setting with which this can be disabled. I wanted to get it implemented in our environment, but the priority was low, I forgot about it. Worth doing.

    If I find the ini variable I will post it here

  4. Hi all,

    Has for the server where to run a schedule agent we use a profile document with the server name where to run the schedule agent.

    if Ucase( db.Server ) <> Ucase(doc_profile.RUNON_SRV(0)) Then

    Exit Sub

    'Set a message to a log or the Domino console

    End If

    We always set the agent to run on any server, and we already have a procedure to set profiles documents in production.

    So that way we don't have problem with the schedule agent anymore.

    • avatar
    • Jake Howlett
    • Tue 11 Mar 2008 10:35 AM

    "In development, you wouldn't run on schedule"

    Why not Veer? I do. My dev box is my test box and I need it to match the live version as closely as possible. All the scheduled agents are enabled in development.

    • avatar
    • Dave S
    • Tue 11 Mar 2008 10:48 AM

    IBM's Answer to the problem.


    • avatar
    • veer
    • Tue 11 Mar 2008 10:50 AM


    For me, the dev box is for development and testing only, which I do, while coding that particular part.

    To test the behavior of the agent running on server, I create another agent and call runonserver in the scheduled agent.

    I haven't found a need where I have to schedule an agent on server and comeback periodically check its output. I do it like I mentioned above.

    If you feel that there is something I am missing out, I would be curious to know about it

  5. I have developed the following agent code which extends your original idea but also:-

    1) Modifies all views in the database

    2) Only updates the view if a change is needed.

    3) The agent can be scheduled to run multiple times per day reducing the chances that it does not run any day. All subsequent times it runs each day the agent will do nothing...

    Dim YesterdayDate As New NotesDateTime(Now)

    Dim TodayDate As New NotesDateTime(Now)

    Call TodayDate.AdjustDay(-1)

    Forall View In DB.Views

      If Instr(View.SelectionFormulae,YesterdayDate.DateOnly$) > 0 Then

        View.SelectionFormula = Replace(View.SelectionFormula, YesterdayDate.DateOnly, TodayDate.DateOnly)

        Call View.Refresh

      End If

    End Forall

  6. Great tip, Jake! I've become "used" to get a small embarrasment whenever scheduled agents are saved and they run. So now I'll use Script Libraries.

    Always interested on reading your experiences, when you have a chance, can you comment on your usage of "Development" environments, test, production, and your practices on moving code along these environments?


  7. @Peter, iterating through db.Views is kind of expensive. Better to hard-code the view names or store them in a profile, some such thing.

    Also, as others have pointed out, if the agent skips a day for whatever reason there's a problem. Better if you search for the ['s in the formula and see whether what comes before the next ] looks like a date.

    And you don't always want today's date in there.

    And there's also the matter of @Today used in a column formula.

  8. Why not use @TextToTime("Today") to get today's date?

  9. This tip is too funny. :) Just last week I had to create a Date view. Like everyone else is saying I like the tip about the script library. I might have to go back and change my code. :)

    • avatar
    • PaulE
    • Mon 2 Feb 2009 04:12 AM

    I've discovered a very annoying side effect of this method. I have a view with a date based selection formula set like this. The view also has actions who's button labels and hide formulae which are computed and have @DbColumn commands in them.

    Whenever the agent that re-sets the view selection runs the users see an ECL warning for user "-no signature-" attempting to use @DbColumn. Somehow resetting the view selection formula breaks the signatures on action buttons.

    I can see no way around this as the database.sign method works on the client and not on the server. *sigh*

  10. @Charles

    I think it was added in 7.0 because i found another cross reference (for AMgr_SkipPriorDailyScheduledRuns) here:


Your Comments


About This Page

Written by Jake Howlett on Tue 11 Mar 2008

Share This Page

# ( ) '


The most recent comments added:

Skip to the comments or add your own.

You can subscribe to an individual RSS feed of comments on this entry.

Let's Get Social

About This Website

CodeStore is all about web development. Concentrating on Lotus Domino, ASP.NET, Flex, SharePoint and all things internet.

Your host is Jake Howlett who runs his own web development company called Rockall Design and is always on the lookout for new and interesting work to do.

You can find me on Twitter and on Linked In.

Read more about this site »

More Content