SnTT: Sorting a NotesDocumentCollection - Ascending *or* Descending

Finally I have time for another SNTT post! This one shows, I suppose, the power of the collaboration that goes on in the Notes/Domino community.

I needed to be able to sort a collection of documents for my web service project and was lucky enough to find pretty much the exact code I needed at Per Henrik Lausten's site. He had taken code from a couple of other guys and combined it together. The one thing it didn't do which I needed was to be able to sort in Ascending or Descending order.

It was a pretty easy change to make. And here is the code. Note: I only include the sortCollection code because that is all I changed - you will need the sortValues code from Per Henrik's or Joe Litton's site for everything to work.

Function sortCollection(coll As NotesDocumentCollection, fieldnames() As String, 
sortOrder as String) As NotesDocumentCollection

' Description:
' Sorts and returns a NotesDocumentCollection
' Fieldnames parameter is an array of strings
' with the field names to be sorted on
' 
' Modified by Don McNally, December 2006 - http://dmcnally.blogspot.com/
' 
' Based on code by:
' Max Flodén - 
http://www.tjitjing.com/blog/2006/05/how-to-sort-notesdocumentcollection-in.html
' Joe Litton - http://joelitton.net/A559B2/home.nsf/d6plinks/JLIN-5ZU3WH
' Peter von Stöckel - 
http://www.bananahome.com/users/bananahome/blog.nsf/d6plinks/PSTL-6UWC7K
' Per Henrik Lausten, November 2006 - 
http://per.lausten.dk/blog/2006/10/sorting-notesdocumentcollection-by.html 
'
' Example of use
' Dim fieldnames(0 To 2) As String
' fieldnames(0) = "SKU"
' fieldnames(1) = "OrderDate"
' fieldnames(2) = "Client"
' Set collection = SortCollection (collection, fieldnames) 

 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 loopCount As Integer, loopStart As Integer, loopEnd As Integer, loopStep As Integer

 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 array that can be used for sorting using the sortValues function
 Dim aryFieldValues() As String
 For i = 0 To coll.Count - 1 
  Redim Preserve aryFieldValues(1 To i+1)
 
  strSort = ""
  For n = Lbound(fieldnames) To Ubound(fieldnames) + 1
   strSort = strSort & Left(arrSort(i,n) & Space(arrFieldValueLength(n)), arrFieldValueLength(n))
  Next n
 
  aryFieldValues(i+1) = strSort
 Next i


' ---
' --- 2) Sort array using sortValues function by Joe Litton
' ---
 arrSort = sortValues(aryFieldValues)

' ---
' --- 3) Use sorted array to sort collection
' ---
 Set collSorted = db.GetProfileDocCollection("Foo")  ' create an empty NotesDocumentCollection
 If ucase(sortOrder) = "D" Then
  loopStart = Ubound(arrSort)
  loopEnd = 1
  loopStep = -1
 Else
  loopStart = 1
  loopEnd = Ubound(arrSort)
  loopStep = 1
 End If
 For loopCount = loopStart To loopEnd Step loopStep
  Set doc = db.GetDocumentByUNID(Right(arrSort(loopCount), 32))
  Call collSorted.AddDocument(doc)
 Next

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

End Function

This LotusScript was converted to HTML using the ls2html routine,
provided by Julian Robichaux at nsftools.com.

I had some thoughts about extending the code even further to allow different sort orders for the different fields (like Ascending on the first field and Descending on the second) but didn't end up needing it for my project. Hope you find this useful.

Technorati: Show-n-Tell Thursday