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

By Date: November 2012

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.

Read more

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