All of you are hopefully aware that you should never, ever, use time sensitive formulas anywhere in a view (in a selection formula or a column formula). This includes @Now, @Today, @Yesterday, and @Tomorrow. There are several ways to avoid using time sensitive formulas, and many of these can be found on this and other web sites. Here are a couple of the many things that can be done:

• Writing a daily agent to update all documents (create a field called "TODAY" on the document)
• Writing a daily agent to update just the documents that need it (put a flag on the document)
• Writing a daily agent to move documents in and out of a folder

Here, we talk about a new one that is available in Notes 6. Even though this is available now, we are not saying that this method should be used instead of the other ones that worked in previous releases. All we are doing is giving a new choice.

Let's say you want to have a view that shows documents created in the last 14 days. The easiest thing to do is to create a selection formula like this:

SELECT @Created > @Adjust(@Today; 0; 0; -14; 0; 0; 0)

Since a time sensitive formula is in the view selection formula, every time any user opens the view the index will need to be rebuilt, even if no documents have changed in the view. A better method would be to hard-code the date in the selection formula so the index won't need to change. This tip was posted on October 1, 2003. 14 days ago is September 17, 2003. So we could change the selection formula to be:

SELECT @Created > @Date(2003; 9; 17)

That selection formula has fixed values in it, so the index will only be updated when documents are created (or documents created less than 14 days ago are modified). But, with hard-coded dates, the time frame will get out of date each day. That's where Notes 6 comes in.

In Notes 6 you can now change selection formulas and column formulas programmatically. Note that this could be very dangerous. You should use this capability with caution. For our example, we will create a scheduled agent, set to run at 12:10 AM each day, that updates the hard-coded selection formula. This will allow us to take the performance advantage of using hard-coded dates, but still make sure the view is up to date.

(Technically, the view is looking for documents created after midnight local time - server time - on the hard-coded date. If your application is global, there is a potential for a document created 14 days and a few hours ago to still appear in the view, or ones created 13 days but not quite 14 to disappear from the view. This only affects documents on the verge of disappearing, and should not be an issue.)

Sub Initialize
   Dim session As New NotesSession
   Dim rightNow As New NotesDateTime(Now)
   Dim db As NotesDatabase
   Dim view As NotesView
   Dim selection As String
   
   Set db = session.CurrentDatabase
   Set view = db.GetView("Last 14 Days")
   Call rightNow.AdjustDay(-14)
   selection = "SELECT @Created > @Date("
   selection = selection & Cstr(Year(rightNow.LSLocalTime)) & "; "
   selection = selection & Cstr(Month(rightNow.LSLocalTime)) & "; "
   selection = selection & Cstr(Day(rightNow.LSLocalTime)) & ")"
   view.SelectionFormula = selection
End Sub

All this agent does is creates a selection formula to be used by the view called "Last 14 Days". We take the current date and adjust it backwards 14 days. The adjusted date is used to build the new hard-coded values for the year, month, and day in the selection formula. Note that simply changing the selection formula of the view is sufficient - we do not need to save the change or anything else.

Again, there are other ways to accomplish the task of having a "view" of documents created in the last 14 days. Folders work fine, updating documents is fine, etc. This is just one other alternative that can be used.