Lotusscript

A question was posted in the IBM DeveloperWorks forum for Notes/Domino 8 about the possibility to detect from within Notes if a computer is equipped with a touch screen. The answer was that you have to check if a specific DLL is installed, which is done though the registry. The original posted then asked how to do that in Lotusscript, so I deceded to simply post some code I am using. I did not write this code, and I don't know who originally did. I think I may have taken some VB code and simply adapted it for Lotusscript. I plan to rewrite this a s a class when I have some time. In the mean time, here is the code.   Option Public Option Declare Dim REG_NONE As Long Dim REG_SZ As Long Dim REG_EXPAND_SZ As Long Dim REG_BINARY As Long Dim REG_DWORD As Long Dim REG_DWORD_LITTLE_ENDIAN As Long Dim REG_DWORD_BIG_ENDIAN As Long Dim REG_LINK As Long Dim REG_MULTI_SZ As Long Dim REG_RESOURCE_LIST As Long Dim REG_FULL_RESOURCE_DESCRIPTOR As Long Declare Function RegCloseKey Lib "advapi32.dll" (Byval hKey As Long) As Long Declare Function RegCreateKeyEx Lib "advapi32.dll" Alias "RegCreateKeyExA" (Byval hKey As Long, _ Byval lpSubKey As String, Byval Reserved As Long, Byval lpClass As String, _ Byval dwOptions As Long, Byval samDesired As Long, Byval lpSecurityAttributes As Long, _ phkResult As Long, lpdwDisposition As Long) As Long Declare Function RegOpenKeyEx Lib "advapi32.dll" Alias "RegOpenKeyExA" (Byval hKey As Long, _ Byval lpSubKey As String, Byval ulOptions As Long, Byval samDesired As Long, _ phkResult As Long) As Long Declare Function RegSetValueExString Lib "advapi32.dll" Alias "RegSetValueExA" (Byval hKey As Long, _ Byval lpValueName As String, Byval Reserved As Long, Byval dwType As Long, Byval lpValue As String, _ Byval cbData As Long) As Long Declare Function RegSetValueExLong Lib "advapi32.dll" Alias "RegSetValueExA" (Byval hKey As Long, _ Byval lpValueName As String, Byval Reserved As Long, Byval dwType As Long, lpValue As Long, _ Byval cbData As Long) As Long Declare Function RegQueryValueExString Lib "advapi32.dll" Alias "RegQueryValueExA" _ (Byval hKey As Long, Byval lpValueName As String, Byval lpReserved As Long, lpType As Long, _ Byval lpData As String, lpcbData As Long) As Long Declare Function RegQueryValueExLong Lib "advapi32.dll" Alias "RegQueryValueExA" _ (Byval hKey As Long, Byval lpValueName As String, Byval lpReserved As Long, lpType As Long, _ lpData As Long, lpcbData As Long) As Long Declare Function RegQueryValueExNULL Lib "advapi32.dll" Alias "RegQueryValueExA" _ (Byval hKey As Long, Byval lpValueName As String, Byval lpReserved As Long, lpType As Long, _ Byval lpData As Long, lpcbData As Long) As Long ' --- Registry key values Const HKEY_CLASSES_ROOT = &H80000000 Const HKEY_CURRENT_USER = &H80000001 Const HKEY_LOCAL_MACHINE = &H80000002 Const HKEY_USERS = &H80000003 Const HKEY_CURRENT_CONFIG = &H80000005 ' --- Registry return values Const ERROR_NONE = 0 Const ERROR_BADDB = 1 Const ERROR_BADKEY = 2 Const ERROR_CANTOPEN = 3 Const ERROR_CANTREAD = 4 Const ERROR_CANTWRITE = 5 Const ERROR_OUTOFMEMORY = 6 Const ERROR_INVALID_PARAMETER = 7 Const ERROR_ACCESS_DENIED = 8 Const ERROR_INVALID_PARAMETERS = 87 Const ERROR_NO_MORE_ITEMS = 259 ' --- Registry access key Const…

Yesterday I was asked to create a way to let us disable agents running on a Domino server in an easy way before the Domino server comes back from a crash. The reason for this request is that for a while we have been having one particular agent crash, taking the whole Domino server down with it. It only happens occasionally, and seems to be related to the document being processed. When the server comes up after a crash like that, a consistence check is done, then the agent manager launches the agent again, causing the server to go down again. I added code to the offending agent, so it would flag the document before processing and un-flag after processing is done. This way, when the agent encounters an already flagged document, it will be skipped as it was processed during a previous crash. For some reason this did not work yesterday morning, when one of those rare corrupted(?) documents was encountered. The logic in the code was faulty, because the document was of a new type, so it was never flagged as being processed. The same document was processed over and over again, taking the server down every time. So I simply created two functions, put them in a global script library where I keep utility functions used in many places, and added 3 lines of code to each agent where I wanted this functionality. The first function is simply to check if a specified file exists. I am using en error handler to catch any error (for example missing directory). Function FileExists(filename As String) As Boolean On Error GoTo errHandler If Dir$(filename)<>"" Then FileExists = True Else FileExists = False End If exitFunction: Exit Function errhandler: FileExists = False Resume exitFunction End Function The second function is the one where I check for the existance of a file named the same as the agent, with an extension of .disabled. If that file does not exist, I check for a file with the extension .enabled. If that file is missing, I simply create a blank file with that name. This way, the first time any agent is executed, the file will be created for us, and I don't have to sit and manually create them all. Function DisableAgent() As Boolean Dim session As New NotesSession Dim agentname As String Dim filename As String agentname = session.CurrentAgent.Name filename = "D:\NotesAgentControlFiles\" + agentname + ".disabled" If FileExists(filename) Then DisableAgent= True Else filename = "D:\NotesAgentControlFiles\" + agentname + ".enabled" If Not FileExists(filename) Then Open filename For Output As #1 Print #1, "" Close #1 Print "Created control file " & filename End If DisableAgent= False End If End Function Finally, in each agent I want to be able to disable like this, I add this code in the beginning: '*** Check if disable-file exists, exit in that case If DisableAgent() Then Exit Sub End If Just a few lines of code, but hopefully it will save someone a few minutes of work. Of course, you…

I decided to play around a little, and as an experiment put up one of my Notes projects on the open source repository GitHub. You can see the result here: http://github.com/TexasSwede/Class.MailMerge This is a script library in Lotusscript to create documents based on a source document and a template document. I have blogged about it before, but I added some functionality to it, and thought it would be easier for people to download a complete database.  

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 +…

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…

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…

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…

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…