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.
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
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
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
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.
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
Sorry, I have not used those before.
David
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
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