Filtering and Sorting Participants – Just not Together

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)
 

One thought on “Filtering and Sorting Participants – Just not Together

  1. There are a couple of issues with the solutions above. The first requires that all the items in the sort collection are closed down after they are worked with. What is more I am not convinced they are actually closed down while there is a reference to them in the sort collection. That is why when you shut Raiser’s Edge down it complains that not all the objects have had their closedown methods enacted.

    The second method works fine if you are a supervisor user. However I have experienced issues if you have restricted rights. You will of course need to have rights to participants and I am not refering to that. I have not done enough analysis to work out exactly which rights affect the code but when I use the second method for a non-supervisor user I get the error message:

    General ODBC Error: [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'AND'.
    Native error:156
    [Microsoft][ODBC SQL Server Driver][SQL Server]The cursor was not declared.
    Native error:16945

    I have however found a work around that is very good.

    Using the IBBSortableDataObjectCursor interface which the cParticipants collection implements I can set my sort field (Participants_fld_DateAdded) and sort direction (descending) so that the code snippet looks like this:

    Dim oSort As IBBSortableDataObjectCursor
    oFilter.CustomWhereClause = " RECORDSID = " & oconstit.Fields(RECORDS_fld_ID)

    Set oSort = oParts
    oSort.SortField = Participants_fld_DateAdded
    oSort.SortOrder = Descending
    bFound = False

    For Each oPart In oParts
    etc

Comments are closed.