Filtering on Gifts

One common complaint about the API is that it is poorly documented. I found that out myself when I went to use the gift filter object. This is a powerful object that allows you to filter a collection of gifts by its properties. The filter object has very many properties but very few of them are documented. There is one knowledgebase article and one FAQ entry showing how to use it.
Before we start looking at the gift filter object however there are other filters built into the initialization of the gift collection. They include:

This will allow you to use the filter object as well as excluding anonymous. This leads to the possibility of using the filter object together with the custom where clause to create a really powerful gift filter

What is more there is a secondary gift filter object called IGiftsFilter2 which the main CGiftsFilter implements. We may look at this in another article.

To start using the gift filter object we have to initialize our gift collection:

The first group is a collection. Here you add ids to the collection. Note that these are system ids, not the values you see on screen on the record but those that are viewable on the properties page.
AppealIDs
CampaignIDs
ConstitRecordID
FundIDs
GiftIDs
GiftTypes

So for example if my appeal has a system id of 2 I would add it as follows:

Code:

oFilter.AppealIDs.Add 2, CStr(2)

A word on the GiftTypes filter. Here you can filter on cash, pay-cash, pledge, etc. However this is not simply the text but rather an id that the type refers to. This too is not well documented. There is a link in the knowledgebase (http://www.blackbaud.com/esupport/es…r=0&id=BB17413) that gives this information or it can be derived by looking at the SQL or a query.

We only use the start date here has the end date is ignored for a specific date.

The remainder are miscellaneous properties of varying importance.

SoftCredit – How has the gift been soft credited; donor, recipient or both
SoftCreditAmountMethod – How has the soft credit been distributed, full amount to all, split evenly, use amount in grid

ExcludeZeroAmount – excludes zero amounts

GiftConstituentCode – filters by the constituency code on the gift

MatchCredit – how matching gifts are credited; constituent, matching gift organization or both

SubtrackBrokerFee – whether the broker fee is subracted (true or false)
UsePledgeBalance – use the pledge balance (true or false)
UseSaleAmountForSoldStock – use the sale amount for sold stock (true or false)
UseSaleDateForSoldStock -use sale date for sold stock (true or false)

And then these two… (Let me know if you can enlighten me)

UseCFAFiltersForAmount
ConstitIDPrepSQLName

If you have anything to add then please do so. This is by no means all encompassing.

You are using the wrong filter object for constituents. You need to use the CRecordsFilter object. The CFinRelFilter is used when you want to filter a collection of bank relationship records i.e. objects of type CFinancials2. However if you bring up the CRecordsFilter you will not find bank account number there.

What you are going to probably have to do is to use a custom where clause something like this:

Thank you for your quick reply.
As of version 7.81 the account numbers are encrypted in the db. Using the CustomWhereClause queries the raw data. Passing clear text as criteria doesn’t return any records.

Is there a way to encrypt the clear text on the fly so the account number passed in the whereclause matches the raw (encrypted) data in the table ?

Ahhh, yes of course. I forgot about that one. I am at a loss as to what to suggest. It does seem strange that there is a way of searching for a constituent by account number on the search screen but no obvious method in the API.