One Week Solutions

First let me thank you all for the responses to yesterday's conundrum. Lots of great ideas, one of which I've now switched to using. The solution I originally came up with seems kind of stupid now. Let me describe that first.

I had created a Folder call "EventsNextWeek". The first column was sorted on the EventDate field. Obviously the folder started out empty. To fill it involved two steps. First a nightly Agent to build/refresh its contents. The Agent's code was as follows:

Set collection = db.Search({Form="Event" & @Today<=EventDate
& EventDate<=@Adjust(@Today;0;0;7;0;0;0)}, Nothing, 0)
Set folder = db.GetView("MeetingsNextWeek")
Call collection.PutAllInFolder("MeetingsNextWeek")

This alone is required merely to move new Events in and the old ones out, on a daily basis. However, it doesn't deal with new Event documents created where the EventDate value falls in the next seven days or where Event documents are edited and the EventDate value moves outside the time slot we're interested in. Obviously these documents need adding/removing from the folder straightaway. To do this I had used a combination of WQS/WQO Agents. At first I'd thought a WQS would do, but I found you can't add a new document to a folder the first time it's saved. In this case I had to pass a URL parameter to the WQO Agent using the $$Return field on new documents. This is the point where I started to consider it a hack.

Thankfully, yesterday, Gerald Mengisen suggested a much better solution using a new Domino 6 View property called SelectionFormula. You can now set the View's selection formula in LotusScript! I've scrapped my folder and WQS/O Agents. Instead I have a normal view and a modified nightly Agent. The code for the agent is now:

Dim nowDateTime As New NotesDateTime(Now)
Dim thenDateTime As New NotesDateTime(Now)
Call thenDateTime.AdjustDay(7, True)
Set view = db.GetView("MeetingsNextWeek")
selection = {Form="Event" & EventDate >= [ } + 
nowDateTime.DateOnly + { ] & EventDate <= [ } +
thenDateTime.DateOnly +{ ] }
view.SelectionFormula = selection
Call view.Refresh

So, each day, the View only shows Events in the next seven days. It does this without the performance nightmare of having to use @Today in the selection formula. If a new Event is added or removed the View index updates straightaway. Thanks Gerald.

Another suggestion was to use GetAllEntriesByKey() with a list of dates. I've not tried this, but I'm assured it works. The reason I didn't use this is that there are other views I need to use this approach for (one merely to show future meetings) and so one nightly Agent made more sense.

Thanks for all the feedback guys. What must a SQL developer make of all this?! From memory the (My)SQL code needed to find next week's meetings would be something like below. No need for nightly agents or extra views.

 FROM Events 

The important line here is the last one - ORDER BY. All Notes needs is a better way of sorting search results. The NotesDatabase Search method needs an ORDER BY parameter to save all this faffing about.


    • avatar
    • Richard Shergold
    • Wed 1 Feb 2006 02:59 AM

    Jake. I like this idea and am very pleased Gerald pointed it out. I have started to make use of it already with a generic agent that will update any views in any app that need updating as long as I have REM "Agent Update Required" as a comment in the view selection formula.

    As I pointed out in my comment yesterday the one issue to be aware of with this method is what you need to do if you update the design of a database with a new template during the day (not good practice I know but sometimes unavoidable).

    You either need to run your agent in the template beforehand or run it in the DB as soon as the template is applied.

    • avatar
    • Jake
    • Wed 1 Feb 2006 03:23 AM

    The template issue is an, errr, issue. Not sure how I feel about it really. It will be a pain to document and inform the client's admin of, but wodyado? It's life with Notes I guess.

    • avatar
    • Nick Wall
    • Wed 1 Feb 2006 03:29 AM

    You already have your solution, but with regard to "sorting" a NotesDocumentCollection after a db.search()...I have used the following, it could

    be adapted for dates, numbers, etc.

    (I think I got this from LD6 forum, so thanks to whoever originally wrote it):

    Public Function SortDocumentCollection( dcol As NotesDocumentCollection, fieldName As String) As Variant

    'Sort documents in a collection in alphabetic order by the field specified

    'STM 2001-10-05

    Dim session As New NotesSession

    Dim db As NotesDatabase

    Set db = session.CurrentDatabase

    Dim array As Variant

    Dim k As Integer

    k = dcol.count

    If k <> 0 Then

    Redim array( 1 To k )

    array = docCollectionToArray( dcol )

    ' // Need to add a value at the end that will always be greater than all - language dependent

    Redim Preserve array( 1 To k+1 )

    Set array( k+1) = db.CreateDocument

    Call array( k+1).ReplaceItemValue( fieldName, "ÅÅÅÅÅÅÅ" )

    array = QuickSort( array, fieldName, Lbound(array), Ubound(array) - 1 )

    Redim Preserve array( 1 To k )

    SortDocumentCollection = array


    SortDocumentCollection = ""

    End If

    End Function

    Private Function QuickSort (array As Variant, fieldName As String, leftpos As Integer, rightpos As Integer) As Variant

    'Sub function in SortDocumentCollection

    'STM 2001-10-05

    Dim i As Integer

    Dim j As Integer

    Dim pivot As String

    If ( leftpos < rightpos ) Then

    i = leftpos

    j = rightpos + 1

    pivot = Ucase( array(leftpos).GetFirstItem(fieldName).Text )



    i = i + 1

    Loop While Ucase( array(i).GetFirstItem(fieldName).Text ) < pivot And i <= rightpos


    j = j - 1

    Loop While Ucase( array(j).GetFirstItem(fieldName).Text ) > pivot And j >= leftpos

    If ( i < j ) Then

    array = SwapInArray( array, i, j )

    End If

    Loop While ( i < j )

    array = SwapInArray( array, leftpos, j )

    Call QuickSort( array, fieldName, leftpos, j - 1 )

    Call QuickSort( array, fieldName, j + 1, rightpos )

    End If

    QuickSort = array

    End Function

    • avatar
    • Richard Shergold
    • Wed 1 Feb 2006 03:31 AM

    There is also the problem that if for any reason that nightly agent does not run then the app falls apart functionally.

    But that's something you have to live with too I guess.

    I'm not too sure about it either - I love the fact that you can update view formulas like this but these two factors (the template one especially) are a bit of a worry.

    • avatar
    • Richard Shergold
    • Wed 1 Feb 2006 03:43 AM

    Jake, of course the other worry is if your Client's admin procedures are to update their database designs from templates every night (e.g at 4.00 AM or whatever) you need to tell tham that either a) the nightly agent is actually scheduled to run in the template itself or b) it needs to run evey night AFTER their design update process runs. Bit of a nightmare really???

    • avatar
    • Richard Shergold
    • Wed 1 Feb 2006 03:44 AM

    Or alternatively set the view to "not update from design template" I suppose (something I hate doing).

    • avatar
    • YoGi
    • Wed 1 Feb 2006 04:35 AM

    As I said in the previous blog, HTML code cached in a single document, regenerated on a daily basis OR on an user action, seems to me much better.

    Just a @dblookup to grab the code, no administration problem.

    • avatar
    • Richard Shergold
    • Wed 1 Feb 2006 04:47 AM

    Would a better solution be to use @TextToTime("Today") in the view selection formula rather than @Today. I seem to remember that being a tip for these sorts of views? If that performs eficiently then there's no need for the update agent at all?

    • avatar
    • Richard Shergold
    • Wed 1 Feb 2006 04:48 AM

    See {Link}

  1. @Richard, @TextToTime works. But it does require a view refresh to update it properly.

    Jake yesterday mentioned that he stopped when he considered something a hack. Well running an overnight agent to update a view is something that I would consider a hack.

    Why should the design have to be modified everytime? Surely this is quite a major hack.

    • avatar
    • Richard Shergold
    • Wed 1 Feb 2006 05:14 AM

    @Dragon. I'm not so sure that a manual view refresh is required on the @TextToTime view - maybe it used to be but not in R6? I just create a view and then created a new document. When I saved the doc it appeared automatically in the view - no manual refresh was required. Or am I missing something?

    I agree about the overnight agent updating the selection formula - I really think the template problem is a nasty one.

    • avatar
    • Richard C
    • Wed 1 Feb 2006 05:47 AM

    Another way of doing things ...

    1) Upon saving an Event, use a wqs agent to set the isNextWeeksEvent field to be 0 or 1.

    2) Have the MeetingsNextWeek view's selection criteria of being isNextWeeksEvent = 1

    3) Run a scheduled (daily) agent to reset the isNextWeeksEvent field value if it needs to be changed.

    • avatar
    • Jake
    • Wed 1 Feb 2006 06:51 AM

    Dragon. Yeah, it's still a hack, but it's the lesser of two evils. As I love to continually remind you all - I find developing decent websites with Domino is one big hack. C'est la vie.

    I'd prefer to consider using the @TextToTime("Today") solution. Does anybody know if this is correct. Not that I don't trust SearchDomino.com or anything, but I'd like to see some concrete proof this is any better than using @Today. Seems like the same thing to me, in terms of the view index.

  2. Changing selection formulae programmatically is a good solution if there is just one view (or a small number of views) and if the database is not that big. In the case of my bloggregator database, neither one of those is true. I have three date ranges that I display: one day, two days, and seven days; and I generate those displays for all RSS feeds that I pull in, or for any one RSS feed -- and I have upwards of 200 feeds coming in and a total of more than 120,000 documents in the database at present time.

    I could have gone with three "all" views plus three views per feed, but the massive number of view rebuilds that would occur every night when the selection formulae are updated would scare me even if the database were a lot smaller, so there was no way I was going to go this way.

    In theory, I could use just six views -- three uncategorized, and three categorized by feed, and adjust the selection formulae nightly. Chaanging the selection formulae would result in complete rebuilds of all six views every night, but with only six views this may not be too bad. There are two reasons why I didn't do this, though.

    The first reason I didn't do it is that -- hust as is the case with readernames fields -- single category view displays can get quite slow when there are thousands of documents that have to be skipped in order to locate the ones that are actually displayed, so I decided not to go that way. In my own use, the number of documents that would be selected in the seven day categorized view is only around 5000 at any given time, and that's below the threshold where performance issues tend to set in, so I could probably have used the views and show single category, but my goal was to build a behind-the-firewall re-aggregator that could scale to thousands of feeds, with potentially tens of thousands of current in a seven day span.

    The second reason that I didn't do it is that you can't control the order of view rebuilds. There's no guarantee that the order in which you update the selection formulae is the order in which the rebuilds will occur. Although the worst thing I can think of happening in my application if views were updated in an unusual order is that there could be a significant time lag during which many documents appear in the 1 Day\All view but aren't in the 1 Day views for their particular feed (or vice versa), it still seemed to me that making a major design feature dependent on the order of view rebuilds was a bad idea.

    So, I went with the folder approach. Or, I guess I should say the massively mulitplied folder approach ;-) I set up folders for All\1 Day, All\2 Days, and All\7 Days, and the code also sets up a 1 day, 2 day and 7 day folder for each individual feed that are added. As they are created, new documents get put in the 1, 2 and 7 day folders for their particular feed, and also in the 1, 2, and 7 day "all" folder. A nightly agent traverses the All\7 Days folder and removes documents as needed -- from that folder and also from the others as needed. The performance has been consistently good, the massive number of folders required has (so far) not been a problem, and while the agent takes quite a while to run the performance impact on the server while it is running is barely noticeable.

    • avatar
    • Richard Shergold
    • Wed 1 Feb 2006 07:39 AM

    Jake. I think with the @TextToTime method the view needs to be updated each day to make it work - i.e to pick up the correct date again. So I suppose a nightly agent that just refreshes the view would do this? My test worked because the view has a selection of creation date >= @textToTime("Today"). I guess this view will be incorrect tomorrow unless the index is refreshed (i.e it will still have todays date in)

    • avatar
    • PaulG
    • Wed 1 Feb 2006 07:57 AM

    Someone shoot me down - but what's wrong with @transform?

    Build the view - categorised by date. Then on the form doing the lookup, set your 7 dates in a list and use @transform like so:

    @transform(date_list; "X"; @Dblookup("";"";"eventsview"; X; 2))

    The results are already sorted because you make sure the date list is sorted to begin with.

    Is this crazy talk?

    • avatar
    • EdwinL
    • Wed 1 Feb 2006 08:21 AM

    Hi Jake,

    i seem to remember that any functionality you placed under the views "Form Formula" and the "Helprequest" would be cleared after altering the view via LScript version 6.x .

  3. Jake,

    I know I'm coming late to the party... but if the right solution (and by right I mean, works best with least code) is a SQL Query, has there been any discussion with the client about backing their domino websites with something more robust for data? I know that means a lot more work than any solution identified, but where there is one clear need for this kind of data flexibility, there may be more. Perhaps their Admin or System Architect would be receptive to expanding the capabilities and you'd have yourself some work updating their templates. :-)

    A tangent, but I try to consider all the possibilities when I start running into too many difficulites with a particular approach. While more involved, it seems cleaner.

    Best of luck in any case!


    • avatar
    • Tom
    • Wed 1 Feb 2006 02:59 PM

    Instead of @today try this out @TextToTime(Today)

    found this somewhere on the net but haven't used it yet. The example was

    SELECT ((@TextToTime(Today) - CREATIONDATE)/86400)

    cheers Tom

    • avatar
    • Glen
    • Wed 1 Feb 2006 03:18 PM

    This document from Lotus Support discusses the various options: "Time/Date Views in Notes: What Are the Options?" (click on "website" above to see it)

    I have used @Today in views (albeit in the Notes client) without any unacceptable performance hit or delay to the user. Other options are listed in the document.

    • avatar
    • Keiser
    • Thu 2 Feb 2006 03:22 AM

    Yeah, Jerry is right. You could even do it in PHP !

    • avatar
    • Ed Lee
    • Thu 2 Feb 2006 04:12 AM


    Could not just use @TextToTime("Today") in your view selection and then create program document to do an updall on that specific view?

    It's a simple solution that is easy to maintain/


    • avatar
    • Jake
    • Thu 2 Feb 2006 04:31 AM

    Keiser. I wish I could have used LAMP, but the customer *wanted* a Domino solution.

    Ed. I'm confused now. I'm going to have to play with this one and see if it can eliminate the need for nightly agents.

    • avatar
    • Ed Lee
    • Thu 2 Feb 2006 05:27 AM

    Hi Jake,

    Create a Program document that runs Updall -- use this method to schedule Updall to run at particular times.


    load updall database.nsf -T "viewname"

    Then schedule it for whenever you want

    Hope this helps

  4. Just throwing another idea into the pool...

    First off, is this an intranet app? If so, a browser standard should allow you to do XML client side (in theory). If so, this could be relatively simple...

    What I'm getting at is -?ReadViewEntries- and the optional URL arguments that were added for the iNotes team (without these, don't ask me how they'd have accomplished the Calendar portion of iNotes). OK, by now it's Domino Web Access instead of iNotes...I'll catch up. :-)

    The ones in question are:

    - KeyType

    - StartKey

    - UntilKey

    With KeyType, you can tell Domino to treat the StartKey and UntilKey as dates (check "URL Commands" in Designer Help, or the link above for "website"). Here's a snippet:


    If &KeyType=time is specified, then &StartKey and &UntilKey may be ISO date time values. For example, you might use the time value 20020101T140000,00Z to represent 9:00AM Eastern standard Time in Coordinated Universal Time (UTC) or Greenwich Mean Time (GMT), or you might use the time value 20020101T090000,00-05 to represent the same date and time in local time plus offset.


    Using those, and client side XML transforms, all you'd need is a view sorted by EventDate. Avoid the daily agent, avoid numerous folders, and avoid @Today selection formulas. Then again, I'm probably missing something. :-)

    Of course key factor is whether or not requirements allow you to code for only AJAX capable browsers...

  5. Heres mine.

    Column 1, sorted Categorized

    DT := @Date(EventDate);








    Column 2, Sorted


    Then just @dblookup on todays date.

    The big test for performance is the frequency of documents changing VS the data read frequency.

    An expensive view action can make sense when the data is not changing often and retrieved value is used often.

Your Comments


About This Page

Written by Jake Howlett on Wed 1 Feb 2006

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 »


Here are the external links posted on the same day.

More links are available in the archive »

More Content