Usability - Productivity - Business - The web - Singapore & Twins

Managing @Today in view selection formulas

Using @Yesterday, @Today, @Now, @Tomorrow in Notes view selection formulas is a bad idea (but you know that). But if your application depends on such a selection? The solution is to update your database design automatically with a static date. There are a few caveats:
  • You must be careful about date formats, since you don't want code to depend on a locale setting. So @Date(2012;12;31) is your save option
  • After updating a view you want to replicate it across all servers to be sure you don't get design conflicts
  • When users use a local replica of your database you want to check the validity of your selection formula in the queryViewOpen event and eventually adjust it there. This would require you control database be available locally (code not shown here)
  • Extra care is needed if you have views with duplicate names in your database
I designed a solution that uses a control database with one form, one view and a bit of LotusScript. First create a form with the following fields: Server, Database, ViewName, SelectionFormula (all text, data input), lastRun (computedWhenComposed with formula lastRun and finally CurrentSelectionFormula, Text, Computed. Use this formula:
ReplaceStringToday := "@Date("+@Text(@Year(@Today))+";"+@Text(@Month(@Today))+";"+@Text(@Day(@Today))+")";
ReplaceStringYesterday := "@Date("+@Text(@Year(@Yesterday))+";"+@Text(@Month(@Yesterday))+";"+@Text(@Day(@Yesterday))+")";
ReplaceStringTomorrow := "@Date("+@Text(@Year(@Tomorrow))+";"+@Text(@Month(@Tomorrow))+";"+@Text(@Day(@Tomorrow))+")";
@ReplaceSubstring(SelectionFormula; "@Today":"@Now":"@Yesterday":"@Tomorrow"; ReplaceStringToday:ReplaceStringToday:ReplaceStringYesterday:ReplaceStringTomorrow)

Everytime that document gets refreshed the field will reflect how the selection formula currently should look like. Then create a view with Server, Database, ViewName -> all 3 columns sorted (ViewName sorting is optional). I called mine ViewsToAdjust. Next step is to populate the documents with views that actually have time related selection formulas. I use this agent for it:

Sub Initialize
    Dim s AsNewNotesSession
    Dim server AsString
    Dim dbDir AsNotesDbDirectory
    Dim reportDB AsNotesDatabase
    Dim db AsNotesDatabase
    Set reportDB = s. Currentdatabase
    server = reportDB. Server
    server = InputBox$("Select Server to scan", "Server selection", server )
    IfTrim(server ) = ""Then
    Set dbDir = s. Getdbdirectory(Server )
    Set db = dbDir. Getfirstdatabase(TEMPLATE_CANDIDATE )

    DoUntil db IsNothing
        Call ProcessDB (reportDB, db )
        Set db = dbDir. Getnextdatabase()

Sub ProcessDB (reportDB AsNotesDatabase, db AsNotesDatabase)
    OnErrorGoTo err_ProcessDB
    IfNot db. IsopenThen
        Call db. Open("", "")
        IfNot db. IsopenThen
            Print"Can't open " & db. Title
    Print"Processing " & db. Title 
    Call CreateViewAdjusterForms (reportDB, db )
    Resume exit_ProcessDB  

Sub CreateViewAdjusterForms (reportDB AsNotesDatabase, db AsNotesDatabase)
    Dim doc AsNotesDocument
    Dim v AsNotesView
    Dim selectionFormula AsString
    ForAll curView In db. Views
        Set v = curView
        SelectionFormula = v. SelectionFormula
        If isCriticalProblem (Formula )Then
            Set doc = reportDB. Createdocument()
            doc. form = "ViewAdjuster"
            doc. server = db. Server
            doc. database = db. Filepath
            doc. viewname = v. Name
            doc. SelectionFormula = selectionFormula
            Call doc. Computewithform(true, false)
            Call doc. Save(true, True)

Function isCriticalProblem (Formula AsString)AsBoolean
    Dim work AsString
    work = LCase$(Formula )
    isCriticalProblem = InStr(work, "now") <> 0Or_
    InStr(work, "today") <> 0Or _
    InStr(work, "tomorrow") <> 0Or_
    InStr(work, "yesterday") <> 0
Once you have the formulas you want to review them if the are really critical and that the revised formula actually will work. Then design a scheduled agent that checks those views. I run it hourly and on all servers (you could use the lastRun date to only check databases that haven't been processed today.To replicate the changed databases I use the catalog.nsf, so you need to make sure that your catalog task is running properly.

Sub Initialize
    Dim s AsNewNotesSession
    Dim db AsNotesDatabase
    Dim catalog AsNotesDatabase
    Dim v AsNotesView
    Dim dcol AsNotesDocumentCollection
    Dim serverName AsString
    Dim targetDB AsNotesDatabase
    Dim doc AsNotesDocument
    Dim dbName AsString
    Dim dbChanged AsBoolean
    Set db = s. Currentdatabase
    Set catalog = s. getDatabase(db. server, "catalog.nsf")
    Set v = db. Getview("ViewsToAdjust")
    servername = db. Server
    Set dcol = v. Getalldocumentsbykey(servername, true)
    Set doc = dcol. Getfirstdocument()
    dbChanged = false
    DoUntil doc IsNothing
        'Get the database if we don't have it already
        If dbname <> doc. Getitemvalue("database")(0)Then
            IfNot targetDB IsNothingAnd dbChanged Then
                Call ReplicateWithAllServers (s, targetDB, catalog )
            dbname = doc. Getitemvalue("database")(0)
            Set targetDB = s. Getdatabase(serverName, dbname )
            dbChanged = false
        'Open if closed
        IfNot targetDB. IsopenThen
            Call targetDB. Open("", "")
        'Only process if open worked
        If targetDB. IsopenThen
            dbChanged = updateOneView (doc,targetDB, dbChanged )           
        Set doc = dcol. Getnextdocument(doc )

Function updateOneView (doc AsNotesDocument,targetDB AsNotesDatabase, previousChange AsBoolean)
    Dim viewName AsString
    Dim v AsNotesView
    Dim newFormula AsString
    Dim formulaInView AsString
    OnErrorGoTo Err_updateOneView
    'We start with the change status from before
    updateOneView = previousChange
    viewName = doc. getItemValue("ViewName")(0)
    Set v = targetDB. getView(viewName )
    formulaInView = v. Selectionformula
    Call doc. Computewithform(true, true)
    newFormula = doc. Getitemvalue("CurrentSelectionFormula")(0)
    'Now check if update is needed
    If formulaInView <> newFormula Then
        updateOneView = true
        v. Selectionformula = newFormula
        Call doc. Replaceitemvalue("LastRun", date)
        Call doc. Save(true, true)
    'TODO: add error reporting here!
    Resume Exit_updateOneView

Sub ReplicateWithAllServers (s AsNotesSession, targetDB AsNotesDatabase, catalog AsNotesDatabase)
    Dim v AsNotesView
    Dim vec AsNotesViewEntryCollection
    Dim ve AsNotesViewEntry
    Dim TargetServer AsString
    Dim sourceName AsNotesName
    Dim commandString AsString
    Set sourceName = NewNotesName(targetDB. Server)
    Set v = catalog. Getview("($ReplicaID)")
    Set vec = v. Getallentriesbykey(targetDB. Replicaid, true)
    Set ve = vec. Getfirstentry()
    DoUntil ve IsNothing
        targetServer = ve. Columnvalues(1)
        If targetServer <> sourceName. CommonThen
            commandString = "Replicate " & targetServer & " " & targetDB. Filepath & " UPDATE_COLL"
            Call s. Sendconsolecommand(targetDB. Server, commandString )
        Set ve = vec. Getnextentry(ve )

As usual YMMV

Posted by on 30 November 2012 | Comments (0) | categories: Show-N-Tell Thursday


  1. No comments yet, be the first to comment