Tag: Lotus Notes

I use many lists in my programs. They are handy for keeping it tidy and configurable. I surely could put a constant in its own identificator and sometimes I do. But what if I need two or more similar constants? Lists are definitely the best choice for holding column numbers and for exchanging fields between Office and Notes documents.

Sometimes double lists would be better than normal lists. Example: an agent for importing some fields from an Excel book with many sheets into a Notes document using a double list for declaring the import structure. It should work this way:

Dim xlImport List2 As String
xlImport( "Sheet1", "report" ) = "Title"
xlImport( "Sheet2", "date3" ) = "Date"
xlImport( "Sheet1", "dept" ) = "Department"

With such a structure one could define the import as a simple forall loop:

Dim xlSheet As Variant
Dim xlRange As Variant
Forall f In xlImport
  xlSheet = xlBook.Worksheets( Listtag( f, 1 ) )
  xlRange = xlSheet.Range( Listtag( f, 2 ) )
  Call notesDocument.ReplaceItemValue( f, xlRange.Value )
End Forall

Unfortunately double lists don’t exist in LotusScript, so there is no chance for that code to run in a Notes system.

Here is my implementation of double lists, in its own “DoubleLists” script library. Its use is very similar to the one described earlier, but this is working code.

Dim xlImport List As Variant '= List As String
List2( xlImport, "Sheet1", "report" ) = "Title"
List2( xlImport, "Sheet2", "date3" ) = "Date"
List2( xlImport, "Sheet1", "dept" ) = "Department"

Forall sheet In xlImport
  xlSheet = xlBook.Worksheets( Listtag( sheet ) )
  Forall f In sheet
    xlRange = xlSheet.Range( Listtag( f ) )
    Call notesDocument.ReplaceItemValue( f, xlRange.Value )
  End Forall
End Forall

The important thing here is the flexibility of this approach, something similar to the Prolog programming language. The import machine is known and fixed, but by means of a simple set of declarations it can import any book. And adding a new field to import is just a matter of copy and paste a declaration and adjust it, as well as stopping the import of a field is just a matter of commenting out a declaration. And the program gets self documented. These are the reasons why I use lists and double lists as much as possible.

'DoubleLists:

Option Public
Option Declare

Use "LsConst.lss"

Property Set List2( aList As Variant, tag1 As String, tag2 As String ) As Variant
%INCLUDE "error_handling"

    If Iselement( aList( tag1 ) ) Then
        Dim aux1 As Variant '= List
        aux1 = aList( tag1 )
        Select Case Datatype( List2 )
        Case V_DISPATCH, V_ERROR, V_IUNKNOWN, V_LSOBJ, V_PRODOBJ
            Set aux1( tag2 ) = List2
        Case Else
            aux1( tag2 ) = List2
        End Select
        aList( tag1 ) = aux1
    Else
        Dim aux2 List As Variant
        Select Case Datatype( List2 )
        Case V_DISPATCH, V_ERROR, V_IUNKNOWN, V_LSOBJ, V_PRODOBJ
            Set aux2( tag2 ) = List2
        Case Else
            aux2( tag2 ) = List2
        End Select
        aList( tag1 ) = aux2
    End If
End Property

Property Get List2( aList As Variant, tag1 As String, tag2 As String ) As Variant
%INCLUDE "error_handling"

    If Iselement( aList( tag1 ) ) Then
        Dim aux As Variant
        aux = aList( tag1 )
        If Iselement( aux( tag2 ) ) Then
            Select Case Datatype( aux( tag2 ) )
            Case V_DISPATCH, V_ERROR, V_IUNKNOWN, V_LSOBJ, V_PRODOBJ
                Set List2 = aux( tag2 )
            Case Else
                List2 = aux( tag2 )
            End Select
        Else
            List2 = Null
        End If
    Else
        List2 = Null
    End If
End Property

The error_handling.lss file I described in a previous post needs to be available on any machine where the script using it will execute. This is due to a weird behavior of the Notes engine when processing the %Include instruction: The inclusion is done at execution time if executing on a client and at compilation time if executing on a server.

It is certainly possible to distribute a file to all the users, explaining in which folder to copy it, but it is much simpler to have the same database that use it to install it whenever needed.

The HelpAbout document is a good place for storing a file. After attaching it, a HideWhen formula will prevent it from showing up to the user. Then a simple (Install error_handling.lss) agent will extract the file to the proper folder (if the folder doesn’t already have one). And the formula @Command( [RunAgent]; "(Install error_handling.lss)" ) in the PostOpen event of the Database Script library will run the agent each time the user opens the database.

Here is the code for the agent:

'Install error_handling.lss:

Option Public
Option Declare

Use "RegistryAccess"

Sub Initialize
    On Error Goto HandleError
    Goto EnterProc

HandleError:
    Error Err, Getthreadinfo( 1 ) & " : " & Erl & Chr$( 10 ) & Error$

EnterProc:

    Dim install As String
    install = "error_handling.lss"

    Dim notesFolder As String
    notesFolder = RegQueryValue( "HKEY_LOCAL_MACHINE", "SoftwareLotusNotes", "Path" )
    Dim path As String
    path = notesFolder & install
    If Dir$( path ) <> "" Then
        ' exit on library already installed
        Exit Sub
    End If

    Dim s As New NotesSession
    Dim db As NotesDatabase
    Set db = s.CurrentDatabase

    Dim d As notesdocument
    Set d = GetHelpAboutDocument( db )
    If d Is Nothing Then
        ' exit on library not available in the database
        Msgbox "The library " & install & " must be installed" & Chr( 10 ) _
        & "It's not in the database" & Chr( 10 ) _
        & "Contact the administrator of this database"
        Exit Sub
    End If

    Call ExtractAttachment( d, install, path )
    If Dir( path ) = "" Then
        ' exit on file not created
        Msgbox "The library " & install & " must be installed" & Chr( 10 ) _
        & "The file " & path & " couldn't be created" & Chr( 10 ) _
        & "Contact the administrator of this database"
        Exit Sub
    End If
    Print "Installed library " & install

    install = "error_handling_ui.lss"
    path = notesFolder & install
    Call ExtractAttachment( d, install, path )
    If Dir( path ) = "" Then
        ' exit on file not created
        Msgbox "The library " & install & " must be installed" & Chr( 10 ) _
        & "The file " & path & " couldn't be created" & Chr( 10 ) _
        & "Contact the administrator of this database"
        Exit Sub
    End If
    Print "Installed library " & install
End Sub

Function GetHelpAboutDocument( db As NotesDatabase ) As NotesDocument
    On Error Goto HandleError
    Goto EnterProc

HandleError:
    Error Err, Getthreadinfo( 1 ) & " : " & Erl & Chr$( 10 ) & Error$

EnterProc:

    Dim nc As NotesNoteCollection
    Set nc = db.CreateNoteCollection( False )
    nc.SelectHelpAbout = True
    Call nc.BuildCollection
    Dim nid As String
    nid = nc.GetFirstNoteId

    If nid <> "" Then
        Set GetHelpAboutDocument = db.GetDocumentByID( nid )
    Else
        Set GetHelpAboutDocument = Nothing
    End If
End Function

Sub ExtractAttachment( d As NotesDocument, filename As String, path As String )
    On Error Goto HandleError
    Goto EnterProc

HandleError:
    Error Err, Getthreadinfo( 1 ) & " : " & Erl & Chr$( 10 ) & Error$

EnterProc:

    If Not d.HasEmbedded Then Exit Sub

    Dim embedded As NotesEmbeddedObject
    Set embedded = d.GetAttachment( filename )
    If embedded Is Nothing Then Exit Sub

    Call embedded.ExtractFile( path )
End Sub

In my apps I use many embedded views that show a single category. They are handy, but lack powerful features exposed views have. Sorting by a different column is one of them.

Clearly it’s not a problem for embedded views as it is for categorized views. Notes cannot properly sort a categorized view by a different column, because the feature is poorly implemented in that case.

So the hack should allow for a sorting that left the categorization in place. For backward compatibility, it could be a switchable feature, and the switch could be a special starting for the view comment, like “[1]”.