Thursday, January 11, 2007

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 -
' Based on code by:
' Max Flodén -
' Joe Litton -
' Peter von Stöckel -
' Per Henrik Lausten, November 2006 -
' 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
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
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)

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

End Function

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

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.



Sean Burgess said...

I tackled this issue a couple of months ago, but took a slightly different tact. I looked at using arrays, but wasn't really happy with the path it was leading me in. Ultimately, I used a custom LS class that implements a combination of lists and arrays. You can take a look at the code by downloading my ASND Export Facility on OpenNTF.

Look in the ASND Script Library and you will see my solution.

The cool think about the final solution is that you can sort ascending and descending and it also takes into account the type of field that's being sorted. So dates, numbers, and text are all sorted differently.


Per Henrik Lausten said...

Great work, Don

Anonymous said...

Error. I get the following error:

wrong number of array subscripts.

its on this line of code:

Set doc = db.GetDocumentByUNID(Right(arrSort(loopCount), 32))

Don McNally said...

Patrick, I'm not really sure what is going on without seeing how you implemented the code. I can say that mine is working without error.

One thing to look at is that arrSort has to be DIMd as a Variant because it is processed as a two-dimensional array and then reused as a one-dimensional array later in the code.

Let me know whether that helps at all.

Anonymous said...

Type mismatch. Not work!!

Any have the complete and real code working?

Thanks in advance,

Unknown said...
This comment has been removed by the author.
Unknown said...

@ Dim loopCount As Integer, loopStart As Integer, loopEnd As Integer, loopStep As Integer
+ Dim isNum() As Boolean

@ Redim arrFieldValueLength(0 To Ubound(fieldnames) + 1)
+ Redim isNum(0 To Ubound(fieldnames)+1)

@ arrSort(i,n) = "" & doc.GetItemValue(fieldnames(n))(0)
+ isNum(n) = isNum(n) Or Isnumeric(arrSort(i,n))

@ For n = Lbound(fieldnames) To Ubound(fieldnames) + 1
- strSort = strSort & Left(arrSort(i,n) & Space(arrFieldValueLength(n)), arrFieldValueLength(n))
+ If isNum(n) Then
+ strSort = strSort & Right(Ustring(arrFieldValueLength(n),"0") & arrSort(i,n), arrFieldValueLength(n))
+ Else
+ strSort = strSort & Left(arrSort(i,n) & Space(arrFieldValueLength(n)), arrFieldValueLength(n))
+ End If