Data Transformations

Data Transformations allow you to work with data in your Data Source and manipulate it as it comes into Raiser’s Edge. The options available to you within Data Transformations differ based upon the type of field that you’re importing to within the Raiser’s Edge. All Data Transformations are case sensitive and the order of processing follows the order of the grid. You must select a Source Field Value in your mapping template in order to use a Data Transformation.

To watch a video tutorial showing a Data Transformation that uses a Donation Form Page name to assign the correct Fund to a gift, click here.

The functionality of Data Transformation depends upon the field type for the data that you are mapping to that specific Raiser’s Edge field. Click on the links below to read more about what options are available for each of the different field types within the Raiser’s Edge.

Table based fields (such as – Fund, Appeal, Constituent Code, Solicit Code, etc.)

Text based fields (such as a Gift Reference, Last Name, Organisation Name, etc.)

Checkbox or Yes/No fields (such as ‘Is Inactive’, ‘Has no valid address’, etc.

Date based fields (such as a Gift Date, an Action Date, etc.)

Amount based fields (such as a Gift Amount, etc.)

You can dynamically change source data to accurately import inline with your RE standards. So for example if you use short codes for states/counties you might change all ‘New York’ to ‘NY’ as shown below. The ‘From Source’ indicates the value in our Data Source (ie File, Qgiv, MobileCause, Classy, etc.). The Change to Target reflects what you would like to import into the Raiser’s Edge.

You can choose to make an exact match, match a whole word or match any letters in the source. To read more about the different types of matches available, click here.

Casing always must match exactly for any Match type. Additionally, Matches will take into account any Text Casing selections that you have made.

You will also need to select a Replace type. All tables and Yes/No fields must be set for a Complete replace, but Text based fields have additional options. See this knowledgebase article for a full list of options.

Exact Match example: In the “Favourite Royal” attribute, if somebody enters “Charles 3” it is matched on exactly and changed completely to “Charles III”. A value of Charles would not be changed.

Word Match example: If somebody enters the whole word “Kate” then wherever this is found in the field it will be changed to “Catherine” so that it could be used for instances of Kate or Duchess Kate. Since this is a Partial Match, other text in the field will remain the same, so an instance of “Duchess Kate” would result in a value of “Duchess Catherine” and an instance of “Kate, Princess of Wales” would result in “Catherine, Princess of Wales” while just “Kate” would now contain “Catherine”.

Any Match example: Finally if somebody enters the lower case letters “will” it will be changed to uppercase “Will”. This would catch where somebody has entered “will”, “william” or “willie”.

For more information on how text casing affects Data Transformations, see this Knowledgebase article and for more information on the order of processing with several settings in place, see this Knowledgebase article.

Regex – Regular Expression

In addition to the functionality mentioned above, Regular Expressions (RegEx) allow you to do even more. Regular Expressions are formulas that will allow you to control how a field value can be changed to meet your Raiser’s Edge needs. They can be used as either Match Types or Replace Types.

We have written a number of Knowledge Base articles to help you use and understand this incredibly power tool. We have included some do them at the bottom of this User Guide.
Watch this video showing the possibilities of using Regular Expressions.

Catch All

In some cases you want to transform a set of values and any other values that do not match you want to set a default. In this case, you would create a row for a “catch all” value. This is useful, for example, when you have a default value e.g. for country you may have UK -> United Kingdom, USA -> United States, etc. and any other should go to Canada.

Another use is where the field is being used as an either/or field. A common use for this is with the Key Indicator for a Constituent record. When working with a file that contains an Organisation Name for records that are Organisations, but that field is blank for those records that are Individuals, we can use a Catch All as an either/or to assign the Key Indicator (to see this example in full, please refer to this knowledgebase article).

Only one row in the grid can be a catch all. The “From Source” cell is disabled for that row. To read more about the Catch All function, click here.

Stop Processing

The Stop Processing function allows you to set a row in your data transformation to be the final change should the field match multiple Source Field options. The row that has this box checked will be the final change for any records that match that particular row. Any source fields that do not match that row will process beyond that row in the grid. You can read more about this function here.

Import/Export the Grid

When working with Data Transformations, you can Export/Import the grid. This is helpful if you are using the same Source field options in multiple locations throughout your template or if you want to use the same options in another template. To see a video of how to export/import a grid of Data Transformations, click here.

Standard Export – Exports the grid to a .xml file that can be imported into any Data Transformation grid
Standard Import – Imports a .xml file into a Data Transformation grid
Excel Import – Imports an Excel file into a Data Transformation grid

Data Transformations

Importacular User Guide

Catch All

Stop Processing