This is being written in response to two hours of trying to get some code to work only to conclude that there was a bug in the API. I am not overly convinced it will be documented any time soon so let it be documented here!

I wanted to create a collection of participant registrations for a particular constituent. I also wanted to only really look at the most recent three registrations so I needed to both filter and sort. Sounds quite simple really until of course it did not work.

Here is my initial code:

Dim oPart As CParticipant 

    Dim oParts As New cParticipants 
    Dim oFilter As cParticipantFilter 

  
    oParts.INIT getSessionContext 
    Set oFilter = oParts.FilterObject 
    oFilter.FilterID(ParticipantFilterID_ConstituentID) = oConstit.Fields(RECORDS_fld_ID) 
    oParts.SortField = Participants_fld_DateAdded 
    oParts.SortOrder = Descending 
    bFound = False 

    
    For Each oPart In oParts 
        Debug.Print oPart.Fields(Participants_fld_DateAdded) 
        oPart.CloseDown 
        Set oPart = Nothing 
    Next oPart 

     'clean up etc...

The filter object narrows down the collection to a previously defined constituent. The sort field and order changes how they appear in the collection.

Of course I now know that on its own the sort works and on its own the filter works but they just don’t work together.

There are two different work arounds. The first is to use the IBBSortedCollection object.

This object is really useful for sorting collections that don’t normally sort themselves. See the Blackbaud knowledgebase example BB91472 .

In order to get this code to work you have to add the participants in the collection to the sorted collection and then sort it with your date added fields. You can then go through the sorted collection instead. This is shown below:

Dim oPart As CParticipant 

    Dim oParts As New cParticipants 

    Dim oFilter As cParticipantFilter 

    Dim oSort As IBBSortedCollection 

    

    oParts.INIT getSessionContext 

    Set oFilter = oParts.FilterObject 

    oFilter.FilterID(ParticipantFilterID_ConstituentID) = oConstit.Fields(RECORDS_fld_ID) 

    

    Set oSort = REUtil.getREServices.CreateServiceObject(bbsoSortedCollection) 

        

    For Each oPart In oParts 

        oSort.add oPart, oPart.Fields(Participants_fld_ID), oPart.Fields(Participants_fld_DateAdded) 

        oPart.CloseDown 

        Set oPart = Nothing 

    Next oPart 

    

    oSort.SortOrder = Descending 

    oSort.ReSort 

    

    For Each oPart In oSort 

        Debug.Print oPart.Fields(Participants_fld_DateAdded) 

        oPart.CloseDown 

        Set oPart = Nothing 

    Next oPart 

  

    'clean up etc...

       
It involves some overhead which is why the second solution is probably better. This was Blackbaud’s workaround (thanks Ron)

Public Sub SortParticipants() 

   Dim oConstit As CRecord 

   Dim oFilter As cParticipantFilter 

   Dim oPart As CParticipant 

   Dim sSQL As String 

   Dim oParts As cParticipants   Set oConstit = New CRecord 

   oConstit.Init REApplication.SessionContext 

   oConstit.Load 280 

   Set oParts = New cParticipants 

   oParts.Init REApplication.SessionContext 

   sSQL = "RECORDSID = " & oConstit.Fields(RECORDS_fld_ID) & " ORDER BY DATEADDED DESC" 

   Set oFilter = oParts.FilterObject 

   oFilter.CustomWhereClause = sSQL 

   For Each oPart In oParts 

       Debug.Print oPart.Fields(Participants_fld_DateAdded) 

       oPart.CloseDown 

   Next oPart 

   oParts.CloseDown 

   Set oParts = Nothing 

   Set oPart = Nothing 

   Set oFilter = Nothing 

End Sub

Two things of note here.

1. Firstly unlike most of the other top level objects the custom where clause is not specified in the CParticipant’s Init method but rather as a method of the filter object CParticipantFilter.

2. Secondly, and this was news to me, you are able to add an ORDER BY clause inside the custom where clause. I am amazed by this and wonder if it will really work in every single case where you can have a custom where clause. This supposes one of two things; either Blackbaud never sorts any of the collections, or the custom where clause is itself a subselect so that the order by clause never interferes with any other SQL that pulls the data.

I find it hard to believe in the former and the latter would affect performance (but may be true)