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 we 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.
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.
In the “Favourite Royal” attribute, if somebody enters “Elizabeth 2” it is matched on exactly and changed completely to “Elizabeth II”.
If somebody enters the whole word “Lizzie” then wherever this is found in the field it will be changed to “Elizabeth” so that it could be used for instances of Elizabeth I or Elizabeth II. Since this is a Partial Match, other text in the field will remain the same, so an instance of “Lizzie II” would result in a value of “Elizabeth II” and an instance of “Lizzie I” would result in “Elizabeth I” while just “Lizzie” would now contain “Elizabeth”.
Finally if somebody enters the lower case letters “phil” it will be changed to uppercase “Phil”. This would catch where somebody has entered “phil”, “phillip” or “phillipe”.
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.
In some cases you want to transform a set of values and any other values that do not match you want to set to 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 United States.
Another use is where the field is being used by many types of value. For example, there may be an event field for email actions consisting of values such as unsubscribe_click, read, open, forward etc. When mapping to a communication preference attribute you only want the unsubscribe_click to map to the attribute description of Unsubscribed. All other values should be blank. (For attributes this means they will not be added). This is shown below:
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.
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