User Guide

Installation:

  1. You will receive from us a URL to download the application zip file, and a license key, which is a text string.
  2. Create a new folder somewhere convenient on your machine, for example c:\overtheedge. Make sure you do not have any special characters or spaces in the folder name.
  3. Unzip the zip file into the folder, preserving the folder structure. The package includes all the software needed to run the application.
  4. Create a license file license.txt in the config folder. The content of this file should be just one line that looks like this: 
     apsona.OverTheEdge.licenseKey = xxxxxxx

    where xxxxxxx is the license key string.

  5. Open the folder c:\overtheedge\bin, and double-click the file start.cmd. This will start up the migration application in a command window.
  6. Point your browser to http://localhost:9002/re/index.html

Shutting down the application:

Type Ctrl/C in the command window running the application, and wait for it to shut down.

Targeting a Salesforce sandbox instead of a production org:

If you wish to migrate your test data into a sandbox instead of a production org, edit the file config\config.txt and change the occurrence of login.salesforce.com to test.salesforce.com. Specifically, you will find a line that looks like

proxyHandler.map[/sfdcService] = https://www.salesforce.com/services/Soap/u/20.0

This line says that Over the Edge should use Salesforce’s production org endpoint for SOAP services. If you wish to use the sandbox org endpoint, replace this line with

proxyHandler.map[/sfdcService] = https://test.salesforce.com/services/Soap/u/20.0

 

Tips and additional information:

  • Application folder structure

Folder Structure

*Any files not listed in the above table are for application internal use and should not be touched.*

  • If you have an existing installation of this tool, you can safely overwrite its files with the new version, with one exception. Before installing, please copy the file web\cfg\sourceFieldsMap.js to another location, and restore it after you have installed the new version. This will enable you to preserve any mappings you may have created via previous runs of this tool.
  • If you have run this tool once, and wish to run it again, please make sure to remove the keyMappings.js file that lives in the web\cfg folder under your installation folder. This file contains the mapping of Raiser’s Edge record IDs to Salesforce record IDs, and the mapping is rendered invalid when you delete your salesforce records.

 

High Level Mapping:

Constituents: Mapped to Contacts and Accounts. The Spouse field is created as a Contact, placed in the same Household (NPSP) and a Relationship is created between the Spouse and the Constituent. Households can be created automatically for every Constituent.

With NPSP3  OTE creates Household Account records for spouses in the same household.

Relations: Mapped to Relationships (for Contacts) and Affiliations (Accounts).

Events: Mapped to Campaigns.

Event Participants: Mapped to Campaign Members.

Actions: Mapped to Activities.

Appeals: Mapped to Campaigns. Campaign Members are created automatically.

Campaigns (RE): The Raiser’s Edge Campaign table is available as an object called RE_Campain in OTE. You can download its CSV data and import it into whatever object you wish.

Gifts: Mapped to Opportunities. Contact Roles are created automatically. Only records that do not have gift type “Pledge Pay Cash”, “Pledge Pay Stock”, “Pledge Pay Sold Stock” or “WriteOff” will be included among the records imported into Opportunities. A record which has one of those gift types will be imported into Payments.

Installment Payments: Mapped to Payments. Only records which have one of the above gift types will be imported. Also, any record with gift type “WriteOff” will have its corresponding “Written Off” checkbox checked in Salesforce.

Notes: Contact Notes and Donation Notes are supported. They appear among the importable objects. Importing these notes will automatically link them to the correct Contact or Donation record. You can also download notes as CSV files, as with all the other objects. Also, Raiser’s Edge allows notes without the “title” (or “subject”) field, but Salesforce does not. So when we transfer the note record over, if the note has no title, we take the first 30 characters of the note and make it the title in Salesforce, adding an ellipsis after it if needed.

Fund records, as seen in the above screen shot. But we do not yet have support for mapping Fund fields to Salesforce fields. For now, you can only download the Fund records in CSV format. These records can, of course, be imported into any Salesforce object, e.g., via Apsona for Salesforce.

Tributes: You will be able to download a CSV file (there are 2 attribute tables in RE, you might want to consider a custom object to address it) or map the tributes to custom fields on the Opportunity.

Attributes: There is now support for attributes for the Contact, Donation and Fund objects. Each attribute will appear as a column in the CSV data, so you can map the attribute to a custom field in Salesforce. So if you have 25 Gift attributes in your Raiser’s Edge database, there will be 25 columns in the Donation CSV data corresponding to those attributes.
ProspectRatings, ProspectFinancialData, ProspectPhilanthropy and ConstituentSolicitCodes: These are not mapped to any target objects in Salesforce, but you can download the CSV files and use them as needed. The output CSV includes the constituent id so that you can map directly to the id in Salesforce, assuming that you have imported the Raiser’s Edge ID for Contacts. 

Don’t need to import everything?

June 3rd update: 

  • When filtering the data records in preparation for migration, we are now able to filter based on the Contact/Organization’s date added field, its inactive field, as well as the Donation’s date added field. Please see the screen shot below.

Screen Shot 2015-07-03 at 19.08.54

If you apply a filter, say, for contacts created in 2012, that filter will carry through to all dependent records, i.e., only the gifts, campaign memberships and contact roles of the filtered list of contacts will be migrated when that filter is in effect. Thus, using this filtering mechanism, we can now migrate data in chunks. For example, the first chunk of data for all contacts created in 2011, then in 2012, and so on.

Note also that you can apply more than one filter term via the checkboxes.

Filtering the Contacts/Organizations to migrate: In the second step of the migration wizard, you can now specify a filter condition via a where clause for the Raiser’s Edge records table before importing. See screen shot below.

Filtering

The where clause will be applied to the records table, so it can use any fields from that table directly. You can also use fields from other tables via subqueries in the where clause. You can use this feature in multiple ways; here are some example use cases:

  • Before you perform a complete migration, you can run a small test batch to validate your mappings and other settings. You can do this, for example, via the where clause id <= 5 to select just a few records to migrate. The field id is the primary key of the records table in Raiser’s Edge, and is a positive number. So specifying id <= 5 selects at most 5 records (possibly fewer, if some have been deleted).
  • You can restrict the migration to just those contacts who have given donations since a specific date, e.g., February 1, 2004, via the where clause id in (select constit_id from gift where dte >= ‘2004-02-01’). This example uses a subquery on the gift table.
  • You can migrate your data in batches when you have a very large source database. In the first batch, you apply (for example) the where clause id <= 25000; In the second batch, use id > 25000 and id <= 50000; and so on.
  • If you need a “catch-all” where clause that covers all records, you can use id > 0.

Things to note:

  • When you type in a where clause and click the Apply button, OTE fetches the record IDs matching the where clause and remembers them in browser memory. It also updates the record counts in the “Objects to migrate” table below, so that you can see the effect of using the where clause.  If you reload the OTE page, the remembered IDs and the where clause will be forgotten.
  • If you specify a where clause, you can only migrate Contacts, Accounts, and objects dependent on these two (such as Relationships, Affiliations and Donations); you cannot migrate objects such as Campaign and Fund that do not depend on the Contact object. So the recommended flow is that you first finish importing all the non-dependent objects, and then use the where clause (multiple times if necessary) for importing the Contact-related objects.
  • If you’re having trouble with invalid mappings in step 3, remove the file web/cfg/sourceFieldsMap.js to cause OTE to forget its field mappings and start over.

Restore from a .BAK file:

1. Open Microsoft SQL Server Management Studio
2. Connect to the database (using the defaults)
ote4
3. You’ll get a screen that is a larger version of this:
ote5
4. Right Click on “Databases” and Choose “Restore Database”
Resulting Screen:
ote6
5. In the Source –> Device section, click the “…” button to browse to the .bak file.
Choose “Add” to browse to the file.
BEFORE:
ote7
AFTER:
ote8
6. Now your screen looks like this:
ote9
7. Click “Ok”
8. The status bar in the top right will show progress.
ote10
9. Expanding the correct DB will show all the tables:
ote11

Causeview Support:

OTE now automatically detects whether the target system uses Causeview, the Non-Profit Starter Pack (NPSP), or neither.

Causeview specific note: in Causeview, Tribute records are treated as special cases of Gift records. So a Tribute is basically a Gift with a few additional fields. And Over the Edge migrates all Gift records anyway; for this reason, you must migrate Tribute records after you have migrated the Gift records. During the Tribute migration, Over the Edge extracts the tribute fields from the Raiser’s Edge database and simply updates the corresponding fields in the Gift object in Salesforce. In other words, there are no new Salesforce records created when migrating Tribute data.

 

SQL Server Helpful Notes:

Enabling JDBC connections on port 1433

Over the Edge requires access to the SQL Server database via JDBC, and that connection happens on port 1433. When SQL server is not configured to accept such connections, Over the Edge will fail with a “connection failed” error in step 2. To fix this problem, you might want to follow the steps described in this stackoverflow article or this one at the Microsoft Developer Network.

Restoring a backup file

Sometimes, when restoring a backup file from another installation, SQL server complains about ownership and file names. In this example, I was trying to restore the backup file C:\Sridhar\RaisersEdge\RE7_db_db_201211141717-RPA.BAK into SQL server 2008.. So I had to run a script like the one below. The bold face section had to be added, and the target file names in the MOVE clauses had to be altered to match the source file name in each clause.

 

RESTORE DATABASE [RPA_RE7] FROM  DISK = N’C:\Sridhar\RaisersEdge\RE7_db_db_201211141717-RPA.BAK’ WITH  FILE = 1,  replace,

MOVE N’RE7′ TO N’c:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\RPA_RE7.mdf’,

MOVE N’re7_bio’ TO N’c:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\RPA_RE7_bio.mdf’,

MOVE N’re7_gift’ TO N’c:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\RPA_RE7_gift.mdf’,

MOVE N’re7_index’ TO N’c:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\RPA_RE7_index.mdf’,

MOVE N’re7_temp’ TO N’c:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\RPA_RE7_temp.mdf’,

MOVE N’RE7_log’ TO N’c:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\RPA_RE7.ldf’,

NOUNLOAD,  STATS = 10

GO

Using Over the Edge under Windows 10

Before running Over the Edge under Windows 10, please follow these steps:

  1. Install the current Java 8, if you haven’t already done so. We suggest downloading the Java Runtime Environment from http://www.oracle.com/technetwork/java/javase/downloads/jre8-downloads-2133155.html.
  2. Make sure that the installed Java version is available in your windows PATH variable. See this article if you need information about how to do this.
  3. Find the file bin\start.cmd in your Over the Edge folder, and replace its contents with the code below. Make sure not to introduce any line breaks other than the ones in the code below. 
    @echo off
setlocal
java -mx800M -Djava.library.path=%APP_ROOT%/lib -DappRoot=%APP_ROOT% -DconfigFilePath=%APP_ROOT%/config/rem_config.txt -classpath %APP_ROOT%/lib/apsona_rem.jar;%APP_ROOT%/lib/javax.servlet.jar;%APP_ROOT%/lib/ms-sqljdbc4.jar;%APP_ROOT%/lib/org.mortbay.jetty-4.1.4.jar;%APP_ROOT%/lib/rhino-1.7R3.jar -Djetty.port=9002 org.mortbay.jetty.Server %APP_ROOT%/config/jetty.xml
if %errorlevel% == 9009 mshta "javascript:alert('Cannot find java. Please make sure it is installed and available in your PATH variable.');close()"