Tag Archives: Query

Working with the SKY Query API in Chimpegration

Beginner, Not Code, , ,

Blackbaud recently added the SKY Query API for Raiser’s Edge and Financial Edge. At first I was not really sure how this would be of benefit to our products. We have worked with Lists in Raiser’s Edge and on the database view we have generated static queries of records that have been processed. I never thought that we had a need for adding criteria to queries.

Now that I have seen the new Query API, I have been inspired.

How have we got around the lack of a query API so far?

In Chimpegration we push data from Raiser’s Edge to Mailchimp. In order to decide which records to push, we let the user select an NXT list that has previously been created. There are some issues with this.

  • Firstly, the list selection criteria is limited. The user cannot, for example, specify that a constituent with no email address or a blank email address should be ignored.
  • The list functionality does not allow you to choose specific output fields. We offer a limited range of fields that we think that the user may want to export.
  • The lists are static. If you want to update them, you can, but this prevents data being pushed to Mailchimp according to a schedule. (There are some workaround involving Queue but these are awkward).

How does the Query API solve this?

The query API allows you to programmatically list all queries and to load one of them in particular. You can then run the query and fetch the results. This makes it fully dynamic. Scheduled data exports to Mailchimp would be up to date with the latest information.

The user can choose the output data. Whereas previously they have been limited to the areas we offer the user, now they can choose any value available to them in a query. Most organisations won’t want to push a membership attribute or a gift notepad to Mailchimp but there is bound to be one out there that wants to do something like that or some other option that we had not considered. With the full range of output fields they are no longer restricted to what we offer them.

The same goes for criteria. Previously the user was restricted to the fields available in lists. Now the whole range of query fields can be a part of the criteria. If an organisation only wants to export constituents attending a specific event with a large t-shirt size, now they can!

What else can we do with Query API?

In Chimpegration Classic and in Importacular we look up constituents with criteria sets. It has been much simpler to look up constituents with a wide range of criteria. There is some scope for this based on the constituent list API endpoint. However, the Query API really gives this some muscle.

At first I thought that, even if we could do it, it would not be practical to create a query each time the user wanted to use criteria to search. We would end up creating a lot of queries saved in Raiser’s Edge. It is not certain that the user would have rights to delete a query. It just felt wrong.

However, the Query API includes the ability to generate a query on the fly. When including the filter, output fields and sort values in the json payload, the query can be run without explicitly saving it to the organisation’s environment. This is a real game changer.

The SKY API documentation does suggest that this should not be used instead of the constituent list and constituent search endpoints as they are optimised for search. However being able to search on more obscure areas of Raiser’s Edge certainly adds a lot of power to look up records that was previously missing.

When are we releasing the new Chimpegration functionality?

We are actively developing this functionality. However the Query API is still in preview so it is uncertain when this will be released. We may release it also with the caveat that our functionality is in preview and may break at any time (due to changes in the Query API). This is a matter of a few weeks though so watch this space for a demo!

Moving over to SKY API

Beginner, Not Code, Opinion, , ,






This blog has mainly consisted of the COM based RE7 API and my musings of all things Blackbaud related. I am sure that the latter will no doubt continue but I have realised for a while now that as time goes on the RE7 API is becoming less and less relevant (although not entirely so) and that there is a natural progression towards the SKY API.

In general there is probably less to be said about SKY API. Firstly Blackbaud have been doing a much better job at documenting it than they ever did with the RE7 API. They are also putting a lot more thought into it so that there are far fewer inconsistencies (so far at least) than there ever were with the RE7 API. Where there are difficult, new, areas they have written up good documentation or blog posts to explain. In short they have made my job here somewhat redundant… Well thanks a lot Blackbaud!

Actually, yes, thank you. I would much rather a clean usable API than one where I have to write up blog posts explaining how to do things. I am sure that there will be moments but there will quite possibly be fewer of them.

One topic that I think deserves some discussion though is the porting of existing functionality from RE7 to SKY. Those of us that have products written for RE7 are keen to see the functionality available on SKY in order that we can port the solutions over.

I have been very keen to transfer Chimpegration but one of the stumbling blocks has been the lack of bulk data processing on SKY. Specifically the ability to return filtered lists of constituents. On many platforms this means simply specifying a last data changed or a keyword search. On RE7 though it is possible to make use of a query to retrieve that information. The user would themselves set up the criteria in the query and the application would allow the user to select that query.

There is some discussion on the SKY API forums about how imperative it is that this be ported over to SKY and that we should be allowed to once again select an existing query.

Despite really needing this functionality for Chimpegration, I am not convinced that this is the best course of action for SKY. This new API should embrace a general approach to this problem. It cannot be based on RE7’s query module. There is definitely a need to generate lists based on complex filters and criteria and that should be exposed somehow to the developer community but to simply port the existing RE7 functionality to SKY would be short-sighted and not take into consideration the other applications what will one day make use of the same API. I want an API that will work with RE, BBCRM, ETap and others. To simply port queries to SKY would confuse the issue and make for an API that is not consistent.






Small improvements that I like about Blackbaud Enterprise CRM

Not Code, Opinion, , , , , , ,






Blackbaud Enterprise CRM (eCRM or BBEC as it seems to be called interchangeably) is enormous. It is a big change to The Raiser’s Edge but then that is not surprising as it is not actually the replacement for The Raiser’s Edge 7. It is an application way beyond RE 7. Nevertheless you cannot help comparing the two as long as there is no RE8 to compare to. As a software developer it is a whole new paradigm. Of course even if you know how to develop software customisations for BBEC you still need to know how the program works from a user perspective. That is why I am not only learning a new development environment but I am learning a whole new program. Some of the concepts are very similar, some are different. As I learn I have come across some quite small changes that, for whatever reason, fill me with a “wow that’s great” feeling. These are not earth shattering improvements but just things that I am sure people will appreciate. So this post is dedicated to the small differences. Continue reading Small improvements that I like about Blackbaud Enterprise CRM →






Lookup by constituent system id – a new free plugin

Beginner, , , , , ,






There was a post on Blackbus recently where a Raiser’s Edge user was having trouble mapping the output of a third party system to RE. The other system had the constituent system id and did not have the import id or the constituent ids so importing the data directly into RE was proving to be a problem. It would also be difficult to do any kind of lookup natively in RE unless it was done manually through a query.

I suggested that this was a straight forward solution if you were to write a plugin. In fact I felt that it was so straight forward that I would just write it. So here it is a new plugin that takes a system constituent id and outputs the constituent and import ids appending them to a copy of the input file.

Continue reading Lookup by constituent system id – a new free plugin →






Gift Type IDs – A Reference

Intermediate, , ,






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






Adjust that tax claim at your peril

Beginner, , , , , , , , ,






This is perhaps more of a rant than anything else but after being so pleased with the way Blackbaud introduced Batch into the RE:API I now found myself stuck with another piece of Raiser’s Edge functionality that I am less than pleased with.In the UK Gift Aid is big business. In the States (and possibly elsewhere) the donor can claim tax back from charitable donations. In the UK it is the charity that can claim the money back from the government for UK tax payers so this can amount to a very large sum of money if it is handled properly. There have been several changes to Gift Aid over the past four or five versions of Raiser’s Edge. One problem that was not addressed until recently was when Gift Aid had been taken in error and then claimed back from the Inland Revenue. If a gift was given in error then the Gift must be adjusted or written off and the Gift Aid must be reversed. If the Gift Aid was claimed in error (for example the donor was not a tax payer) then the Gift Aid alone must be reversed.
Continue reading Adjust that tax claim at your peril →






API 7.82 – The Silent Revolution

Not Code, , , , , , ,






I normally keep up with the latest changes in the Raiser’s Edge version.  I thought that the jump from 7.81 to 7.82 was not that great. I knew that there were fixes so that RE would work with Office 2007, there were some more changes to optional modules (I was only really familiar with events), there were new names for items in query and export, another change to Gift Aid and that was about it. At least that was it until I saw an item on the conference pages talking about the new Batch API code that had been made available. What?? Batch API! This is something that I have been asking for forever and it completely threw me when I just stumbled across it. Continue reading API 7.82 – The Silent Revolution →






Custom user defined business rules

Beginner, , , , , , ,






The idea of creating user defined business rules has been discussed in various threads at Blackbus and on the Blackbaud Raiser’s Edge forums (see User Defined Business Rules for example). The whole functionality  is really useful but there are clearly limitations.

One really good use of VBA is to perform this very task. For those not familiar with the build in version it allows you to select a query (constituent, gift or action) and for a all or a limited number of security groups it allows you to display a message on opening the record. This same functionality can be repeated using VBA. Of course you are able to query on a lot more, or perform more than simply display a message. Continue reading Custom user defined business rules →






How I would improve the API

Not Code, , , , , , , , , , ,






If I have often wondered if I were in charge of the API at Blackbaud how I would change it. Well unfortunately I don’t wield that kind of power and secondly I am not sure that a major overhaul is required. It is clearly not a modern API being COM based but for the main it works well. However there are still quite a few areas that I would improve upon. I don’t suppose that these will ever happen. Changing the API is no doubt lower on Blackbaud’s list of priorities. With the imminent arrival of the Infinity platform (and, yes, its name does seem to have influenced the pace at which it is being released) , the ability to develop any interface into the system seems a real possibility. RE API by comparison appears somewhat staid and limited. I would imagine it no longer commands the attention at Blackbaud those of us using it would like.

That being said I would love to be proven wrong so Blackbaud if you are listening here are the areas that I would change: Continue reading How I would improve the API →






Installing IDLookup

Not Code, , , , , , ,

Update: IDLookup is a legacy application that is no longer available for download. Almost all of the functionality has been incorporated into Importacular and we recommend that product instead.

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.

When you download IDLookup from Zeidman Development you receive a zipped up file consisting of one installation file. After extracting the installation file (IDLookup Setup.msi) double click on it and follow the guide that will install all the files in their correct location. In the plugins directory you will then see a link for IDLookup. However we are not ready to start just yet.

Continue reading Installing IDLookup →