Sorting a NotesDocumentCollection by multiple field values (Show’n Tell Thursday)

October 26th, 2006

I 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: , , , ,

18 Responses to “Sorting a NotesDocumentCollection by multiple field values (Show’n Tell Thursday)”

  1. Ulrich krause Says:

    great job !!

    Am I allowed to use the code in my OpenNTF Project !!HELP!! ?

    regards

    Ulrich

  2. Per Henrik Lausten Says:

    Hi Ulrich, yes you are - go ahead! Thanks.

  3. Anonymous Says:

    If the document collection is around 10,000 the script is timing out. Any alternative method which can sort collection > 10000

  4. Per Henrik Lausten Says:

    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.

  5. Don McNally Says:

    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

  6. Greg G Says:

    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

  7. Per Henrik Lausten Says:

    Hi Greg, excellent extension to the function. Thanks for your comment.

  8. Katheeja Says:

    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 ..

  9. Thomas Adrian Says:

    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

  10. Anonymous Says:

    Works like a champ! Thanks for saving me hours of dev time! Keep up the good work!

  11. Anonymous Says:

    This don't work for field Notes of data-time type...it's true?

  12. Anonymous Says:

    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

  13. Anonymous Says:

    once its over 500 documents the 'invalid documentid pops up'

  14. thfcme Says:

    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

  15. David Jeyachandran Says:

    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

  16. I have somehow contributed to OpenNTF | Per Henrik Lausten Says:

    [...] woke up this morning to a comment by David Jeyachandran to my blog post on Sorting a NotesDocumentCollection by multiple field [...]

  17. Paul Hewson Says:

    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.

  18. Martin Says:

    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!!!