Create a CLR-based Global Change

This tutorial/example will teach you the basics of building a CLR-based global change using a Blackbaud Infinity SDK Global Change Spec (CLR) catalog template. The template creates a spec for a CLR-implemented global change operation/definition.

The source code is included with this example and includes a Visual Studio 2010 solution and catalog project named Blackbaud.CustomFx.Address.Catalog containing the XML specs/catalog items and vb class file. The code for the global change can be found within a file named AddressCoordinateTimeZone.GlobalChange.vb. The spec file is named AddressCoordinateTimeZone.GlobalChange.xml. Download the source code zip file here.

Prerequisites

Before you start, make sure you have a development instance of Blackbaud CRM installed along with a local install of the Blackbaud Enterprise SDK. For information about how to set up your development environment, check out the following guide here. I will develop on SDK version 2.94, which depends on Visual Studio 2010.

Blackbaud Enterprise SDK version 2.91 and higher takes a .NET 4.0 dependency for server side / catalog components that are created using the Infinity SDK Visual Studio catalog project template. However, Visual Studio 2008 only supports up to .NET 3.5. Visual Studio 2008 will not be able to target .NET 4.0, so you need to develop in Visual Studio 2010 for server side (Infinity SDK Catalog Projects) components for 2.91 and above.

You need to set up mapping credentials for your environment. The Mapping task within the Administration functional area allows you to set credentials for the mapping service. The out of the box "Address Geocode" global change definition depends on mapping credentials being set.

You need to configure and run an instance of the Address Geocode global change. That way our custom Address Coordinate TimeZone (Custom) global change will have address coordinate records to process.

For more information on global changes, see Global Change Definitions and Address Geocodes within our Administration Guide.

What You Will Build

You will implement a global change definition that supports editing and inserting custom time zone data for a constituent address's latitude and longitude known as an Address Geocode. You will author a GlobalChangeSpec that refers to a CLR class that implements the logic for the global change processing. CLR-based specs enable you to go beyond the capabilities of stored procedures by allowing you to create a CLR class to perform the business logic. We have chosen a CLR-based implementation due to the fact that we will make calls to a Google Time Zone API. For more information on the differences between SP- and CLR-based features, see CLR Versus SP Based Global Change.

The spec will also refer to form fields that define the user interface controls for the screens which allow the user to add and edit a global change instance.

Figure: Form fields defined within the GlobalChangeSpec will appear within the Parameters section of the Add global change and Edit global change screens.

After the global change is deployed, we will configure and start an instance of the global change. The goal of the global change is to populate rows within a custom table named USR_ADDRESSCOORDINATETIMEZONE. This table will hang off the ADDRESSCOORDINATE table via a 1 to 1 relationship. Below is a diagram of the custom table in relation to the relevant tables that ship with Blackbaud CRM.

Figure: AddressCoordinates (Geocodes) table and the relationship to the custom USR_ADDRESSCOORDINATETIMEZONE table

Here is a sample of the data that is populated within the USR_ADDRESSCOORDINATETIMEZONE custom table by our global change code.

Figure: Sample USR_ADDRESSCOORDINATETIMEZONE table records

Skills You Will Learn

Here's what you'll learn:

Time Zones Depend on Address Geocodes

Out of the box, Blackbaud CRM will allow a user to track a Geocode (latitude and longitude) for an address within the AddressCoordinates table. We would like to store time zone data in addition to the Geocode. To populate the time zone data, we will create a custom global change definition to select the latitude and longitude records from the database for a given set of constituent address records. After we retrieve the Geocode records, we will pass the latitude and longitude to a web service which will return a time zone value. We will take the time zone value and store it within a custom table named USR_ADDRESSCOORDINATETIMEZONE which will have a 1 to 1 relationship with the constituent address's ADDRESSCOORDINATES table.

Geocodes allow addresses to be mapped. An address's Geocodes are stored within the AddressCoordinates table. You can assign Geocode when you create new records or import records into the system. Our custom global change will depend on the AddressCoordinates table being populated with Geocodes. We will utilize an out of the box "Address Geocode" global change definition on the appropriate constituent addresses prior to running our custom global change.

Mapping Credentials

You will need to setup mapping credentials for your environment. The Mapping task within the Administration functional area allows you to set credentials for the mapping service. The out of the box "Address Geocode" global change definition depends on mapping credentials being set.

For more information on Global Changes, see Global Change Definitions and Address Geocodes within our Administration Guide.

Restrict the Number of Rows Processed

Our global change will build a dynamic SQL SELECT statement that retrieves columns from the ADDRESSCOORDINATES table. Form fields defined within the GlobalChangeSpec will allow the user to pick a selection of constituent records to limit the number of rows processed. In addition, we can further restrict rows by allowing the user to select whether to process primary addresses and/or new or changed address geocodes. In this way, we can improve processing time by limiting the number of rows processed.

The Experimental Google Time Zone API

According to the Google Time Zone API website, "The Google Time Zone API provides a simple interface to request the time zone for a location on the earth... Requesting the time zone information for a specific Latitude/Longitude pair will return the name of that time zone." This is an experimental site with usage limits. The global change that we are about to build is for education purposes only and not meant to be used in production. In other words, I am trying to show you how to build a realistic CLR global change, and this example is not meant for production. If you want to use this API for any production purpose, Google recommends that you purchase a Maps API for Business license.

To use the API you formulate an HTTPS request that contains latitude and longitude data that represents an address on a map. The data returned in the reply can be either JSON or XML format. Here's an example request and XML reply:

Figure: Time Zone API reply sample

At the heart of the request is the location parameter defined with a comma-separated latitude,longitude tuple (eg. location=-33.86,151.20) that represents the location to look up. Our latitude and longitude data is stored in the ADDRESSCOORDINATE table. In the XML reply in the figure above, note the time_zone_name element value of "Pacific Standard Time."

Now let's create a CLR global change...

Next Step: Adding the Project and Global Change Spec