wissel.net

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:
OptionPublic
OptionDeclare

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
        Exitsub
    EndIf
   
    Set dbDir = s. Getdbdirectory(Server )
   
    Set db = dbDir. Getfirstdatabase(TEMPLATE_CANDIDATE )

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

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

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)
        EndIf 
    EndForAll
EndSub

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
EndFunction
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