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:
Option Public
Option Declare

Sub Initialize
    Dim s As New NotesSession
    Dim server As String
    Dim dbDir As NotesDbDirectory
    Dim reportDB As NotesDatabase
    Dim db As NotesDatabase
    Set reportDB = s. Currentdatabase
    server = reportDB. Server
    server = InputBox$ ( "Select Server to scan", "Server selection", server )
    If Trim (server ) = "" Then
        Exit sub
    End If
    Set dbDir = s. Getdbdirectory (Server )
    Set db = dbDir. Getfirstdatabase (TEMPLATE_CANDIDATE )

    Do Until db Is Nothing
        Call ProcessDB (reportDB, db )
        Set db = dbDir. Getnextdatabase ( )
End Sub

Sub ProcessDB (reportDB As NotesDatabase, db As NotesDatabase )
    On Error GoTo err_ProcessDB
    If Not db. Isopen Then
        Call db. Open ( "", "" )
        If Not db. Isopen Then
            Print "Can't open " & db. Title
            Exit sub
        End If
    End If
    Print "Processing " & db. Title 
    Call CreateViewAdjusterForms (reportDB, db )
    Exit sub   
    Print Error$
    Resume exit_ProcessDB  
End Sub

Sub CreateViewAdjusterForms (reportDB As NotesDatabase, db As NotesDatabase )
    Dim doc As NotesDocument
    Dim v As NotesView
    Dim selectionFormula As String
    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 )
        End If 
    End ForAll
End Sub

Function isCriticalProblem (Formula As String ) As Boolean
    Dim work As String
    work = LCase$ (Formula )
    isCriticalProblem = InStr (work, "now" ) <> 0 Or _
    InStr (work, "today" ) <> 0 Or _
    InStr (work, "tomorrow" ) <> 0 Or _
    InStr (work, "yesterday" ) <> 0
End Function
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