{{last_updated_at}} by {{last_updated_by}}

• Table of contents
• Exporting Raiser's Edge for CiviCRM
  • Do you use Pentaho Kettle?
  • Export tool - general guide.
  • Constituent Based Exports
    • Contact Information
    • SQL
    • Export tool (NOTE: This ONLY gets constituents).
    • Constituent Codes
    • Solicit Codes
    • Addresses
    • Primary Business Information
    • Phones/E-mail/websites
    • Relationships
    • Solicitor Relationships
    • Attributes
    • Salutations/addressee info
    • Other constituent tables:
  • Contribution-related exports
    • Contributions/Gifts
    • Soft Credits
    • Solicitor, Gift
    • In Honor/Memorial Of (aka Tributes)
    • Pledges
      • Matching gifts
      • Pledges as contributions
  • Campaigns, Appeals, Packages
  • Actions
  • Action Notes
  • Events
  • Notes
  • Localization formats
  • Financial accounts
  • Misc
  • Media
    • Exporting from RE
    • Importing into CiviCRM
  • And More
    • Tables that Civi doesn't have a direct counterpart for
    • Code Tables/Option Groups/Option Values
  • Deciphering MS SQL
    • Looking Up Functions
    • Looking Up Stored Procedures
    • Addressee/Postal Greeting/E-mail greeting
    • Things I see that RE does better than Civi:

Exporting Raiser's Edge for CiviCRM¶

There are two basic approaches to exporting RE data. There's the built-in export tool, and there's direct SQL interaction. This document will try to cover both approaches where possible. The Export tool has a lower barrier to entry, but a) there's some data you can't export with the tool, and b) the data will be denormalized, requiring additional transformation compared to extracting normalized SQL data.

Note that there's a good video on this topic by Young-Jin from Emphanos, LLC, here: http://sf2013.civicrm.org/migrating-raisers-edge-civicrm

Do you use Pentaho Kettle?¶

If so, you can use my Raiser's Edge to CiviCRM transforms, available here: https://github.com/PalanteJon/civicrm_kettle_transforms

Export tool - general guide.¶

The Raiser's Edge Export tool is on the left toolbar when you first enter Raiser's Edge.

From the tool, you will create a number of exports. When you first create an export, you'll be asked a number of questions, including Export Type (Constituent, Gift, etc.), a checkbox to include inactive records (check this), and an export file type (select CSV).

For most export, select Constituent as the Export type. This is the "base table" - all records will be joined relative to it.

Constituent Based Exports¶

Contact Information¶

RE differentiates between constituents and non-constituents in their system. If you create a new contact, they're a constituent - but then you might decide to add a spouse or employer record, which is NOT considered a constituent, and doesn't show up in most queries. Notably, non-constituents aren't exported when using the Export tool and your base table is "Constituent".

SQL¶

If extracting directly from SQL, SELECT * FROM RECORDS.

Note that you can extract only constituents by adding WHERE IS_CONSTITUENT = -1. For a Civi migration, I recommend importing all contacts.

Export tool (NOTE: This ONLY gets constituents).¶

Tab 1. General:

• Include all records.
• Head of Household processing: Export both constituents separately.
• Check all of the "Include these Constitutents": Inactive, deceased, no valid address

Tab 2: Output.
First, expand the "Constituent Information" in the left pane, and add every field to the export. Do the export (as a CSV).

Constituent Codes¶

In RE: Found at the bottom of the "Bio 2" tab.
In SQL: CONSTITUENT_CODES maps to "GroupContact". TABLEENTRIES stores the codes ("groups"). In my case, SELECT * FROM [CCR_July_snapshot].[dbo].[TABLEENTRIES] WHERE [CODETABLESID] = 43 did the trick. YMMV - see "deciphering stored procedures" below.

Export as one to many, below.
These map to "groups" in Civi - can also be mapped to "tags" if you don't need to track the begin/end date on them.

No need to export these fields:
System Record ID
Import ID
As of Civi 4.4.6, there's no way to import Group Begin/End dates via API, you need to do it via direct SQL.

Solicit Codes¶

These can map to groups - but also may map to privacy preferences or custom fields (e.g. Email Only, Do Not Solicit)

SQL to extract solicit codes:

SELECT RECORDSID AS external_identifier, LONGDESCRIPTION as solicit_code 
FROM CONSTITUENT_SOLICITCODES JOIN TABLEENTRIES ON SOLICIT_CODE = TABLEENTRIES.TABLEENTRIESID 
WHERE TABLEENTRIES.ACTIVE = -1

In my copy of RE, the CODETABLESID is 5044, so to get a list of all solicit codes, use:

SELECT LONGDESCRIPTION, ACTIVE 
FROM TABLEENTRIES 
WHERE CODETABLESID = 5044 
ORDER BY SEQUENCE;

Addresses¶

SQL tables: ADDRESS, CONSTIT_ADDRESS

Addresses are a many-to-many relationship in RE.
Not all addresses in the database are visible in RE. Addresses where the INDICATOR field is 1 or 7, for instance. Make sure to look your data over and filter those out accordingly. Alternatively, you may want to handle them by creating address sharing in Civi.

Source: http://www.kb.blackbaud.co.uk/articles/Article/41141
The address Indicator field is located on the CONSTIT_ADDRESS table and can have the following values:

0 = Alternate
1 = Contact
2 = Preferred (Primary Address entered on the Bio 1 tab of the constituent record)
3 = Business
4 = Spouse Business
5 = Spouse Preferred
6 = Spouse Alternate
7 = Relationship

CONSTIT_ADDRESS.TYPE and CONSTIT_ADDRESS.INDICATOR both have a bearing on location type. TYPE is what appears in the RE UI; however it's calculated based on the indicator. An indicator of 3 is always a shared address from the relationship to "primary business information". Adding a work address manually will always give an indicator of 2 if preferred or 0 otherwise.

When you create an address on a related non-constituent, the address's CONSTIT_ID is actually the constituent's! E.g. if John Doe works for Acme Corp, an address added to Acme Corp will have John Doe's CONSTIT_ID. Even more confusing, a blank address with the Acme Corp CONSTIT_ID will be created. To decipher this, you need to look at the CONSTIT_RELATIONSHIPS.CONSTIT_ADDRESS_ID field.

Here's the SQL I use for getting address data in Kettle:

SELECT
  ADDRESS_BLOCK
, CITY
, POST_CODE
, "STATE"
, CTY.LONGDESCRIPTION as country
, ADDRESS_ID
, ca.CONSTIT_ID
, PREFERRED
, LOC_TYPE.LONGDESCRIPTION
, ca.DATE_FROM
, ca.DATE_TO
, SENDMAIL
, ca.ID AS CAID
, cr.IS_EMPLOYEE
, cr.IS_PRIMARY AS IS_RELATIONSHIP_PRIMARY
, cr.RELATION_ID
, ca.INDICATOR
FROM ADDRESS a
LEFT JOIN TABLEENTRIES AS CTY ON CTY.TABLEENTRIESID = COUNTRY
JOIN CONSTIT_ADDRESS ca ON a.ID = ca.ADDRESS_ID
LEFT JOIN TABLEENTRIES AS LOC_TYPE ON ca."TYPE" = LOC_TYPE.TABLEENTRIESID
LEFT JOIN RECORDS r ON ca.CONSTIT_ID = r.ID
-- This next join is needed to handle address sharing with the business
LEFT JOIN CONSTIT_RELATIONSHIPS cr ON ca.ID = cr.CONSTIT_ADDRESS_ID AND ca.CONSTIT_ID = cr.CONSTIT_ID
WHERE INDICATOR <> 7 AND ADDRESS_BLOCK IS NOT NULL

Primary Business Information¶

From "Bio 1", if you press the "Business" button, you can add a related business and its address - but if you check the "Primary Business Information" box, the address will actually have a CONSTIT_ID tied to the original contact. Very confusing! To properly migrate this address, you'll need to link the address using the CONSTIT_RELATIONSHIP table.

Phones/E-mail/websites¶

Email and websites are stored in the PHONES table along with phone numbers.

In older versions of RE (possibly up to 7.93), phones were tied to addresses, not contacts. In current versions this is no longer true, but the fields are still present, which is a red herring.

Notes:

• You can NOT have duplicate phone types in RE, so no need to try and catch multiple "Home" numbers!
• Oh - except that one contact can have two home phone numbers on two different addresses.
• Don't forget to filter out duplicate numbers/e-mails/etc. when someone puts the same phone number on two different addresses.

This SQL gets me a useful list of phones and e-mail for further processing in Kettle (for RE 7.96):

SELECT DISTINCT
PHONES.CONSTIT_ID
, NUM
, DO_NOT_CALL
, LONGDESCRIPTION AS location_type
, PHONES."SEQUENCE"
, PHONES.PHONESID
FROM PHONES
LEFT JOIN TABLEENTRIES ON PHONETYPEID = TABLEENTRIESID
LEFT JOIN RECORDS r ON r.ID = PHONES.CONSTIT_ID
WHERE PHONES.INACTIVE = 0
AND CONSTIT_RELATIONSHIPS_ID IS NULL
--Sequence so we get primary number correct
ORDER BY PHONES.PHONESID, PHONES."SEQUENCE"

Relationships¶

Relevant SQL table: CONSTIT_RELATIONSHIPS

Relationships are different in Civi and RE in the following significant ways:

• Relationships don't have to have a relationship type.
• The A-B relationship doesn't have to have the same relationship type as B-A (e.g. if my relationship is "parent", the reciprocal relationship could be "son" or "daughter".
• Related contacts need not have their own constituent record (though they can). If they don't have their own constituent record, they nevertheless have a record in RECORDS, they're just not a constituent.
• There need not be a relationship type at all. This doesn't make sense, except that:
• There are hardcoded fields for IS_SPOUSE, HON_MEM_ACKNOWLEDGE, IS_HEADOFHOUSEHOLD, and SOFTCREDIT_GIFTS.

Because relationships aren't necessarily reciprocal, I find it helpful to take my list of invalid relationships and do BOTH of the following:

• Look up the RELATIONSHIP_TYPE against the name_b_a field in civicrm_relationship_type.
• Look up the RECIP_RELATIONSHIP_TYPE against both name_a_b and name_b_a in civicrm_relationship_type.

Solicitor Relationships¶

Solicitor relationships are stored in a different table. I used this SQL to extract them:

SELECT
CONSTIT_ID
, SOLICITOR_ID
, TABLEENTRIES.LONGDESCRIPTION as solicitor_type
, AMOUNT
, NOTES
, cs."SEQUENCE" as weight
FROM CONSTIT_SOLICITORS cs
LEFT JOIN TABLEENTRIES ON cs.SOLICITOR_TYPE = TABLEENTRIES.TABLEENTRIESID
ORDER BY weight

Attributes¶

Attributes are the RE equivalent of custom fields. However, unlike custom fields, they can also have a "date" value and a "comments" value. While this can be replicated in Civi via multi-record custom field groups, ideally the data is evaluated attribute by attribute.

Valuable information about the setup of the attributes is available in RE from Config > Attributes.

• The analogous field to civicrm_custom_field is AttributeTypes.
• AttributeTypes.CODETABLESID gives a lookup for the RE "option group" that contains valid options for that attribute.
• All constituent attribute data is stored in the table ConstituentAttributes for constituents and RELATIONSHIPATTRIBUTES for non-constituents. Note that it's stored in a Key-Value Pair-style table - you'll need to do a bunch of SQL queries, or run a Kettle "Row Denormaliser" step to get this data in order.

Here's my preliminary SQL to export attributes from RE:

SELECT
at.DESCRIPTION as Category
, TABLEENTRIES.LONGDESCRIPTION as Description
, TEXT
, "BOOLEAN"
, COMMENTS
, ca.ATTRIBUTEDATE
, ca.PARENTID
FROM ConstituentAttributes ca
JOIN AttributeTypes at ON ca.ATTRIBUTETYPESID = at.ATTRIBUTETYPESID
LEFT JOIN TABLEENTRIES ON ca.TABLEENTRIESID = TABLEENTRIES.TABLEENTRIESID
-- This UNION adds non-constituent attributes
UNION 
SELECT
at.DESCRIPTION as Category
, TABLEENTRIES.LONGDESCRIPTION as Description
, TEXT
, "BOOLEAN"
, COMMENTS
, ra.ATTRIBUTEDATE
, cr.RELATION_ID AS PARENTID
FROM RELATIONSHIPATTRIBUTES ra
JOIN AttributeTypes at ON ra.ATTRIBUTETYPESID = at.ATTRIBUTETYPESID
LEFT JOIN TABLEENTRIES ON ra.TABLEENTRIESID = TABLEENTRIES.TABLEENTRIESID
JOIN CONSTIT_RELATIONSHIPS cr ON ra.PARENTID = cr.ID

To get a list of option values out of RE for the attributes, use this SQL:

SELECT
DESCRIPTION
, at.CODETABLESID
, LONGDESCRIPTION
FROM TABLEENTRIES te 
LEFT JOIN AttributeTypes at ON te.CODETABLESID = at.CODETABLESID
ORDER BY DESCRIPTION

Attributes can be multi-record custom fields by their nature, so you have to account for that. Here's some alpha-grade SQL for sussing out which fields have multi-record custom fields:

SELECT ATTRIBUTETYPESID, PARENTID, COUNT(LONGDESCRIPTION)
FROM ConstituentAttributes ca
JOIN TABLEENTRIES te ON ca.TABLEENTRIESID = te.TABLEENTRIESID
GROUP BY PARENTID, ATTRIBUTETYPESID
HAVING COUNT(LONGDESCRIPTION) > 1
ORDER BY ATTRIBUTETYPESID

note: In Civi 4.5+, you could conceivably use "EntityRef" functionality to facilitate chained selects of OptionValue lists. That would let you create a multi-record custom field group that would very closely map how Attributes work in RE - but you'd have all the disadvantages of multi-record custom fields.

Salutations/addressee info¶

RE stores contact salutations and addressee info in two places.

Primary salutations/addressess are stored on the RECORDS table. PRIMARY_ADDRESSEE_ID, PRIMARY_ADDRESSEE, and PRIMARY_ADDRESSEE_EDIT, PRIMARY_SALUTATION_ID, PRIMARY_SALUTATION, PRIMARY_SALUTATION_EDIT.

An unlimited number of non-primary salutations can be stored in the CONSTITUENT_SALUTATION table.

Salutation options values are stored in the SALUTATION table, in the format "CODE1, CODE2, CODE3, etc.". Each code refers to an id in the SALUTATION_FIELDS table, which contains tokens (e.g. "First Name", "Spouse Last Name") as well as common words like "And".

Note that PRIMARY_ADDRESSEE is more akin to addressee_display in Civi, in that it stores the calculated display ID. Also note that when PRIMARY_ADDRESSEE_EDIT is -1 (true), that's the equivalent of a custom addressee in Civi, and the value stored in PRIMARY_ADDRESSEE_ID must be ignored.

Salutation types can be found with:

SELECT te.LONGDESCRIPTION as label
FROM TABLEENTRIES te
JOIN CODETABLES ct ON te.CODETABLESID = ct.CODETABLESID
WHERE ct.NAME = 'Addr/Sal Types'
ORDER BY te.SEQUENCE

Joseph Lacey's Salutations extension gives CiviCRM a salutations interface comparable to Raiser's Edge.

Other constituent tables:¶

Skip these tables:

• Spouse
• Gifts
• First Gift, Last gift, Largest Gift
• Actions
• First Action, Last Action
• Summary Information

Contribution-related exports¶

Contributions/Gifts¶

Contributions (in RE parlance: Gifts) are complicated beasts!

Here are some relevant database tables and their equivalent in Civi:
GIFT civicrm_contribution
GiftSplit civicrm_line_item
CAMPAIGN Roughly maps to Campaign. Your mapping may vary and/or include custom fields.
APPEAL Also roughly maps to Campaign (or Source). Your mapping may vary and/or include custom fields.
FUND Roughly maps to Financial Type, but you might choose to import as a custom field instead.

See "Campaigns, Appeals, Packages" below for more.

Finally, note that RE has a concept of an "Adjustment". If a gift has already been posted to the accounting software, you can't simply change the amount. You create an adjustment, which has the updated data, and the two gift records are linked via the AdjustmentId. This is also how pledge installments are written off (see "Pledges" below).

Note that gift type is hardcoded into a function called "TranslateGiftType) - so you may want to include that function in your SQL, e.g.:

SELECT
gs.GiftId
, g.CONSTIT_ID
, gs.Amount
, g.DTE as gift_date
, FUND.DESCRIPTION as fund
, CAMPAIGN.DESCRIPTION as campaign
, APPEAL.DESCRIPTION as appeal
, g.PAYMENT_TYPE
, g.ACKNOWLEDGE_FLAG
, g.CHECK_NUMBER
, g.CHECK_DATE
, g.BATCH_NUMBER
, g.ANONYMOUS
, gst.LONGDESCRIPTION as giftsubtype
, g.TYPE
, DBO.TranslateGiftType(g.TYPE) as type2
FROM GiftSplit gs
LEFT JOIN FUND on gs.FundId = FUND.id
LEFT JOIN APPEAL on gs.AppealId = APPEAL.id
LEFT JOIN CAMPAIGN on gs.CampaignId = CAMPAIGN.id 
LEFT JOIN GIFT g on gs.GiftId = g.ID
LEFT JOIN TABLEENTRIES gst on g.GIFTSUBTYPE = gst.TABLEENTRIESID

(See here: http://www.re-decoded.com/2013/07/payment-type-or-payment-method-id-reference/#more-714)

Payment Type is also hard-coded, it seems:
1 Cash
2 Personal Check
3 Business Check
4 Credit Card
5 Standing Order
6 Direct Debit
7 Voucher
8 Other

Soft Credits¶

Stored in GIFTSOFTCREDIT. RE does NOT have the concept of a soft credit type - which is fine.

SELECT
, GiftId
, ConstitId
, Amount
, 'Soft Credit' as soft_credit_type
FROM GiftSoftCredit

Solicitor, Gift¶

(Important! Gift solicitors are different from Contact Solicitors)

I imported these as soft credits, but a different TYPE of soft credit. Here's the SQL I used to get the data out of RE:

SELECT
ParentId as gift_id
, SolicitorId as soft_creditee_external_identifier
, Amount
, 'Solicitor' as soft_credit_type
FROM GiftSolicitor

In Honor/Memorial Of (aka Tributes)¶

As of CiviCRM 4.5, In Honor/Memorial of is considered a form of soft credit. In RE, they're still separate, and are called Tributes. The structure is a little more complex - the table structure is Constituent <-> Tribute <-> Gift_Tribute <-> Gift. Civi is Contact <-> Soft Credit <-> Contribution.

Here is some preliminary SQL that pulls tribute data suitable for transformation and import to Civi as ContributionSoft entities. Note that CiviCRM doesn't have a concept of a "Description" but does have the concept of a PCP Note, so I'm importing the description there - in the future, I could see the argument for Civi exposing the PCP Note as a description.

SELECT

gt.GIFT_ID
, gt.TRIBUTE_TYPE
, t.DESCRIPTION
, t.RECORDS_ID as tributee_extenal_identifier
, te.LONGDESCRIPTION as tribute_type
FROM GIFT_TRIBUTE gt
JOIN TRIBUTE t ON gt.TRIBUTE_ID = t.ID
LEFT JOIN TABLEENTRIES te on gt.TRIBUTE_TYPE = te.TABLEENTRIESID

Pledges¶

Here are the relevant tables and their equivalents in Civi:

GIFT
Installment
InstallmentPayment

GIFT is equivalent to civicrm_contribution AND to civicrm_pledge. Pledges and contributions are stored in the same table - so a pledge paid in six installments will have SEVEN records in the GIFT field. One will be of Gift Type Pledge or MG Pledge ("Matching Gift Pledge"). MG Pledges will be on the original donor, with the payment on the matching donor. You can also tell pledges by the presence of the INSTALLMENT_FREQUENCY, NUMBER_OF_INSTALLMENTS, FrequencyDescription, REMIND_FLAG, NextTransactionDate and the Schedule* fields. Note that some of these might also be used for recurring contributions.

Installment and InstallmentPayment are, when combined, the equivalent of the civicrm_pledge_payment table. civicrm_pledge_payment has a field scheduled_amount and actual_amount. RE's model is somewhat superior in that it allows partial payments on a pledge installment.

Notes: When creating pledges in CiviCRM via API, the open pledge payments are simultaneously created. To import pledge payments from RE, you first want to delete the auto-generated pledge payments, then import your own pledge payments. Finally, when importing pledge payments, the status of the parent pledge isn't updated. So you'll probably want some SQL to indicate whether the pledges are pending, complete or canceled, depending on the status of the pledge payments. Finally, watch out for adjustments, which is how some or all pledge installments might be marked as "Written Off" in RE.

The INSTALLMENT_FREQUENCY list is hard-coded:

1 Annually
2 Every 6 Months
3 Every 3 Months
4 Every 2 Months
5 Every Month
6 Due Twice/Month
9 Irregular
10 Single Installment

Some SQL:

/* Find all GIFT records with one or more associated Installment records.  These are pledges OR recurring gifts. */
SELECT DISTINCT
g.CONSTIT_ID
, g.ID as GiftId
, g.Amount
, g.DTE as receive_date
, FUND.DESCRIPTION as fund
, FUND.FUND_ID
, CAMPAIGN.DESCRIPTION as campaign
, APPEAL.DESCRIPTION as appeal
, g.PAYMENT_TYPE
, g.ACKNOWLEDGEDATE
, DBO.TranslateGiftType(g.TYPE) as type
, g.REF as note
,DATE_1ST_PAY
,g.DATEADDED
,g.DATECHANGED
,INSTALLMENT_FREQUENCY
,NUMBER_OF_INSTALLMENTS
,POST_DATE
,POST_STATUS
,REMIND_FLAG
,Schedule_Month
,Schedule_DayOfMonth
,Schedule_MonthlyDayOfWeek
,Schedule_Spacing
,Schedule_MonthlyType
,Schedule_MonthlyOrdinal
,Schedule_WeeklyDayOfWeek
,Schedule_DayOfMonth2
,Schedule_SMDayType1
,Schedule_SMDayType2
,NextTransactionDate
,Schedule_EndDate
,FrequencyDescription
, r.CONSTITUENT_ID
FROM Gift g
LEFT JOIN GiftSplit gs on g.ID = gs.GiftId
LEFT JOIN FUND on gs.FundId = FUND.id
LEFT JOIN APPEAL on gs.AppealId = APPEAL.id
LEFT JOIN CAMPAIGN on gs.CampaignId = CAMPAIGN.id 
LEFT JOIN RECORDS r ON g.CONSTIT_ID = r.ID
JOIN Installment i ON g.ID = i.PledgeId

Find pledge payments:

/* Find all pledge installments, and their related payments if they exist. */
SELECT
i.InstallmentId
, i.PledgeId
, i.AdjustmentId
, i.Amount as scheduled_amount
, i.Dte
, ip.Amount as actual_amount
, ip.PaymentId
, g.CONSTIT_ID
, g.RECEIPT_AMOUNT
, g.DTE as receive_date
, g.TYPE
, DBO.TranslateGiftType(g.TYPE) as type
FROM Installment i
LEFT JOIN InstallmentPayment ip ON i.InstallmentId = ip.InstallmentId
LEFT JOIN GIFT g ON ip.PaymentId = g.ID
/* Adjustments are stored in here too - when an adjustment happens, the pledge ID of the original value is blanked */
WHERE i.PledgeId IS NOT NULL 
ORDER BY i.AdjustmentId
/* Write-off Types: Covenant WriteOff, MG Write Off, Write Off */

Matching gifts¶

Gifts are connected to their matching gift via the GiftMatchingGift table, and appear in both the matched person's and matcher's "gifts" tab.

Pledges as contributions¶

If you're working with an organization that wants to use CiviAccounts, you'll want to track pledges as pending contributions.

Campaigns, Appeals, Packages¶

RE's model for campaigns is hierarchical and more sophisticated than CiviCRM's. A campaign (e.g. "Capital fundraising FY2017") can consist of several appeals (e.g. "Capital fundraising FY 2017 Spring Mailing"). Appeals will generally correspond to a particular action, especially a postal mailing. Campaigns and Appeals can be linked in a many-to-many relationship, but this is rare. The 1-to-many is by far the most common approach. Finally, an appeal can consist of "packages", which is a segment of your appeal. For instance, a single mailing ("appeal") could go out to major donors, regular donors and non-donors. You might also A/B test envelopes vs. postcards in the same mailing. This would result in a total of six "packages" for a single appeal. RE can track goals for each separately, and easily report on "number solicited" by package vs. number of gifts received.

Actions¶

Actions fill the same purpose as Activities in CiviCRM, but are architected quite differently - in some ways better, some ways worse. I don't have as much concrete info here, but here's a decent start at extracting Actions data via SQL:

SELECT
  a.ADDED_BY
, a.AUTO_REMIND
, a.RECORDS_ID as external_identifier
, cr.RELATION_ID as action_contact_id
, a.DTE as activity_date_time
, LETTER.LONGDESCRIPTION as letter
, a.PRIORITY as priority_id
, a.REMIND_VALUE
, a.CATEGORY
, a.Completed
, a.COMPLETED_DATE
, a.FUND_ID
, a.FOLLOWUPTO_ID
, a.TRACKACTION_ID
, a.PhoneNumber as phone_number
, a.Remind_Frequency
, a.WORDDOCNAME
, a.APPEAL_ID
, a.APPEAL_LETTER_CODE
, a.OUTLOOK_EMAIL_SUBJECT
, STATUS.LONGDESCRIPTION as status
, TYPE.LONGDESCRIPTION as type
, LOCATION.LONGDESCRIPTION as location
, ActionNotepad.ActualNotes
, CAMPAIGN.DESCRIPTION as campaign
FROM ACTIONS a
LEFT JOIN TABLEENTRIES as STATUS ON a.STATUS = STATUS.TABLEENTRIESID
LEFT JOIN TABLEENTRIES as TYPE ON a.[TYPE] = [TYPE].TABLEENTRIESID
LEFT JOIN TABLEENTRIES as LOCATION ON a.[Location] = LOCATION.TABLEENTRIESID 
LEFT JOIN TABLEENTRIES as LETTER on a.[LETTER_CODE] = LETTER.TABLEENTRIESID
LEFT JOIN ActionNotepad ON a.ID = ActionNotepad.ParentId
LEFT JOIN CAMPAIGN on a.CAMPAIGN_ID = CAMPAIGN.id
LEFT JOIN CONSTIT_RELATIONSHIPS cr on a.CONTACT_ID = cr.ID

"Category" and "Action type" both roughly map to "Activity Type". Same for "status" and "COMPLETED" and "COMPLETED_DATE" mapping to "activity_status". RE lets you designate a related Campaign, Fund and Proposal; out of the box, Civi only supports Campaign. The auto-reminder is more flexible than you can get with scheduled reminders in Civi without getting very complicated. "Solicitors" can't be mapped to a contact reference lookup, because more than one can be stored.

Note: The SQL above presumes only one note per action. If you have multiple notes per action, the action will be represented with multiple records, one per associated note. I'll try to provide SQL for extracting the notes separately at a later date.

Action Notes¶

Action Notes are stored in their own table. This maps to "Details" on a Civi activity, but you can log multiple notes per action in RE. Here's the SQL I used to extract them in preparation:

SELECT
  NotesID
, Title
, Description
, Author
, ActualNotes
, ParentId
, NotepadDate
, TABLEENTRIES.LONGDESCRIPTION as Type
  FROM ActionNotepad
  LEFT JOIN TABLEENTRIES ON ActionNotepad.NoteTypeId = TABLEENTRIES.TABLEENTRIESID
ORDER BY ParentId, ActionNotepad."SEQUENCE"

Events¶

Events are stored fairly similarly to CiviCRM, except:

• locations are stored on the event record itself (which I'm not dealing with).
• There's fields for storing data about classes. I haven't delved into this - I suspect that this may tie into recurring events.
• "Event Category" and "Event Type" might both map to Civi's "Event Type". This isn't the case for me.
• Events need not have begin/end dates. While this is technically true for Civi, you're buying yourself a whole lot of trouble. I'm pulling in "DATE_ADDED" to substitute in for START_DATE where none exists.

Here's some SQL to pull in the most relevant data:

SELECT
  se.CAPACITY
, se.END_DATE
, se.ID
, se.NAME
, se.START_DATE
, se.DATE_ADDED
, te.LONGDESCRIPTION as activity_type
, se.INACTIVE
, se.DISPLAYONCALENDAR
, CAMPAIGN.DESCRIPTION as campaign
, se.DESCRIPTION
FROM SPECIAL_EVENT se
LEFT JOIN CAMPAIGN on se.CAMPAIGN_ID = CAMPAIGN.id 
LEFT JOIN TABLEENTRIES te ON se.TYPEID = te.TABLEENTRIESID

Notes¶

RE notes (stored in the "ConstituentNotepad" table) can store quite a bit of data that Civi notes can not. They can store formatting (but with proprietary format tags, not HTML), inline photos, etc, and contain fields for date of note (separate from "Date Added" and "Date Changed"), the type of note, etc. Fortunately, they store plain-text versions of formatted notes in their own field. "Notes" is formatted; "ActualNotes" is plain text (except, well, where it isn't).

I've resolved this by removing notes over a certain length (above 15K and I assume you're a photo) and concatenating the fields I want to keep (e.g. Note Type and Description) with the ActualNotes field.

It may be possible to export the photos in the Notes using the techniques described below under "Media".

Here's the SQL I'm currently using to extract notes before doing transforms in Kettle:

SELECT
Title as subject
, Description
, Author
, ActualNotes
, ParentId
, cn.DateChanged
, LONGDESCRIPTION as NoteType
FROM ConstituentNotepad cn
LEFT JOIN TABLEENTRIES ON NoteTypeId = TABLEENTRIESID

Localization formats¶

Raiser's Edge stores most of this info in the COUNTRY_CODES table, though the actual country name is stored in TABLEENTRIES. This SQL will get you both:

SELECT LONGDESCRIPTION, COUNTRY_CODES.*
  FROM COUNTRY_CODES
  JOIN TABLEENTRIES ON COUNTRY_CODES.COUNTRYCODESID = TABLEENTRIES.TABLEENTRIESID

Address formats (stored in Civi in civicrm_address_format) are stored in COUNTRY_LABELS. You can add ass many tokens (FIELD_NUM) to a line (LINE_NUM) and they'll appear in order of SEQUENCE). I couldn't find a lookup table in the database that translated the tokens, but here it is:

1 = Address lines
2 = City
3 = State/Province
4 = Postcode
5 = Country
6 = Country abbreviation
9 = County
101 = :
102 = ,
103 = -
104 = .
105 = /
106 = <space>

Financial accounts¶

I believe that their presence in RE is set by a flag GENERAL_INFORMATION.GL_PRESENT, with GENERAL_INFORMATION.GL_ENG_TYPE playing a role (I believe dealing with Financial Edge integration).
The relevant fields are:

• FUND (relates to GIFT.FundId).
• FUNDDISTRIBUTION (connector table, also connects to Gift Type and Subtype, which are also set on the Gift. You can have different Financial Accounts based on a combination of FundId, TypeId, and SubTypeId).
• GLDISTRIBUTIONDETAIL (Contains GL Account codes, etc. Looks like it contains some Quickbooks integration fields. Most similar to civicrm_financial_account.)
• GLDISTRIBUTION (This also links to FUNDDISTRIBUTION but AFAICT has no data that needs migrating to CiviCRM).

Misc¶

Some RE tables and what they're for (and their equivalents in Civi if applicable):
GENERAL_INFORMATION: Similar to both civicrm_domain and civicrm_settings. Version number, Organization name, license number, general settings, etc.

Media¶

Exporting from RE¶

The files stored on the "Media" tab are held in the [dbo].[MEDIA] table in MS SQL. Assuming embedded and not linked data, the files are stored in the MS Access OLE format. It's relatively difficult to extract data from the OLE wrapper, though searching for extract access ole on any search engine will give you lots of options in a variety of languages. Blackbaud even has code to do it "here":https://kb.blackbaud.com/articles/Article/58559, if you feel like using VBA.

I opted to use a commercial software package from Yohz Software called "SQL Image Viewer":http://www.yohz.com/siv_details.htm. If you use this tool, enter the command:

SELECT OBJECT FROM [dbo].[MEDIA]

Then press "Execute Query", then press "Export" when it's done. This exports about 200 items/minute on a computer with a very slow hard drive.

If you want to decode the OLE containers on your own, there's the "bcp":https://msdn.microsoft.com/en-us/library/ms162802.aspx CLI tool that installs with MS SQL, and you can run a SQL query from SQL Server Management Studio that extracts your OLE containers; see "here":http://dba.stackexchange.com/questions/80817/how-to-export-an-image-column-to-files-in-sql-server. I experimented with this strategy, and this CLI command extracted an Access OLE container:

bcp "SELECT OBJECT FROM [July_Snapshot].[dbo].[media] WHERE ID = 1210 " queryout "C:\Users\Jon\Desktop\temp\test.ole" -T -N -S HOSTNAME\RE_EXPRESS

Importing into CiviCRM¶

The approach I took was to copy all the files into the "custom files" directory as specified in Administer > System Settings > Directories. Then I used the Attachment entity of the API to import the file to an activity. For the Media tab, I created activities especially to import the media onto.

Here's an example of the correct usage of the API to add in image "drill.jpg" to an activity with an ID of 628:

$result = civicrm_api3('Attachment', 'create', array(
  'sequential' => 1,
  'name' => "drill.jpg",
  'mime_type' => "image/jpeg",
  'entity_id' => 628,
  'entity_table' => "civicrm_activity",
  'options' => array('move-file' => "/home/jon/local/civicrm-buildkit/build/d46/sites/default/files/civicrm/custom/drill.jpg"),
));

Note that Civi will rename your files with random characters at the end, so this action is not idempotent. Keep a reserve copy of your exported RE media to roll back to!

If you use the API CSV import tool, your CSVs should look like this:

"name",entity_id,"entity_table","mime_type","options.move-file"
"100.png",87511,"civicrm_activity","image/png","/home/jon/local/lccr/wp-content/plugins/files/civicrm/custom/100.png"
"1000.pdf",88411,"civicrm_activity","application/pdf","/home/jon/local/lccr/wp-content/plugins/files/civicrm/custom/1000.pdf"

And More¶

Tables that Civi doesn't have a direct counterpart for¶

• Aliases (stores Maiden Name and d/b/a - unsure how to import into Civi just yet)
• Solicitor Goals - Can be found on an RE contact record on "Bio 1" tab by clicking "Details" next to "Is a Solicitor" checkbox. Don't know how to use them.

Open each CSV file in Excel or similar. Sort each field by ascending AND descending to see if any data is stored in that field. If every record has no data or the same data, delete it - it's not being tracked in the current system. If you see only one or two records with a particular field, they're also probably fine to go, but check with the client first.

Next, strip out all of the constituent information except for primary/foreign keys. I like to keep in First/Middle/Last name just for human readability though. So leave in those three fields, plus any field with the word "ID" in it. This is your base constituent info, and will be in every other export you do.

Now comes the fun part! Export each table, one at a time, by adding those fields to an export that already includes the base constituent info.

For one-to-many relationships, the system will ask you how many instances of the information to export. I default to 12, then look over the data to see how many are actually used, then re-export with a higher or lower number.

I also remove records that don't contain the relevant data. For instance, when exporting Solicit Codes, I sort by the first Solicit Code. Then I scroll down past the folks that have Solicit Codes to those who have none, and delete the rows for folks who have none.

Note that for simplicity's sake, RE contains many views of the tables that, if you export them all, you'll have redundant data. There's no need to export "First Gift", "Last Gift", or "Largest Gift" - simply export all gifts. Likewise for "Preferred Address".

When exporting one-to-many tables that themselves contain one-to-many tables (e.g. Addresses contains Phones), do NOT select 12 of each! That means you're exporting 144 phone numbers per record. First determine the maximum number of addresses being tracked, re-export with that number, THEN export with phone numbers. Also, it's reasonable to export with 5 phone numbers per address.

NOTE: Letters sent is incomplete, there's more than 12 letters to some folks!

GIFTS is related to constituent on the last column (Constituent System Record ID)

Code Tables/Option Groups/Option Values¶

If you're extracting data from the SQL back-end, you'll see that the RE equivalent to Civi option groups is "code tables". There's two functions that handle lookups: dbo.GetTableEntryDescription and dbo.GetTableEntryDescSlim. To determine where the data is being accessed by the function, see "Deciphering MS SQL", below. Use the "lTableNumber" passed to those functions and you'll find your data in dbo.CODETABLES (comparable to civicrm_option_group), dbo.CODETABLEMAP and dbo.TABLEENTRIES (comparable to civicrm_option_value).

Deciphering MS SQL¶

SQL Server Profiler is a tool that lets you spy on SQL statements passed to MS SQL, which is good for determining where certain data lives. However, RE depends on functions and stored procedures, so sometimes the SQL won't tell you exactly where to look.

Looking Up Functions¶

These are embedded in SQL and have a nomenclature like: dbo.GetTableEntryDescSlim. Find them in SQL Server Management Studio: database > Programmability > Functions > Scalar-valued Functions.

Looking Up Stored Procedures¶

If, in the profiler, taking a certain action shows a command like this:
These have a syntax like:

exec sp_execute 48,43,'Acknowledgee'

You're dealing with a stored procedure. You need to find the corresponding exec sp_prepexec command (in this case, the one with a 48). In this case, it looks like:

declare `p1 int
set `p1=48
exec sp_prepexec `p1 output,N'`P1 int,`P2 varchar(255)',N'SELECT  Top 1 TABLEENTRIESID  FROM DBO.TABLEENTRIES WHERE CODETABLESID = `P1 AND LONGDESCRIPTION = `P2  ',43,'Acknowledgee'
select `p1

Note that there's a tool called "SQL Hunting Dog", a free plug-in for SQL Server Management Studio, which makes locating stored procedures, etc. easier.

Addressee/Postal Greeting/E-mail greeting¶

RE has a much wider variety of greeting formats out-of-the-box. The "spouse ID" is stored on the record to enable quick lookups of addressee greetings that include the spouse.

It's likely that you'll want to map existing RE greetings to Civi greetings. Here is some SQL that will show you how the current greetings in RE are constructed:

/****** Script for SelectTopNRows command from SSMS  ******/
SELECT s.ID
, sf1.FIELDNAME as FIELD1
, sf2.FIELDNAME as FIELD2
, sf3.FIELDNAME as FIELD3
, sf4.FIELDNAME as FIELD4
, sf5.FIELDNAME as FIELD5
, sf6.FIELDNAME as FIELD6
, sf7.FIELDNAME as FIELD7
, sf8.FIELDNAME as FIELD8
, sf9.FIELDNAME as FIELD9
, sf10.FIELDNAME as FIELD10     
, sf11.FIELDNAME as FIELD11
, sf12.FIELDNAME as FIELD12
, sf13.FIELDNAME as FIELD13
, sf14.FIELDNAME as FIELD14
, sf15.FIELDNAME as FIELD15
, sf16.FIELDNAME as FIELD16
, sf17.FIELDNAME as FIELD17
, sf18.FIELDNAME as FIELD18
, sf19.FIELDNAME as FIELD19
, sf20.FIELDNAME as FIELD20
  FROM SALUTATIONS s
  LEFT JOIN SALUTATION_FIELDS sf1 on CODE1 = sf1.ID
  LEFT JOIN SALUTATION_FIELDS sf2 on CODE2 = sf2.ID
  LEFT JOIN SALUTATION_FIELDS sf3 on CODE3 = sf3.ID
  LEFT JOIN SALUTATION_FIELDS sf4 on CODE4 = sf4.ID
  LEFT JOIN SALUTATION_FIELDS sf5 on CODE5 = sf5.ID
  LEFT JOIN SALUTATION_FIELDS sf6 on CODE6 = sf6.ID
  LEFT JOIN SALUTATION_FIELDS sf7 on CODE7 = sf7.ID
  LEFT JOIN SALUTATION_FIELDS sf8 on CODE8 = sf8.ID
  LEFT JOIN SALUTATION_FIELDS sf9 on CODE9 = sf9.ID
  LEFT JOIN SALUTATION_FIELDS sf10 on CODE10 = sf10.ID
  LEFT JOIN SALUTATION_FIELDS sf11 on CODE11 = sf11.ID
  LEFT JOIN SALUTATION_FIELDS sf12 on CODE12 = sf12.ID
  LEFT JOIN SALUTATION_FIELDS sf13 on CODE13 = sf13.ID
  LEFT JOIN SALUTATION_FIELDS sf14 on CODE14 = sf14.ID
  LEFT JOIN SALUTATION_FIELDS sf15 on CODE15 = sf15.ID
  LEFT JOIN SALUTATION_FIELDS sf16 on CODE16 = sf16.ID
  LEFT JOIN SALUTATION_FIELDS sf17 on CODE17 = sf17.ID
  LEFT JOIN SALUTATION_FIELDS sf18 on CODE18 = sf18.ID
  LEFT JOIN SALUTATION_FIELDS sf19 on CODE19 = sf19.ID
  LEFT JOIN SALUTATION_FIELDS sf20 on CODE20 = sf20.ID

Things I see that RE does better than Civi:¶

• Better greetings/salutations UI out of the box. In Civi, you must in-line edit the greetings, then press "Edit" next to the greetings, and even then you only see the tokens you'll use. RE lets you edit with no clicks, and parses the tokens for you.
• The equivalent of option values are stored with their id, not their value. This isn't a big deal, but it DOES make data transformation easier in RE, and I suspect it makes their equivalent of pseudoconstant code easier to read.
• There's a lot more data stored in many-to-many tables. For instance, job titles are stored in the relationship tab, reflecting the fact that someone can have more than one job.