One very useful way of loading a collection of records is using the custom where clause. For example if you want to find a list of constituents who are born in a certain year you could write the following

Dim oRecords As New CRecords
oRecords.INIT SessionContext, tvf_record_CustomWhereClause, "BIRTH_DATE LIKE '1950%'"

This is the only really effective way of doing this without returning a list of all constituents filtering them in the code (much less efficient).

There are lots of examples of this in the knowledgebase and in the forums or on this site.

I had used the membership module previously but of all the Raiser’s Edge modules this was the one that I was least familiar with. I needed to load a collection of membership records with specific criteria. My first thought was to use the filter object that exists for most of the other top level objects (this is very powerful for gifts). However for membership this does not exist.

My next thought was the custom where clause (thankfully this did exist). I looked at the membership table and there were very few fields of any real use! You can filter on date added, date joined and the regular property fields (added by, last changed by, etc) but I wanted details about the membership standing and their category. I started to do a sub select to get that information but nothing worked. I stumbled upon a Memberships view in the schema and it turns out the custom where clause maps to this view and not to the Membership table (I did not find one piece of documentation about this so I was fortunate to stumble over it).

Using the custom where clause I was able to get direct access to the standing. I should point out that if you only want records of one standing (and that is the only criteria you want) then you can use this in the filter parameter of the init method on CMemberships.

To get the category I still needed to write a subselect. This spans a couple of tables but is nevertheless not too painful. The code is shown below:

Dim oMems as New CMemberships
Dim sSQL as StringsSQL = "(Memberships.Standing = 1 OR " & _
"Memberships.Standing = 2) AND " & _
"Memberships.CurrentTransactionID IN " & _
"( " & _
"SELECT MembershipTransaction.ID " & _
"FROM MembershipCategory INNER JOIN " & _
"MembershipTransaction ON MembershipCategory.MembershipCategoryID = " & _     "MembershipTransaction.Category INNER JOIN " & _
"TABLEENTRIES ON MembershipCategory.CategoryID = TABLEENTRIES.TABLEENTRIESID " & _
"WHERE TABLEENTRIES.LONGDESCRIPTION = 'My Category' " & _
")"
oMems.Init SessionContext, tvf_Membeship_CustomWhereClause, sSQL

I have not examined all the other custom where clauses but would be interested to hear if anyone has had a similar experience with the other less used objects.