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.]
Great function for sorting collections.
I did find a problem, the sort fails if data to be sorted contains quotes. It makes @sort function an invalid formula.
I am trying to devise a workaround but no success yet.
Any Ideas?
Thanks, I was not aware of this problem.
I haven’t had a chance to test or even try to reproduce, but this is the first idea for a workaround that comes to mind:
1) Replace all quotes with a string that is not likely to appear elsewhere in the data, e.g. #maxquotestring#
2) Run SortCollection
3) Re-replace that string with the quotes again.
I will try to revisit this again later and will post any improvements to the function.
It seems like if you have a very large collection then the @sort formula became to large and faile.
Im in a hurry (at a customer) so I dont have the time to investigate it further. So this is just to let You know
Ok, thanks. I have used it on about 10k document views at most but I would guess that the @Sort function has some limitation to what it can take in or the limitation may be with Evaluate. I did a quick search in the documentation but did not find anything, I would guess it’s the good old 64k limit… (More on Lotus Notes limitations in the Notes client help database)
Of course you can always replace @Sort with your favourite Lotusscript sort routine Lotusscript, or Google and you will find many.
And you think this script is faster than view.GetAllEntriesByKey(). I see many For Next loops in this script who are slowing the program. I have a 25.000 documents view, and GetAllEntriesByKey is working mutch better.
Adrian – not exactly sure what you mean. This function is for sorting a NotesDocumentCollection, how does view.GetAllEntriesByKey apply to that?
Hi Max, I have updated your great sorting routine to use Shell sort instead of @Sort. The Shell sort routine is based on Joe Littons work.
Please see:
http://per.lausten.dk/blog/2006/10/sorting-notesdocumentcollection-by.html
Thank you very much.
This function works fine.
Great. I modified the code to use a variants and them applied quicksort function to do the sorting.
Here is how I uploaded to a variant
dim tempdoc as NotesDocument
dim tempItem as NotesItem
Dim varSort as Variant
Set tempdoc = db.CreateDocument
tempdoc.ArrSort = “”
Set tempitem = tempDoc.GetFirstItem(“ArrSort”)
For i = 0 To coll.Count – 1
strSort = “”
For n = Lbound(fieldnames) To Ubound(fieldnames) + 1
strSort = strSort & Left(arrSort(i,n) & Space(arrFieldValueLength(n)), arrFieldValueLength(n))
Next n
Call tempitem.AppendToTextList(strSort )
Next i
then call the selection sort function
varSort = tempItem.Values
Call SelectionSort(varSort )
and finally, rebuilt the collection
Set collSorted = coll.Parent.GetView(viewname).GetAllDocumentsByKey(fakesearchstring)
For i = 0 To Ubound(varSort)
Set doc = db.GetDocumentByUNID(Right(varSort(i), 32))
Call collSorted.AddDocument(doc)
Next i
Here is the selection sort function
Sub SelectionSort (MyArray As Variant)
‘ Selection Sort, “Data Structures Using C”, Tenenbaum, …, p. 337.
If Ubound (MyArray) <= 0 Then Exit Sub
nElem = Ubound (MyArray) + 1
For i = nElem – 1 To 1 Step -1
Large$ = MyArray(0)
indx = 0
For j = 1 To nElem
If j > i Then Exit For
If Strcompare(MyArray(j), Large$) >0 Then ‘ Ascending
Large$ = MyArray(j)
indx = j
End If
Next
MyArray(indx) = MyArray(i)
MyArray(i) = Large$
Next
End Sub
Great job! This works after several others I tried failed. I suggest having the view name and fake search string passed in as a parameter so that the function doesn’t have to be edited and so that lazy coders like me don’t have to read the function before trying to run it. Haha. Thanks for the contribution. What do I owe you? Rob Pinion
After sorting, how can we go about inserting page breaks between the sorted data? E.G. between clients? I am using this script to sort dozens of clients and it would be much easier to have page breaks between the clients
Thanks for doing the work.
Here’s an idea.
By changing the new doc collection creation line to
Set collSorted = coll.Parent.Views(0).GetAllDocumentsByKey(fakesearchstring)
Instead we just grab the first available view in the db. Because we don’t use the view other than to create the doc collection, we can use anything.
By making this small change, you don’t have to pass in or code any db names or view names. Makes the function a better standalone candidate for a utility library.
Cheers and thanks!
It’s a great generic function to sort the collection.
Congrats for the good work done.
But i have one query in this function.I need to sort a list which consisits of only numbers . for example list will contain numbers such as 012999 and 003323..your function is not working in such scenario.
Request you to suggest a solution for this.
You made my Day!!!
Thank you
You know it’s some good code when after all these years, IT STILL WORKS !!!!
My project just got completed faster than expected thanks to you. Thanks again for posting.
Great function.
Thanks for share with us.
Faucedt the email account you need to remove.