Tag Archives: SQL

Payment Type or Payment Method ID reference

Some time ago I wrote the mappings for gift type ids and their corresponding descriptions. This is useful in the API and also when interrogating the database directly. More recently I have needed to do the same thing with payment type. I find it very strange that in some scenarios this field is referred to payment type and other times it is payment or just pay method. Whichever you use the mappings are given below:

  Continue reading Payment Type or Payment Method ID reference

SSIS Convert

I have been working on a project to bring in a large amount of data into The Raiser’s Edge. Everyone else on the project has been using SSIS to do this and previously I would probably have manipulated the data in Excel, Access or for the most complicated data written a small application to convert the data into the format that I wanted before importing it into RE. Having used SSIS for a day now I am say that I am converted!

Continue reading SSIS Convert

Crash when Filtering on Missing Constituent Codes

I wrote some code for a client a while back and tested it thoroughly and everything worked fine. There was somewhat of a delay before the client was due to implement it and when they ran the application it crashed at the beginning. After some investigation we worked out what was going on. The code gathered together a collection of constituents that had two constituent codes. Only now one of them was no longer in The Raiser’s Edge. Continue reading Crash when Filtering on Missing Constituent Codes

Integrating Raiser’s Edge with non-Blackbaud Products

A lot of the work that I do involves the integration of third party products with The Raiser’s Edge. It is not always right to develop a custom application to perform this integration. There are many factors including the volume of data transfer, the complexity of data, how quickly the data needs to be integrated and of course what budget is available. This article attempts to outline the options available to Raiser’s Edge managers who need to integrate with non-Blackbaud products.

Continue reading Integrating Raiser’s Edge with non-Blackbaud Products

Battling on with the Batch API

I have on several occasions expressed my delight about the arrival of the Batch API. I am still enthusiastic but somewhat war weary having spent this passed week trying to solve problems that have appeared. Clearly the Batch API was not rigorously tested before it was released as otherwise the sort of errors I am getting would not have appeared. In case you have been struggling too or in case you thinking about using this functionality here is what I have been up against.
Continue reading Battling on with the Batch API

Gift Type IDs – A Reference

A while ago I wrote an article about filtering on gifts. I find myself regularly having to reference the knowledgebase link which itself links to an Access database file. Here is a snippet from that other article.

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.
For example:

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

I got somewhat tired of having to download that mdb file each time and some of the gift types stuck, many I forget. So here is the list (mainly for my own reference but for the benefit of anyone else who needs this list):

Gift Types ID Description
1 Cash
2 Pay-Cash
3 MG Pay-Cash
4 Covenant Payment
8 Pledge
9 Stock
10 Stock (Sold)
11 Pay-Stock
12 MG Pay-Stock
13 Pay-Stock (Sold)
14 MG Pay-Stock (Sold)
15 Gift In Kind
16 Pay-Gift In Kind
17 MG Pay-Gift In Kind
18 Other
19 Pay-Other
20 MG Pay-Other
21 Write Off
22 MG Write Off
24 Net Covenant
25 Gross Covenant
26 Deposited Convenant
27 MG Pledge
28 Adjustment
29 Covenant Write Off
30 Recurring Gift
31 Recurring Pay-Cash
32 GL Reversal
33 Amendment
34 Planned Gift

Membership – Retrieving information

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

Continue reading Membership – Retrieving information

Filtering and Sorting Participants – Just not Together

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.

Continue reading Filtering and Sorting Participants – Just not Together

Installing IDLookup

In this post I hope to offer some help in installing IDLookup. Many people have downloaded it but it may not be so straight forward to install. Note that these instructions can also be used for installing Query Manager.

If you do not know what IDLookup is here is a brief outline. IDLookup is  a plugin that I have created to lookup records in Raiser’s Edge given a list of constituents and their address details. This is useful where you have a third party list given that does not contain your constituent ids (such as many giving sites e.g. JustGiving, FirstGiving, CharityWeb, etc)  or cold lists that you want to ensure really are cold. The application can be found on Zeidman Development ->IDLookup. This is a demo version that is tied to the sample database.

Continue reading Installing IDLookup

Sometimes the API is just not enough

Sometimes I cannot quite get want I want from Raiser’s Edge using the API. Or if I can it is very slow to get the information. For example when you get a collection of top level records, say constituents, there are filter objects which work well, you can also use the custom where clause which works when there is not a filter parameter. However what do you do when you want a combination of different areas of The Raiser’s Edge all combined into one selection.

Continue reading Sometimes the API is just not enough