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.
To watch a video tutorial showing a Data Transformation on a Fund field, 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.
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.
Or for pay method on a gift where the incoming value is the type of credit card, we may want to transform it to “Credit Card” like this:
We have mapped the incoming “Credit Card” field to both Credit Type and also to Pay Method. However with Pay Method we transform the value.
You can choose to make an exact match, match a whole word or match any letters in the source. The target can now be completely replaced or partially replaced. This is shown below.
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. 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.
Date Transformations and Dynamic Dates
When a date field is selected on a mapping the option will be added to the field settings, allowing you to transform the incoming date format and also to specify dynamic dates instead of a fixed date.
In the screenshot below, the incoming date is formatted as “Year-Month-Day” e.g. 2017-12-02, but Raiser’s Edge wants the date to be in a local format. Typically in the US this would be 12/02/2017 or in the UK 02/12/2017. This transformation will change the incoming date into the format that RE expects it to be in.
The dropdown shows you a number of different alternatives but if the incoming format is not present then you are able to select “Enter you own format”. See Acceptable Date Formats for more information.
You can also choose the current date and plus/minus any number of days, months or years.
Please note that you still need to enter a date in the default area or map the field to an existing one, otherwise it will not show up in the review screen.
When it comes to reviewing the data in the review screen later on this will populate it with the correct value based on the current date.
However, please be aware that these options will only be available for actual date fields such as gift date or attribute date, other fields that contain fuzzy dates will not have these options. This includes birth and deceased dates.
Proportional Amount Options
When the field you select is a number e.g. the gift amount, you are given the option to use a proportion of the amount, rather than the full amount. This is very useful for gift splits, but also for event fees and donations.
There are two options.
- You can either use a percentage of the incoming value e.g. when you know that a certain percent of the gift should go to one fund and a different percentage should go to a different fund in a split gift you can set it up here.
- Alternatively if you know that there is a fixed fee for one part of the gift and the remainder should be for this part then you can set up the remainder here.
Data Transformations do work in conjunction to other Field Settings, so that other choices will affect their processing. For more information, please refer to this Knowledgebase article.
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.
In the above image, you can see a sample Data Transformation grid. To Export this template, you would select the option in the bottom right hand corner that points to the right.
If you want to Import your saved grid, you can use the opposing image (that points to the bottom left corner).