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)