Tag Archives: The Raiser’s Edge

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?

Update: This has now been released!

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!

Importacular and Regular Expression Transformations

Beginner, ,

Some Preamble

Regular Expression are really complicated. Even now I find it difficult to get my head around them. If you are new to them check out these two sites:

https://www.regular-expressions.info/ – a great tutorial and reference

https://regexr.com/ – a really good “playground” for testing your regular expressions

Overview

Importacular offers the user the ability to transform incoming data from one value into another. When we first started out this was simply a “from” value and a “to” value and if the incoming value matched the “from” it would change it to the “to”. That was very simple but effective. We soon realised that more power was needed so Importacular added partial matches or word matches (and clarified that the original was an “exact” match).

We also then added different replacement types too. These were “Complete” and “Partial” and later “Append” and “Prepend. If you selected “Complete” then all of the incoming value was replaced with the replacement value. If you selected “Partial” then only the matched part would be replaced keeping the remainder of the original value. “Append” and “Prepend” would add the replacement text to the end or the beginning of the original respectively.

Then we added RegEx – firstly for matching and then for replacing. The rest of this post describes how that works.

Matching

Importacular loops through each row in the data transformation grid and continues through each row unless the stop processing flag has been set.

If you choose a match type of RegEx you can put your RegEx in the “From Source” cell and Importacular will try and match on it. For example if you use this very simple RegEx:

.*

Importacular will match on any number of any character i.e. it will always match on what is found.

If you use this RegEx:

B[a-z]g

It will match on “Big”, “Bog”, “Bag” and also “Bkg” (as well as every letter from a to z).

If it finds a match it will try to replace the value

Replacing

When you replace using RegEx there are two thing to note.

  1. It does not matter how the match was made. It could be a RegEx, a complete, a partial or a word match. Replace is independent of how the match was made.
  2. Importacular does not use the classic replace mechanism of RegEx i.e. create a capture group often using parenthesis or sometime slash and parenthesis and then reference that group with a dollar e.g. $1 or $2. Importacular does not use this method!

Importacular’s replace works like this. It takes the incoming value and applies the regular expression to it in order to extract a value. That value is then used as the replacement text. For example if the incoming value is:

2022 Annual Appeal

We can extract the year by using the regular expression:

^20[0-9][0-9]

(Note that there are a number of different ways you could get the same information out using a RegEx. This is just one of them)

Say I have a US phone number and I want to get the area code. The phone number is in two different formats e.g. (415)-123-456 or 415-123-456. I can extract the area code using the following:

(?<=()[0-9]{3}|^[0-9]{3}

If I want to be really clever, I can use a second row in my transformation to transform the area code into the city. In this case after extracting “415” I would transform it to San Francisco.

Conclusion

The hardest part of using regular expressions in Importacular really is the regular expression itself. I won’t try to convince you otherwise. Hopefully this post will make it easier to use those regular expressions once you have determined what you need. Use the RegExr site (link at the top of this post) to test your matching and replace extraction before you put it into the transformation grid. Once in the transformation grid you can also check the review screen to see if it has worked as the resulting value will show up there transformed if everything has worked as expected.

An Audit Trail Update

Not Code, ,

When we released Audit Trail Cloud we were not able to show the name of the person who made the change. This has been a big issue. However recently Blackbaud updated their webhook API. For some (but not all) webhooks, Blackbaud now send through the id of the person that made the change.

So what’s the problem?

Firstly not all the webhooks send through the person who made the change. At the time of writing, only the constituent, gift and address webhooks include the changed by id. This leaves the records such as email and phone without this information.

What is more, we are only given these for add and change webhooks but not delete. This leaves us not knowing who deleted records.

Which id are we given?

This is a curious one. We are given the database view id for a user. This id is not obviously visible anywhere in the application (You can see it in query if you look at the SQL). In order to convert the id to a username we have to use the new (at the time of writing) NXT Data Integration API. The Get Information about a User method let’s you get user detail based on the id.

One caveat with this whole API is that it requires the user calling it is an environment admin.

What’s Next For Audit Trail?

We know that some new webhooks are coming soon and as soon as they do we will incorporate them.

We are also looking at inferring the changed by user. If a change is made to a constituent and then soon after a change is made to their email, we infer that the same person made that change… Coming soon!

Name Splitting in Importacular

Beginner,

Every so often we get a support question from a user asking us how they can import data like the following that appears in one Excel column:

“Dr David A Zeidman PhD”

We have invariably told them that this is very difficult to manage and that they would have to manually break up the one column into the 5 separate components (title, first name, middle name, last name and suffix) so that they could map them.

With Importacular 3.5 (available now for self-hosted organizations and coming within an indeterminate period of time for Blackbaud hosted users) you are able import combined fields like this.

The new constituent area settings allows you to split one field on your incoming file or data source into parts. The logic takes into consideration common titles, first name and last name (taken from US survey data) as well as suffixes. It also handles multi-word last names e.g. Von Trap or De La Fuente.

What is the best part of this? There is absolutely no extra cost to use this feature. It is included as standard irrespective of whether you have purchased any other data sources.

Download the latest version of Importacular now!

What is the difference between Chimpegration and Online Express?

Not Code, Opinion, , ,

What is the difference between Chimpegration and Online Express?

We are big Blackbaud fans and love much of what Blackbaud produces. Online Express is no exception. The fact that there is one application that can ask for donations, handle event registrations, manage memberships as well as allowing you to send out targeted and personalised emails is amazing. What is even better is that it does so with a complete integration into The Raiser’s Edge. – WOW!

Continue reading What is the difference between Chimpegration and Online Express? →

Just When You Thought Validatrix Could Not Get More Complicated…

Advanced, Not Code,

One of the problems of offering a tool that can create custom business rules for almost any scenario is that it has to have a lot of functionality. As we developed Validatrix we realised that some of that functionality was missing so we added it on. The problem with that of course is that the more you add on the more complicated the application becomes.

We have attempted to remedy this by offering the Validatrix Query Converter which does a good job of converting queries into rules and is often a good starting point for expanding a rule further.

We realised that we had missed out on a piece of functionality that was preventing us from creating a rule with a specific scenario. It is possible to all sorts of logic within one rule. We can check if a field value is exactly the same as another, if it is not the same as if it is greater than or less than etc. Continue reading Just When You Thought Validatrix Could Not Get More Complicated… →

Validatrix Query Converter Makes it even easier to protect your data

Not Code,

When we first developed Validatrix we had a lofty ambition that users of Raiser’s Edge would be able to protect their records using any combination of business rules. Out of the box, RE allows you to make some fields required. If the whole organisation wants city to be a required field all you have to do is going to configuration and set that field to be required.

However there are many limitations with that. What if we are only working with email only records. We may not have a physical address for those constituents. Validatrix makes it possible to combine criteria to validate records.

(Do they have an address block: YES
OR
Do they have an a postal code: YES)
SO
DO they have a city:

No? Well show a message.

What we soon realised was that for simple scenarios it was not that difficult to create rules. We also realised that after much practise and taking a look at our Validatrix Recipes area on ZeidZone, it became easier. Those starting out or writing more complicate rules needed a little more help.

That is why we developed the Validatrix Query Converter. Most, if not all DBAs can write a regular RE query. If they can write a query to give all the records where a message would be shown then this tool can convert the query into a Validatrix rule.

I have to say straight up here that not every single scenario is covered. There are some things (not many) that query can do that cannot be done in a rule. (There are so very many more things that Validatrix rules can do that query can do!). However this should get the beginner and those that are working with complex rules onto the right track.

If you have any questions about this product then please do not hesitate to get in touch with us here.

 

Adding an Education Record to a Non-Constituent Individual

Intermediate, , ,

I have been struggling to find a way to a an education record to a non-constituent individual relationship. There is a Blackbaud knowledgebase article here that outlines how you add an education relationship but it takes a lot for granted. Here is the gist of it:

Dim oEdu As CEducation2
Set oEdu = New CEducation2
oEdu.Init REApplication.SessionContext
With oEdu
   .Fields(EDUCATION2_fld_RECORD_ID) = 678   'Selects the record to add the Education record to
   .Fields(EDUCATION2_fld_SCHOOL_ID) = "Berry College"
   .Save
End With

However where does the 678 come from? For a constituent this is simple. It would be the constituent system id found under the Records_fld_Id field.

However what would it be for a non-constituent individual relationship. After all they can also have education relationship records.

I tried the field Individual2_fld_Id but the error I got back told me that I could not add an education to an organization record! Not very helpful.

In the end, with a bit of trial and error I worked out that you need to put the field Individual2_fld_Relation_id.

This is the id for the corresponding entry in the Records table which consists of both constituents and non-constituents.

Hope this helps somebody.

Working with Phones/Email without addresses in Raiser’s Edge 7.94

Intermediate, , ,

Prior to the release of The Raiser’s Edge version 7.94 I was working with all of our products to ensure compatibility with this latest version. If you have not heard (and if you have not heard where have you been hiding), this release of RE removes phones and emails from physical addresses.

When the concept of emails was new, it was possible that you would have an email address tied to your telephone provider or an email address specific to your place of work.  Your phone would either be at home or at work. Having these connnected to your physical address made sense. However it quickly became aparent that with the arrival of mobile phones and of email addresses that were accessible no matter where you were located, phones and emails (and for that matter all types of communication links) should be tied directly to the constituent record and not to a physical address. This is what has happened with the release of RE7.94.

This is a big shift and in terms of developing applications, we have had to allow for both possibilities so that our programs are compatible with users still on 7.93 and below and those that have made the leap over to 7.94.

The good news is that the old way of doing things still works in 7.94. You can still access phones via the CConstitAddress.Phones collection for an address. However you will probably want to access them how they are intended… Free from addresses.

This is done using the new interface IBBPhonesParent. This is implemented by CRecord, CIndividual2 and COrganization2. The collection of phones is a CConstitPhones object which contains the usual methods. You can iterate the collection to give you one CConstitPhone object but here is the problem.

For reasons that I don’t fully understand (I was told due to binary compatibility reasons) there are no properties or methods on the CConstitPhone object. Instead you have to convert this object to an IBBDataObject in order to access the Fields property. This is a real pain but to save myself some trouble I put together two extension methods for the CConstitPhone object which does this for me. (Unfortunately extension properties do not exist so that is why I cannot simply create an exact corresponding Fields properties. Those working with C# will be familiar with this as there is not a Fields property but rather  get_Fields and set_Fields methods)

<System.Runtime.CompilerServices.Extension()> _
Public Function Fields(ByVal phone As CConstitPhone, fieldConstant As ECONSTITPHONEFIELDS) As Object
   Dim dataobject As IBBDataObject = CType(phone, IBBDataObject)
   Return dataobject.Fields(fieldConstant)
End Function
<System.Runtime.CompilerServices.Extension()> _
Public Sub Fields(ByVal phone As CConstitPhone, fieldConstant As ECONSTITPHONEFIELDS, value As Object)
    Dim dataobject As IBBDataObject = CType(phone, IBBDataObject)
    dataobject.Fields(fieldConstant) = value
End Sub

With these extension method you can simply access the Fields methods on a CConstitPhone object in almost the same way as you would with other objects.

So here is an example of creating a new phone on a constituent record using the above extension code.

Dim constit As CRecord = GetConstituent()
Dim phonesParent As IBBPhonesParent
Dim phones As CConstitPhones
Dim phone As CConstitPhone

phonesParent = CType(constit, IBBPhonesParent)
phones = phonesParent.Phones
phone = phones.Add()
phone.Fields(ECONSTITPHONEFIELDS.CONSTIT_PHONES_fld_PHONETYPE, "Home")
phone.Fields(ECONSTITPHONEFIELDS.CONSTIT_PHONES_fld_NUM, "123-4567")
phone.Fields(ECONSTITPHONEFIELDS.CONSTIT_PHONES_fld_IS_PRIMARY, True)

constit.Save()
constit.CloseDown()
constit = Nothing

Global Merge More Accurately with The Mergician

Not Code, ,

We have just released an update to our successful global merge utility The Mergician. We have always had a way of searching for records in The Raiser’s Edge using complex and flexible criteria. IDLookup was one of the first plug-ins that we ever wrote and since it was first developed it has grown to contain some of the most powerful look up criteria around. A year or two ago we created IDLookup Dedupe, making use of IDLookup to search your database for duplicates. Well now we have gone one step further and brought all of this flexible, customization and downright lovely criteria lookup into The Mergician giving everybody the power to choose exactly how they dedupe their data.

There are 20 fields to choose from, many of which you can decide whether or not to search by the whole word or a specific number of characters. You can search using soundex (where names like Johnson and Jonson would be matched), you can search using first name and address line equivalents so that names like Bill match on William and Bvd match on Boulevard. If you are feeling very adventurous you can change the collation so that accented names would match with or without the accents e.g. Édouard would match Edouard.

Not only can you set up one criteria set, but you can set up multiples. You can decide that you want to narrow down the matches made from one to the next or you can decide that you want to select the criteria set with the fewest matches.

The Mergician still supports The Raiser’s Edge own dedupe algorithm so you can continue to create your merge report using that. You can also use our simple email matching tool too or you can use a third party agency to do the matching for you. However using our latest matching engine will give you real flexibility to match based on your own data without having to rely on mysterious algorithms or scoring charts.

Find out more about The Mergician or download the trial version today.