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:

tvf_Gift_All
tvf_Gift_AnonymousOnly
tvf_Gift_ConstituentGifts
tvf_Gift_CustomWhereClause
tvf_Gift_ExcludeAnonymous
tvf_Gift_ExcludeDeceasedConstits
tvf_Gift_ExcludeInactiveConstits
tvf_Gift_ExcludeNoValidAddressConstits
tvf_Gift_ExcludeTaxClaimEligible
tvf_Gift_GLReversals
tvf_Gift_TaxClaimEligibleOnly
tvf_Gift_TributeGifts
tvf_Gift_UnPostedGifts
tvf_Gift_UseFilterObject

They can ORed in order to give several options combined e.g.

Code:

oGifts.init SessionContext, tvf_Gift_UseFilterObject OR tvf_Gift_ExcludeAnonymous

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:

Code:

Dim oGifts As New CGifts
Dim oFilter As CGiftsFilter  

oGifts.Init REApplication.SessionContext, tvf_Gift_UseFilterObject
Set oFilter = oGifts.FilterObject

We are now ready to start using the filter.

The properties can be split into groups

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.

Update: This list can now be found here.
For example:

Code:

oFilter.GiftTypes.Add 1, "Cash"
oFilter.GiftTypes.Add 2, "Pay-Cash"

The next group are composite properties. They are (or can be in some cases) added together. For example

DateRange
DateToUse
StartDate
EndDate

StartAmount
EndAmount

For example I would enter a date range, a type of date to use and the actual date

Code:

oFilter.DateToUse = dtu_GiftDate
oFilter.DateRange = bbDATE_SPECIFICDATE
oFilter.StartDate = 01/01/2007

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.

10 thoughts on “Filtering on Gifts

  1. Hi,

    I’m new to the RE API and I’m realy stuck here:

    I would like to filter constituents. The criteria is the bank account (as in the search screen).
    This is what I have:

    ————
    Dim oRECS As CRecords = New CRecords
    oRECS.Init(oCONN.SessionContext, TopViewFilter_Record.tvf_record_UseFilterObject)
    Dim oCONTS As IBBRecords2 = oRECS
    Dim oFLT As CFinRelFilter = oCONTS.FilterObject
    With oFLT
    — filter criteria should come here, but the only property available is:
    .ExcludeIDs
    End With

    ————–

    Thanks in advance.

    Eric

  2. Eric,

    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:

    Dim oRECS As CRecords = New CRecords
    sql = ” ID IN (SELECT CONSTIT_ID FROM CONSTITUENT_BANK WHERE ACCOUNT_NO_RAW = ‘” & myBankAccountNumber & “‘”
    oRECS.Init(oCONN.SessionContext, TopViewFilter_Record.tvf_record_CustomWhereClause, sql)

    This assumes that the account number does not contain any formatting just numbers otherwise you can use the ACCOUNT_NO field instead.

    David

  3. Hi David,

    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 ?

    Or is there an other way to filter constituents using the account no?

    Eric

  4. 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.

  5. hi David,

    Have you any experience with the FormatField function ?
    It has the fmtENCRYPTED and fmtENCRYPTED_DES as EFormatDescriptors constants.

    I’m trying to encrypt the account no in the customwhereclause string.

    Eric

  6. batch of gifts load in api examples ?

    Does any one have sample vb api code that loads a string of data that contains 1 or more gifts and create gift records for a consituent(s) ?

    Thank You

  7. There are several examples and some discussion on this site (select the batch tag) and on Blackbus and indeed the API help file comes with quite a good (if albeit a little simplistic) example

    David

Comments are closed.