How to sort a NotesDocumentCollection in Lotusscript

This Lotusscript function sorts a document collection on one or multiple fields.

I have previously used several other algorithms that use a view to sort the collection, these however have the drawback that they become very inefficient (i.e. slow) as the number of documents in the view used for sorting grows. The solution presented below does not have this problem.

It has been developed and tested in Lotus Notes 6.5.3 but should work in all ND6 (release 6) and possibly earlier Lotus Notes releases too (if you test this successfully or unsuccessfully write a comment to this post and everyone will know).

Example of use:

Dim fieldnames(0 To 2) As String
fieldnames(0) = "SKU"
fieldnames(1) = "OrderDate"
fieldnames(2) = "Client"
Set collection = SortCollection (collection, fieldnames)


Function to sort DocumentCollection:

Function SortCollection(coll As NotesDocumentCollection, fieldnames() As String) As NotesDocumentCollection

' ------------------------------------------------
' --- You may use and/or change this code freely
' --- provided you keep this message
' ---
' --- Description:
' --- Sorts and returns a NotesDocumentCollection
' --- Fieldnames parameter is an array of strings
' --- with the field names to be sorted on
' ---
' --- By Max Flodén 2005 - http://www.tjitjing.com
' ------------------------------------------------

Dim session As New NotesSession
Dim db As NotesDatabase
Dim collSorted As NotesDocumentCollection
Dim doc As NotesDocument
Dim i As Integer, n As Integer
Dim arrFieldValueLength() As Long
Dim arrSort, strSort As String
Dim viewname As String, fakesearchstring As String

viewname = "$All" 'This could be any existing view in database with first column sorted
fakesearchstring = "zzzzzzz" 'This search string must NOT match anything in view
Set db = session.CurrentDatabase

' ---
' --- 1) Build array to be sorted
' ---

'Fill array with fieldvalues and docid and get max field length
Redim arrSort(0 To coll.Count -1, 0 To Ubound(fieldnames) + 1)
Redim arrFieldValueLength(0 To Ubound(fieldnames) + 1)
For i = 0 To coll.Count - 1
Set doc = coll.GetNthDocument(i + 1)
For n = 0 To Ubound(fieldnames) + 1

If n = Ubound(fieldnames) + 1 Then
arrSort(i,n) = doc.UniversalID
arrFieldValueLength(n) = 32
Else
arrSort(i,n) = "" & doc.GetItemValue(fieldnames(n))(0)
' Check length of field value
If Len(arrSort(i,n)) > arrFieldValueLength(n) Then
arrFieldValueLength(n) = Len(arrSort(i,n))
End If
End If

Next n
Next i

'Merge fields into list that can be used for sorting using @Sort function
For i = 0 To coll.Count - 1
If Not strSort = "" Then strSort = strSort & ":"
strSort = strSort & """"
For n = Lbound(fieldnames) To Ubound(fieldnames) + 1
strSort = strSort & Left(arrSort(i,n) & Space(arrFieldValueLength(n)), arrFieldValueLength(n))
Next n
strSort = strSort & """"
Next i

' ---
' --- 2) Sort array
' ---
arrSort = Evaluate("@Sort(" & strSort & ")")

' ---
' --- 3) Use sorted array to sort collection
' ---
Set collSorted = coll.Parent.GetView(viewname).GetAllDocumentsByKey(fakesearchstring)

For i = 0 To Ubound(arrSort)
Set doc = db.GetDocumentByUNID(Right(arrSort(i), 32))
Call collSorted.AddDocument(doc)
Next i

' ---
' --- 4) Return collection
' ---
Set SortCollection = collSorted

End Function


(This article is previously published and has been moved to this blog)

[Update: Instead of using Evaluate and @Sort in section 2 in the code you can of course use any of your favourite sort routines. Or search the Net for one, there are many out there.]

Lotusscript: Body Above Signature in Memo

I got a library of Lotusscript functions and code examples that I find useful and reuse in projects every now and then. Some are very simple and some did require a little or a lot of research and work before I got them working. In this post is an example on how to disable the email signature from Lotusscript.

Problem:
When creating a memo from backend Lotusscript and then showing it to the user using the frontend/UI classes, the user email signature (if enabled) will be inserted at the top of the document, i.e. the body text will be below the signature instead of above it as expected.

Solution:
Disable the signature and then re-enable it as shown in the following code example.

Limitations:
This is only an example on how to disable the user email signature. If you want it inserted at the end of whatever your email contents is (in this example "Hello world!") you can easily modify the example to read the signature from docProfile and then insert it.

Pre-requisites:
This has been tested in Lotus Notes 6.5.3 but it should work both in previous releases such as R5 and in the later ND7 release.
Write a comment if you have confirmed this working (or not working) in other releases.

Below is a code example that
1) creates a memo using Lotusscript backend classes,
2) disables user email signature,
3) shows the memo to the user using Lotusscript frontend classes, and
4) re-enables the user email signature

Please note that the NotesRichTextItem Update method used in this example is new in ND6.


' ------------------------------------------------
' --- You may use and/or change this code freely
' --- provided you keep this message in your code
' ---
' --- Example by Max Flodén 2005
' --- http://www.tjitjing.com
' ------------------------------------------------

Dim workspace As New NotesUIWorkspace
Dim dbMail As NotesDatabase
Dim docMail As NotesDocument
Dim rtitem As NotesRichTextItem

Dim docProfile As NotesDocument
Dim strProfileEnableSignature As String

'Open users mail database
Set dbMail = New NotesDatabase( "", "" )
Call dbMail.OpenMail
If Not dbMail.IsOpen Then
Call Messagebox("Could not open mail database.", 48, "Error")
Exit Sub
End If

'Create new memo from back end
Set docMail = dbMail.CreateDocument
docMail.Form = "Memo"
docMail.Subject = "Hello"
Set rtitem = New NotesRichTextItem( docMail, "Body" )
Call rtitem.AppendText("Hello world!")
Call rtitem.AddNewline(1)
Call rtitem.Update 'Update method is new in ND6

'Disable signature in users mail profile
Set docProfile = dbMail.GetProfileDocument("CalendarProfile")
strProfileEnableSignature = docProfile.EnableSignature(0)
If strProfileEnableSignature = "1" Then
docProfile.EnableSignature = ""
Call docProfile.Save(True,False)
End If

'Show memo to UI/front end
Set uidoc = workspace.EditDocument(True, docMail,,,True,True)

'Re-enable signature in users mail profile
If strProfileEnableSignature = "1" Then
docProfile.EnableSignature = "1"
Call docProfile.Save(True,False)
End If