Sorting a NotesDocumentCollection by multiple field values (Show’n Tell Thursday)
October 26th, 2006I have been using Joe Littons sortCollection function with success to sort a NotesDocumentCollection by the value in a specific field name. However the function can only sort by one field value and I recently had the requirement to be able to sort by multiple field values.
Max Flodén has created a function that does exactly that: sorts a NotesDocumentCollection by one or more field values. But the code from Max uses Evaluate and @Sort to do the actual sorting - and therefore has problems with quotes in the values and apparently also a problem with large collections (see comments to his post).
So I have combined the best from the two sort functions - the sortValues function from Joe Litton that uses Shell sort and the option to sort by multiple field values from Max Flodén - to get a function that sorts a NotesDocumentCollection by one or more field values.
Update: I have updated the sort function to use a fast and easy way of creating an empty NotesDocumentCollection introduced by Peter von Stöckel.
Update November 21: During testing I found an unnecessary loop in the sortCollection function. I have therefore updated the sort function.
Update December 9, 2011: The function is now available on the new OpenNTF XSnippets site.
Tags: Lotus Domino, Lotusscript, open source, OpenNTF, Show-n-Tell Thursday
October 30th, 2006 at 16:29
great job !!
Am I allowed to use the code in my OpenNTF Project !!HELP!! ?
regards
Ulrich
October 31st, 2006 at 09:49
Hi Ulrich, yes you are - go ahead! Thanks.
November 17th, 2006 at 12:03
If the document collection is around 10,000 the script is timing out. Any alternative method which can sort collection > 10000
November 17th, 2006 at 13:34
Hi, I have just tested the sorting routine with 12.000 documents and it sorts the documents as expected.
While doing the test I found that there is one loop that goes through all the documents which is not necessary for the sort to work. I will update this post with the new code.
Perhaps this helps in your case. So please try the sort again when I have updated the code. Also make sure that it is the script that causes the problem and not something else such as network delays, server processing power etc.
January 11th, 2007 at 18:10
Hi. Your post was VERY timely for me and a project I was working on. I extended your code a little because I needed to sort in descending as well as ascending order. I just blogged it here: http://dmcnally.blogspot.com/2007/01/sntt-sorting-notesdocumentcollection.html
December 18th, 2007 at 22:55
Excellent post. I extended your code a little because I needed to sort documents in a different database. I simply pass the SortNDC function a database as well and add code so that if the passed db is nothing, the function should use the current database.
Thanks.
Greg
December 20th, 2007 at 08:48
Hi Greg, excellent extension to the function. Thanks for your comment.
January 1st, 2008 at 18:17
Hello people,
I was quite stuck with sorting for days .. Thanks for all those who had been in creation and in reincarnation of the code 🙂 .. Happy New Year to All .. Code works great ..
February 19th, 2008 at 23:45
Per, There is an array limit on arrays. which causes the sorting to halt.
Array bounds must fall in the range -32768 to 32767, inclusive.
Have you seen any update to this
March 12th, 2008 at 18:50
Works like a champ! Thanks for saving me hours of dev time! Keep up the good work!
April 10th, 2008 at 16:40
This don't work for field Notes of data-time type...it's true?
July 3rd, 2008 at 22:00
I'm getting stuck on this part. It says I can't find Universal ID. But I am trying to sort a Date-Time field
Set collSorted = db.GetProfileDocCollection("Foo") ' create an empty NotesDocumentCollection
Forall y In arrSort
Set doc = db.GetDocumentByUNID(Right(y, 32))
Call collSorted.AddDocument(doc)
End Forall
July 4th, 2008 at 18:01
once its over 500 documents the 'invalid documentid pops up'
January 20th, 2009 at 19:52
I'm a little late to the party 🙂 But this script really helped me, thank you so much for adjusting the excellent original with the Shell sort. The only problem I had was with some dates (as a sorted column), that I had to change the code just a little, but other than that, works great. THANK YOU
July 12th, 2010 at 02:38
Thank you Per Henrik Lausten!
I'm using this in the Notes Reconn project at OpenNTF.
http://www.openntf.org/Projects/pmt.nsf/ProjectLookup/Notes%20Reconn
Regards
David
July 12th, 2010 at 09:27
[...] woke up this morning to a comment by David Jeyachandran to my blog post on Sorting a NotesDocumentCollection by multiple field [...]
February 14th, 2011 at 01:51
I love it. It went crashin' nasty to start with - but I realised passing in a dc from an external db was a problem. Added a new dbCollSource parameter to refer to when getting docs and creating my sorted collection and all was well. Still, a great piece of work - many thanks for sharing it.
July 15th, 2011 at 22:36
Hi! Great code but it doesn't seem to work to sort numeric values - they get sorted as strings...
Is there a fix for it?
Thanks!!!