Category Archives: Lotusscript

A few days ago, a question was asked on StackOverflow about how to export the content of a Notes view to Excel. The caveat was that some columns contained multiple values, but not on all documents.

To solve this, I wrote a Lotusscript class that will export view data as either CSV or as an HTML table, both can then be saved to a file and opened in Excel. I am posting the code below. Enjoy!

%REM
    Agent View Export
    Created Mar 27, 2013 by Karl-Henry Martinsson
    Description: Code to export a specified view as CSV.
    Copyright (c) 2013 by Karl-Henry Martinsson
    This code is distributed under the terms of 
    the Apache Licence Version 2. 
    See http://www.apache.org/licenses/LICENSE-2.0.txt
%END REM

Option Public
Option Declare

Class RowData
    Public column List As String

    Public Sub New()
    End Sub

    Public Sub SetColumnHeader(view As NotesView)
        Dim viewcolumn As NotesViewColumn
        Dim cnt As Integer
        ForAll vc In view.Columns
            Set viewcolumn = vc
            column(CStr(cnt)) = viewcolumn.Title 
            cnt = cnt + 1
        End Forall  
    End Sub

    Public Sub SetColumnValues(values As Variant)
        Dim cnt As Integer
        Dim tmp As String 
        ForAll v In values
            If IsArray(v) Then
                ForAll c In v
                    tmp = tmp + c + Chr$(13)
                End ForAll
                column(CStr(cnt)) = Left$(tmp,Len(tmp)-1)
            Else
                column(CStr(cnt)) = v 
            End If
            cnt = cnt + 1
        End ForAll          
    End Sub
End Class

Class CSVData
    Private row List As RowData
    Private rowcnt As Long

    %REM
        Function New
        Description: Open the view and read view data 
        into a list of RowData objects.
    %END REM    
    Public Sub New(server As String, database As String, viewname As String)
        Dim db As NotesDatabase
        Dim view As NotesView
        Dim col As NotesViewEntryCollection
        Dim entry As NotesViewEntry
        Dim colcnt As Integer

        Set db = New NotesDatabase(server, database)
        If db Is Nothing Then
            MsgBox "Could not open " + database + " on " + server,16,"Error" 
            Exit Sub
        End If
        Set view = db.GetView(viewname)
        If view Is Nothing Then
            MsgBox "Could not access view " + viewname + ".",16,"Error" 
            Exit Sub
        End If
        Set col = view.AllEntries()
        rowcnt = 0
        Set entry = col.GetFirstEntry()
        Set row("Header") = New RowData()
        Call row("Header").SetColumnHeader(view)
        Do Until entry Is Nothing
            rowcnt = rowcnt + 1
            Set row(CStr(rowcnt)) = New RowData()
            Call row(CStr(rowcnt)).SetColumnValues(entry.ColumnValues)
            Set entry = col.GetNextEntry(entry) 
        Loop
    End Sub

    %REM
        Function CSVArray
        Description: Returns a string array of CSV data by row
    %END REM
    Public Function CSVArray() As Variant
        Dim rowarray() As String 
        Dim textrow As String
        Dim cnt As Long
        ReDim rowarray(rowcnt) As String

        ForAll r In row
            textrow = ""
            ForAll h In r.column 
                textrow = textrow + |"| + Replace(h,Chr$(13),"\n") + |",|
            End ForAll
            rowarray(cnt) = Left$(textrow,Len(textrow)-1)
            cnt = cnt + 1
        End ForAll  
        CSVArray = rowarray
    End Function

    %REM
        Function HTMLArray
        Description: Returns a string array of HTML data by row
    %END REM
Public Function HTMLArray() As Variant
        Dim rowarray() As String 
        Dim textrow As String
        Dim cnt As Long
        ReDim rowarray(rowcnt) As String

        ForAll r In row
            textrow = ""
            ForAll h In r.column 
                textrow = textrow + |<td>| + Replace(h,Chr$(13),"<br>") + |</td>|
            End ForAll
            rowarray(cnt) = "<tr>" + textrow + "</tr>"
            cnt = cnt + 1
        End ForAll  
        HTMLArray = rowarray
    End Function

End Class

Here is an example of how to use the class:

Sub Initialize
    Dim csv As CSVData
    Dim outfile As String

    Set csv = New CSVData("DominoServer/YourDomain", "names.nsf", "People\By Last Name")
    '*** Create CSV file from view
    outfile = "c:\ExcelExportTest.csv"
    Open outfile For Output As #1
    ForAll row In csv.CSVArray()
        Print #1, row
    End ForAll
    Close #1
    '*** Create HTML table and save as .xls to open in Excel
    outfile = "c:\ExcelExportTest.xls"
    Open outfile For Output As #2
    Print #2, "<table>"
    ForAll row In csv.HTMLArray()
        Print #2, row
    End ForAll
    Print #2, "</table>"
    Close #2
End Sub

Welcome to the third part of this series of articles about how to write better code for the Notes/Domino platform. In part 1 I wrote about creating your forms in a way so you get an instant idea of how they work (what is hidden, computed-for-display fields, etc) and in part 2 the subject had moved to Lotusscript, more specifically variable names and comments.

As already mentioned in that last article (as well as in some of the comments), breaking out code into functions is a great way to make code easier to read as well as dramatically easier to maintain. That is what I will focus on in this article.

Functions

In Lotusscript, the functions are declared the same way as in Visual Basic:

Function YourFunctionName(argument As DataType) As ReturnDataType
    YourFunctionName = ReturnValue
End Function

You can have any number of arguments, from none to many. However, it is often suggested to keep the number of arguments to a minimum. I try to not use more than three arguments, unless there is no way around it. If you need to send a lot of information into a function, use an array or a custom data type instead. Then it is easier to change the arguments later, without changing the signature of the function. I have occasionally seen that cause issues in nested script libraries. Also, with many arguments the function declaration will probably not fit on your screen, unless you wrap the line. I try to always keep code visible in the editor, as it is easy to miss things when you have to scroll sideways to view code.

So let’s take a look at one of the code samples in my last article, the one where we call names.nsf to get the email address for the current user. Let’s make this a function (like Kenneth Axi suggests in his comment), but we will make it a more generic function, returning the email address for any user present in address book, not just current user.

Here is the original code:

key = session.CommonUsername
Set view = db.GetView("People")
Set doc = view.GetDocumentByKey(key)
emailaddress = doc.GetItemValue("InternetAddress")

Below is how I would write the function. We will talk more about error handling later, but you want to make sure to always check return values before you use them. The function takes one argument, the name of the user we want the email address for, and returns the email address, or blank if no user/address was found. The username must be in the same format as the names are being displayed in the view we doing the lookup in.

Function getEmailAddress(username As String) As String
    Dim personname As NotesName 
    Dim nabdb As NotesDatabase
    Dim nabview As NotesView
    Dim persondoc As NotesDocument

    If username = "" Then
        MsgBox "Username is empty. Exiting.",,"No Value"
        Exit Function
    End If
    '*** Create NotesName object and get CommonName part.
    '*** We do this so username argument can be in any format.
    Set personname = New NotesName(username)

    '*** Use Domino Directory on main server
    Set nabdb = New NotesDatabase("Domino1/MyDomain","names.nsf")
    If nabdb Is Nothing Then
        MsgBox "Could not open names.nsf. Exiting.",,"Error"
        Exit Function
    End If

    Set nabview = nabdb.GetView("PeopleByFirstName")
    If nabview Is Nothing Then
        MsgBox "Could not find view 'PeopleByFirstName'. Exiting.",,"Error"
        Exit Function
    End If

    Set persondoc = nabview.GetDocumentByKey(personname.Common)
    If persondoc Is Nothing Then	
        getEmailAddress = ""	  ' Did not find person, return blank value
    End If
    getEmailAddress = persondoc.GetItemValue("InternetAddress")(0)
End Function

You may wonder why I am creating a NotesName object and not just using the name passed as argument directly? That will allow me to get the Common Name of the name, no matter in what format it is passed. You should always check the values of any arguments passed to your functions to make sure you can handle them and that they contain valid data. In this case I make sure the username is not empty, but for a string containing a date I would use the IsDate() function to test that the value is a valid date-time.

Now we can call the function like this:

MsgBox getEmailAddress("Karl-Henry Martinsson")
MsgBox getEmailAddress("Karl-Henry Martinsson/MyDomain")
MsgBox getEmailAddress("CN=Karl-Henry Martinsson/O=MyDomain")

Beautiful, isn’t it?

How to name functions

This brings us to the naming convention for functions. For functions, I use CamelCase, as that makes it much easier to see what the function does. I have to admit, I am not always consistent with get and set, I sometimes capitalize the first letter, sometimes not. Using CamelCase for functions and lowercase for variables makes it easier for me to read my code later, but you should do what you are comfortable with, and what works for you.

The actual name of the function should describe what it does, without being too long or too short. A well named function will by itself document what it does when you see it in your code later. The function we just created above could have been called getEmail() or getEmailAddressForUserFromCentralServer(). The first is a little too short — you don’t know if it is getting a mail address or an actual email document– while the second is too long.

Script Libraries

Consider creating script libraries where you store commonly used functions. I have a number of script libraries, containing different sets of functions, and when I need certain functionality in a new application, I just copy one or more of them into that application. I name the script library in a way so I know what functions are in each one of them:

I group functions into separate script libraries, making it easy to copy just certain functionality to a new application.

There are many ways to name your script libraries, and I know some developers who use very complicated elaborate naming systems, where they can see at a glance if the script library uses back-end or front-end functions/classes, etc. I prefer a slightly simpler approach.

So let’s look at a real life code example, from one of my production applications. This is an agent, called from a browser, that generates some charts and other information, based on the URL parameters passed to the agent.

Thanks to the descriptive function names, hardly any comments are needed. The only comment in this code segment clarifies why we are doing what we do. Obviously the code is much more complex than what you see above, but it will give you an idea of how you can make your code easy to review and maintain.

In the next article I will talk about classes and object oriented Lotusscript.

We already talked about how to make your forms and views easier to maintain. But in most Notes/Domino applications, a majority of the code is written in Lotusscript (at least for traditional applications, XPages are outside the scope of this discussion for now).

It is not hard to write easy to read Lotusscript code. Just follow some simple rules. As always, there are more than one way to do things, but this is how I do it (most of the time). I found that this works for me.

Variables

• Always use Option Declare (or Option Explicit) in all your code. This will prevent you from getting errors later when (not if!) you spell a variable wrong. Option Declare forces you to declare all variables before you use them. It also helps you by warning if a function or method is called with a variable of the wrong data type. Most of your bugs can be avoided using Option Declare. Use it!
• Give your variables meaningful names. There is no need to abbreviate variables and/or give them cryptical names, or  you will just spell them wrong later. Use a naming system that makes sense to you. As I mentioned in my previous post, I avoid Hungarian notation, it just makes the variables hard to read. Since Lotusscript is a strongly typed language, the compiler is taking care of data types. I however do use some indicators for certain variables. For the few global variables I use, I do prefix them with g_, as I then can give local variables a similar name. By giving your variables meaningful names, the code also becomes largely self-documenting.
• Be consistent in how you capitalize your variables. I normally write all variables in lower case only, but that is my personal preference. That way they are easy to distinguish from built-in Lotusscript functions and properties of objects. Some programmers prefer some version of CamelCase, which is fine. I occasionally use that, especially in Javascript.
• Be consistent in how you name related variables. If you have a variable named startpos to indicate the beginning position of a string within another string, name the other variable endpos, not posend. Think through all variables you need so you give them good names. The variables posstart and posend are not as easy to read and understand as startpos and endpos. Of course, using CamelCase, posStart and posEnd are much easier to decypher.
• Use the same/similar naming convention for the Domino objects as IBM uses in the online help. Use ws, uidoc, session, db, doc, etc. If you need more than one object of any type, e.g. NotesDatabase, name them in a consistent way. I always use thisdb for the database the code is executing in, nabdb for names.nsf, archivedb for (you guessed it!) an archive database, etc.
• Declare the variables at the beginning of the code/subroutine/function. Don’t declare them throughout the code, this makes it much harder to find them later, when you perhaps need to see what data type they are.
• Declare the variables in a logical order. I always group the variables by type. First the UI classes, then backend classes, then any custom classes/data types, and finally other variables. Within each group, I declare them in the order I am using them. The benefit of this is that you get an idea of where to look for the variables in your function/subroutine.
• Use Option Declare. Yes, this needs to be repeated. Way too many (especially new) programmers forget this, or don’t know about this.

Here is a typical example of how I would declare variables in one of my applications:

Dim ws As New NotesUIWorkspace
Dim uidoc As NotesUIDocument
Dim session As New NotesSession
Dim thisdb As NotesDatabase
Dim nabdb As NotesDatabase
Dim nabview As NotesView
Dim persondoc As NotesDocument
Dim doc As NotesDocument
Dim username As String
Dim phonenumber As String
Dim employeenumber As Integer

As you can see, this function will do some kind of lookup in names.nsf and retrieve the name, phone number and employee number from the person document. It is then (probably) storing the values into the backend document of the currently open document. Just by declaring and naming variables in a logical way you can often figure out what the code will do further down.

If you declare variables that you end up not using, remove them. Since you now are a good programmer who is using Option Declare, you can very easily test if a variable is used by commenting it out. If you don’t get an error, you can remove it.

Comments

This brings us to the art of commenting. Yes, it is a little bit of an art, and I have seen way too much code written where the code is either overly commented (not very common), or hardly at all (very common). The art is to add just the right amount of comments.

There is an old saying among programmers: “if the code was hard to write, it should be hard to read”. But it may not be another programmer that have to look at the code and find a bug in it or update it with new functionality, it may very well be you. And if you have commented your code, you will not have to analyze every line of code, trying to figure out hwo you were thinking a few months or even years ago.

You want the comments to explay “why”, not “what” the goal of the code, not explain the individual lines of code. What I mean with that is that it does not make sense to have code like this:

'*** Set key variable to current user's name
key = session.CommonUsername
'*** Get the "People" view in database
Set view = db.GetView("People")
'*** Get person document in view based on key
Set doc = view.GetDocumentByKey(key)
'*** Get the email address from the field "InternetAddress"
emailaddress = doc.GetItemValue("InternetAddress")

The comments in the example above does not add anything to the code. This code below is much better, and also shorter:

'*** Get email address for current user from person document
key = session.CommonUsername
Set view = db.GetView("People")
Set doc = view.GetDocumentByKey(key)
emailaddress = doc.GetItemValue("InternetAddress")

This comment explains what we actually are attempting to do. In real life, you would probably break out this code into a separate function with a descriptive name. The example above is just for illustrative purposes. I will cover functions in a future article.

Comments are crucial when you use some kind of work-around, or when your variables or field names have been named something that is not clear. I have cases where a field was initially named for the data it would hold, but later it was changed to something totally different. Make a note of that in your code. Or if you have to use some more or less obscure work-around to get the code to work, it would be very annoying if you (or another programmer) later removes that line. A typical example is to perform a db.FTSearch() with a query that will not return anything to get an empty document collection for later use.

I use the following system for comments:

• Comments explaining one line of code or a command are put on the same line, with no stars, just an apostrophe.
• Comments explaining a section of code are put on their own line, and I start them with three stars to make them more visible and make it clear they are comments and not code.

Remember, comments are there to help you remember (or tell someone else) what is happening in the code.

Bringing it together

Let’s look at a real life example. This one is taken from one of the IBM developerWorks forums, where a user had some issues attaching files to a rich text field. After getting some help, he posted the code he finally got to work:

Sub Initialize
    Dim s As New NotesSession
    Dim ws As New NotesUIWorkspace
    Dim uidoc As NotesUIDocument 
    Dim filesys As String
    Dim FullPath As String
    Dim OUName As String
    Dim FileName As String
    Dim Path As string
    Dim db As NotesDatabase
    Dim view As NotesView
    Dim doc As NotesDocument
    Dim Object As NotesEmbeddedObject
    Dim IDFile As string

    Set uidoc = ws.CurrentDocument
    OUName = uidoc.FieldGetText( "OUName" )
    FileName = uidoc.FieldGetText( "FileName" )
    IDFile = uidoc.FieldGetText( "IDFile" )

    Set doc = uidoc.document
    Call uidoc.Save 
    Dim FullPath1 As Variant
    FullPath = doc.GetItemValue("FullPath")(0)
    Dim rtitem As New NotesRichTextItem (doc, "FileName")
    Set Object = rtitem.EmbedObject(EMBED_ATTACHMENT,"",IDFile,OUName)
    Call doc.Save(True,True)
End Sub

As you can see, variables are declared all over the place, some declared but never used, inconsistent named, and of course no comments.
Here is my cleaned up version of the same code, with comments added as well:

Sub Initialize
    Dim ws As New NotesUIWorkspace
    Dim uidoc As NotesUIDocument
    Dim doc As NotesDocument
    Dim rtitem As NotesRichTextItem 
    Dim filepath As String
    Dim filename As String

    '*** Save currenly open document
    Set uidoc = ws.CurrentDocument
    Call uidoc.Save   ' Save UI doc so backend doc get updated

    '*** Get updated backend document and read field values
    Set doc = uidoc.document
    filepath = doc.GetItemValue("FullPath")(0) ' Path must end with /
    filename = doc.GetItemValue("FileName")(0)

    '*** Create a new rich text field called 'Attachment' and save doc
    '*** Use GetFirstItem() if the field already exists in document
    Set rtitem = New NotesRichTextItem(doc, "Attachment")
    Call rtitem.EmbedObject(EMBED_ATTACHMENT,"", filepath + filename)
    Call doc.Save(True,True)

End Sub

I think this code is much easier to follow, cleaner and easier to maintain. This is the kind of code I would like to see if I came in to update or modify an existing Notes application. Wouldn’t you?

Update: Made some small changes and clarifications, and fixed some typos.

In a recent discussion thread in one of the forums on IBM developerWorks, the issue of which is faster, doc.GetItemValue() or viewentry.ColumnValues(). I decided to test this, using two Lotusscript agents, created to be as similar as possible, working against the same view of the same database.

First, here is Agent #1, using ColumnValues() to get the value we are looking for from the first column of the view. This agent took 66.3 seconds to run, and below you can see exactly how that time was spent:

And this is Agent #2, identical to Agent #1, except two lines, first getting the NotesDocument from the view entry, and then using GetItemValue() to get the value out of the document. This agent took 225.8 seconds to run:

In both agents, the call to get the next document in the ViewEntryCollection takes about 60 seconds. As you can see, the 30,000 calls to GetColumnValues() in agent #1 takes pretty much no time — 1.3 seconds — while it takes 133 seconds to open the 30,000 documents and read the value of the field from each one in agent #2. Almost exactly 100 times longer!

In agent 2, you also have to add 26 seconds to get the NotesDocument object from the ViewEntry object.

I hope this settles the discussion.

Yesterday I blogged about a simple class to parse file names, and that inspired me to improve it and add some functionality, which will actually come in handy for a project at work shortly.

The class is pretty self-explanatory, there is really nothing complicated in the code.
When the class is initialized, if a path to a directory (i.e. ending with \) is passed to the constructor the directory is created if it does not exist. If the directory exist, there are functions to copy or move both single files or all files in the directory. Directories can also be deleted using the RemoveDir method.
In addition, there are properties to get the path, file name, extension and file size (in bytes) of the file (if the class was initialized with a file name).

Here is an agent with some examples of how to call the class:

Option Public
Option Declare
Use "Class.FileFunctions"

Sub Initialize
  Dim file As FileObject
  Dim cnt As Integer
  Dim success As Boolean 

  '*** Create new file object
  Set file = New FileObject("D:\Downloads\Downloads\MERP\Assassins of Dol Amroth.pdf")

  '*** Copy the file to another (new) directory
  Call file.CopyTo("D:\Downloads\MERP1\", file.FileName)

  '*** Move the file to a new location and replace space with + in file name
  Call file.MoveTo("D:\Downloads\MERP2\", Replace(file.FileName," ","+"))

  '*** Create a new directory if it does not exist
  Set file = New FileObject("D:\Downloads\MERP3\Test\")

  '*** Copy all files in specified directory to another directory
  Set file = New FileObject("D:\Downloads\Downloads\MERP\")
  cnt = file.CopyAllTo("D:\Downloads\MERP\Backup\")
  MsgBox "Copied " & cnt & " files."

  '*** Move all files in the previously specified directory to another location
  cnt = file.MoveAllTo("D:\Downloads\Middle-Earth Role Playing Game\")
  MsgBox "Moved " & cnt & " files."

  '*** Remove D:\Downloads\Downloads\MERP\
  Call file.RemoveDir("")

  '*** Remove D:\Downloads\MERP3\ and Test directory that we created earlier
  success = file.RemoveDir("D:\Downloads\MERP3\Test\")
  If success = True Then
    success = file.RemoveDir("D:\Downloads\MERP3\")
    If success = False Then
      MsgBox "Failed to delete D:\Downloads\MERP3\"
    End If
  Else
    MsgBox "Failed to delete D:\Downloads\MERP3\Test\"
  End If
End Sub

Below is the class itself, I put it in a script library called Class.FileFunctions.

%REM
  Copyright (c) Karl-Henry Martinsson 2012.
  Some code copyright Andre Guirard (see below).
  You are free to use and modify my code, as long as you keep
  all copyright info intact. If you improve the code, please
  consider sharing it back to the community.
%END REM

Option Public
Option Declare

Type FileDetails
  path As String
  filename As String  
  extension As String
  filesize As Long
End Type

Class FileObject
  Private file As FileDetails
  Public silent As Boolean
  
  Public Sub New(filepathname As String)
    silent = False
    FullPathName = filepathname
    If file.FileName = "" Then
      If file.Path <> "" Then
        On Error 76 GoTo parentDoesNotExist 
        'No filename but path, then we create that directory (if missing)
        If Dir$(file.Path,16)="" Then
createDirectory:  
          Call MakeDir(file.Path)
        End If
      End If
      file.FileSize = 0 
    Else
      file.FileSize = FileLen(filepathname)
    End If
    Exit Sub
parentDoesNotExist:          
    Resume createDirectory  
  End Sub
  
  
  Public Property Set FileName As String
    file.FileName = FileName
    file.Extension = StrRightBack(FileName,".")
  End Property
  
  Public Property Get FileName As String
    FileName = file.FileName  
  End Property
  
  
  Public Property Get Extension As String
    Extension = file.Extension  
  End Property
  
  Public Property Set Extension As String
    file.Extension = Extension   
  End Property
  
  
  Public Property Set FilePath As String
    file.Path = FilePath  
    If Right(file.Path,1)<>"\" Then
      file.Path = file.Path & "\"
    End If
  End Property
  
  Public Property Get FilePath As String
    FilePath = file.Path  
  End Property
  
  
  Public Property Set FullPathName As String
    Me.FilePath = StrLeftBack(FullPathName,"\")
    Me.FileName = StrRightBack(FullPathName,"\")
  End Property
  
  Public Property Get FullPathName As String
    FullPathName = file.Path & file.FileName  
  End Property
  
  
  Public Function CopyTo(ByVal newpath As String, ByVal newname As String) As Boolean
    '*** Check if both arguments are blank, then exit
    If FullTrim(newpath) = "" Then
      If FullTrim(newpath) = "" Then
        CopyTo = False
        Exit Function
      End If   
    End If
    If FullTrim(newpath) = "" Then
      newpath = file.Path
    End If  
    If FullTrim(newname) = "" Then
      newname = file.FileName 
    End If  
    Call MakeDir(newpath)
    On Error GoTo errHandlerCopyTo
    FileCopy me.FullPathName, newpath + newname
    If silent = False Then
      Print "Copied " & filename & " from " & file.Path & " to " & newpath
    End If
    CopyTo = True
exitFunctionCopyTo:
    Exit Function
errHandlerCopyTo:
    CopyTo = False
    Resume exitFunctionCopyTo
  End Function
  
  
  Public Function MoveTo(ByVal newpath As String, ByVal newname As String) As Boolean
    '*** Check if both arguments are blank, then exit
    If FullTrim(newpath) = "" Then
      If FullTrim(newpath) = "" Then
        MoveTo = False
        Exit Function
      End If   
    End If
    If FullTrim(newpath) = "" Then
      newpath = file.Path
    End If  
    If FullTrim(newname) = "" Then
      newname = file.FileName 
    End If
    Call MakeDir(newpath)
    On Error GoTo errHandlerMoveTo
    FileCopy me.FullPathName, newpath + newname
    Kill me.FullPathName
    If silent = False Then
      Print "Moved " & filename & " from " & file.Path & " to " & newpath
    End If
    
    MoveTo = True
exitFunctionMoveTo:
    Exit Function
errHandlerMoveTo:
    MoveTo = False
    Resume exitFunctionMoveTo
  End Function


  Public Function CopyAllTo(ByVal newpath As String) As Integer
    Dim filename As String
    Dim filecount As Integer
    '*** Check if both arguments are blank, then exit
    If FullTrim(newpath) = "" Then
      If FullTrim(newpath) = "" Then
        CopyAllTo = 0
        Exit Function
      End If   
    End If
    If FullTrim(newpath) = "" Then
      newpath = file.Path
    End If  
    Call MakeDir(newpath)
    On Error GoTo errHandlerCopyAllTo
    filename = Dir$(file.Path,2)  ' Include hidden files
    Do until filename=""
      FileCopy file.Path + filename, newpath + filename
      If silent = False Then
        Print "Copying " & filename & " from " & file.Path & " to " & newpath
      End If
      filecount = filecount + 1
      filename = Dir$()
    Loop
    CopyAllTo = filecount
exitFunctionCopyAllTo:
    Print "Copied " & filecount & " files"
    Exit Function
errHandlerCopyAllTo:
    CopyAllTo = filecount
    Resume exitFunctionCopyAllTo
  End Function

  Public Function MoveAllTo(ByVal newpath As String) As Integer
    Dim filename As String
    Dim filecount As Integer
    Dim deletelist List As String
    '*** Check if both arguments are blank, then exit
    If FullTrim(newpath) = "" Then
      If FullTrim(newpath) = "" Then
        MoveAllTo = 0
        Exit Function
      End If   
    End If
    If FullTrim(newpath) = "" Then
      newpath = file.Path
    End If  
    Call MakeDir(newpath)
    On Error GoTo errHandlerMoveAllTo
    filename = Dir$(file.Path,2)  ' Include hidden files
    Do Until filename=""
      FileCopy file.Path + filename, newpath + filename
      If silent = False Then
        Print "Moving " & filename & " from " & file.Path & " to " & newpath
      End If
      deletelist(filename) = file.Path + filename
      filecount = filecount + 1
      filename = Dir$()
    Loop
    Print "Cleaning up..."
    ForAll f In deletelist
      Kill f  
    End ForAll
    MoveAllTo = filecount
exitFunctionMoveAllTo:
    Print "Moved " & filecount & " files"
    Exit Function
errHandlerMoveAllTo:
    MoveAllTo = filecount
    Resume exitFunctionMoveAllTo
  End Function

  Public Function RemoveDir(ByVal dirpath As String) As Boolean
    '*** If blank, use the path in object
    If FullTrim(dirpath) = "" Then
      dirpath = file.path
    End If
    On Error GoTo errHandlerRemoveDir
    RmDir dirpath
    RemoveDir = True
exitRemoveDir:        
    Exit Function
errHandlerRemoveDir:
    RemoveDir = False
    Resume exitRemoveDir
  End Function
  

  ' ===== Private Supporting Functions =====  
  
  Private Sub MakeDir(Byval strWhere As String)
    ' *** This code by Andre Guirard @ IBM
    ' *** http://www-10.lotus.com/ldd/bpmpblog.nsf/dx/recursive-mkdir-vs.-iteration
    ' *** Using an iterative method instead of recursive due to stack issues (see link above)
    On Error 76 Goto parentDoesNotExist 
    Dim stack$ 
    Const NL = { 
} 
    Do 
      Mkdir strWhere 
      On Error Goto 0 ' first success, stop trapping errors; avoid infinite loop. 
      strWhere = Strleft(stack, NL) ' "pop" a path for next iteration 
      stack = Mid$(stack, Len(strWhere)+2) 
failed: 
    Loop Until strWhere = "" 
    Exit Sub 
parentDoesNotExist: 
       ' This error code can indicate other problems, but assume missing parent. 
       ' If not, we get a different error (75) later when trying to create the parent. 
    Dim fpath$, fname$ 
    SplitFilepath strWhere, fpath, fname 
    If fpath = "" Then Error 76, "Invalid path: '" & strWhere & "'" 
    stack = strWhere & NL & stack ' "push" onto stack to retry later. 
    strWhere = fpath ' try a path one step shorter. 
    Resume failed 
  End Sub 

  
  Private Sub SplitFilePath(Byval fullpath$, dirpath$, filename$) 
    ' *** This subroutine by Andre Guirard @ IBM
    ' *** http://www-10.lotus.com/ldd/bpmpblog.nsf/dx/recursive-mkdir-vs.-iteration
    ' *** Called from MakeDir()    
    Const DELIMS = {/\:} 
    While Instr(DELIMS, Right$(fullPath, 1)) ' discard final delimiter character... 
      fullpath = Left$(fullpath, Len(fullpath)-1) 
    Wend 
    Dim candidate$, i% 
    filename = Strtoken(fullpath, Left$(DELIMS, 1), -1) 
    For i = 2 To Len(DELIMS) 
      candidate = Strtoken(fullpath, Mid$(DELIMS, i, 1), -1) 
      If Len(candidate) < Len(filename) Then 
        filename = candidate 
      End If 
    Next 
    Dim fplen% 
    fplen = Len(fullpath)-Len(filename) 
    If fplen > 0 Then fplen = fplen - 1 
    dirpath = Left$(fullpath, fplen) 
  End Sub
  
End Class

 
Enjoy!
 

A question I have seen a few times in the developerWorks forums is how to detect what values (often names) have been added in a multi-value field.

This is not very difficult to do in Lotusscript, and there are a few different ways to do it. I prefer to use lists, but you can of course use arrays as well. The basic idea is to declare a global variable to hold the initial value of the field, populate it when the document is either opened or put into edit mode, and then checked when the document is saved.

Globals:
Dim oldValue List As String

PostModeChange:
Dim oldarray as Variant
'*** Create array, assume ; as item separator in field
oldarray = Split(uidoc.FieldGetText("myMultiValueField"),";")
'*** Build a list of values
ForAll o in oldarray 
    oldValue(o) = o 
End ForAll

QuerySave: 
Dim newarray as Variant
Dim newValue List As String
'*** Create array, assume ; as item separator in field
newarray = Split(uidoc.FieldGetText("myMultiValueField"),";")
'*** Compare with values in list
ForAll n in newarray
    '*** Check if value is not in global list, then it is new
    If IsElement(oldValue(n)) = False Then
        newValue(n) = n    ' Add new value to list
    End If
End ForAll
'*** Loop through all new values and print them
ForAll nv in newValue 
    Print nv + " is new." 
End Forall
 

The same technique can be used to detect what fields have changed since the document was opened. Just store the values of all fields (except any starting with $) in a list, then check the values in the fields against that list when saving the document.

 
Globals:
Dim oldValue List As String

QueryOpen:
'*** Build a list of values with field name as listtag
ForAll i in doc.Items 
    If Left$(i.Name,1)<>"$") Then
        oldValue(i.Name)=i.Text
    End If
End ForAll

QuerySave: 
Dim modified List As String
Dim tmpValue As String
'*** Compare current fields with values in list
ForAll o in oldValue 
    '*** Check if value is the same or not
    tmpValue = uidoc.FieldGetText(Listtag(o))   ' Get current field value
    If tmpValue <> o Then
        modified(ListTag(o))=tmpValue    ' Add new value to list of modified fields
    End If
End ForAll
'*** Loop through all modified fields and display new value
ForAll m in modified
    Print Listtag(m) + " was changed from " + oldValue(Listtag(m)) + " to " + m
End Forall
  

All very easy when you know how to do it. And again, it shows the power of lists.

While reading the developerWorks forums, I have noted that some tasks seem to be common, and users often have issues with it. One very common is to export data from Notes/Domino to Excel. My guess is that some manager/executive want to get the data to manipulate and analyze it in Excel, which they know better, and where reporting can be done easier.

First of all, for anyone that want to do real integration with Excel (or Word, or any other office program), I would suggest to look at the presentations given by John Head at Lotusphere and other conferences in the past:
Lotusphere 2011 – JMP208
IamLUG 2009 – Integration and Coexistance
Lotusphere 2006 – BP309
Lotusphere 2005 – JMP108

But if you just need to export raw data to Excel, there are a couple easy ways to do that. You don’t need to automate Excel, something the programmers posting in the forums seem to frequently do. That just makes things more complicated, and if you run the code in a server agent, Microsoft Office have to be installed on the server itself. Different versions of Excel also cause different issues.

The different methods I use to export Notes data to Excel are as follows:

• Create a CSV file, which can be opened in Excel
• Create a HTML table in a file with .xls extension, which can be opened in Excel
• Create a HTML table and return it through a browser, with a content type set to open in Excel.

Depending on what the requirement is, I usually choose one of those method.

Method 1 – CSV file (local)

The easiest way to get data from Notes to Excel (or any other spreadsheet program) is to simply save the data as  comma-separated values (CSV). Use regular Lotusscript file operations and write the values to the file, encased in double quotes and separated by a comma. You need to build a string containing a complete line, as the Print statement adds a line break to the end automatically.

A CSV file could look like this:

FirstName,LastName,Department,StartYear
"Karl-Henry","Martinsson","IT","2002"
"John","Smith","Accounting","2009"

The first row is the header, with the field names listed. This helps on the receiving side, but is optional. Dates and numbers don’t need quotes around them, as long as they don’t contain commas. So make a habit to format any numbers without commas.

Method 2 – HTML file (local)

This method is very similar to the CSV method. The difference is that you create a table in HTML, matching the cells in Excel you want to create/populate. But instead of creating a file with the extension .htm or .html, you give it the extension .xls. Excel is intelligent enough to identify it as HTML and map the content correctly.

The biggest advantages of using a HTML file instead of CSV is that you get more control over the formatting. By using colspan and rowspan, you can merge cells together, for example in a header. You can also use markup to make cells bold or italic, or even set the text and background colors.

Method 3 – HTML (through browser)

This method is a variant of method 2. But instead of calling the agent inside the Notes client and creating a file somewhere in the file system, the agent is called through a URL. This means the Domino HTTP task must be enabled on the server. The URL can be called from inside the Notes client using the @URLOpen function, or the OpenURL method of the NotesUIWorkspace class.

Instead of writing to a file, use the Print statement to print to the browser. The first line printed must be the content type, so the browser realize what application to open the file in. For Excel, it looks like this:

Print "content-type:application/vnd.ms-excel"

It also works with (among other) “application/msexcel” and “application/xls”, but the one above is the official one, so I suggest using that.

After you print the content type, simply print the HTML table code to the browser. This will open the HTML in Excel after the agent is done. It’s that easy.