Project

General

Profile

Exporting Raisers Edge for CiviCRM » History » Version 4

Jon Goldberg, 06/08/2018 08:00 PM

1 1 Jon Goldberg
{{lastupdated_at}} by {{lastupdated_by}}
2
3
{{>toc}}
4
5
# Exporting Raiser's Edge for CiviCRM
6
7
8
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.
9
10
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
11
12
##  Do you use Pentaho Kettle?
13
14
If so, you can use my Raiser's Edge to CiviCRM transforms, available here: https://github.com/PalanteJon/civicrm_kettle_transforms
15
16
## Export tool - general guide.
17
18
The Raiser's Edge Export tool is on the left toolbar when you first enter Raiser's Edge.
19
20
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).
21
22
For most export, select Constituent as the Export type.  This is the "base table" - all records will be joined relative to it.
23
24
## Constituent Based Exports
25
26
### Contact Information
27
28
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".
29
30
### SQL
31
32
If extracting directly from SQL, `SELECT * FROM RECORDS`.
33
34
Note that you can extract only constituents by adding `WHERE IS_CONSTITUENT = -1`.  For a Civi migration, I recommend importing all contacts.
35
36
### Export tool (NOTE: This ONLY gets constituents).
37
38
Tab 1. General:
39
- Include all records.
40
- Head of Household processing: Export both constituents separately.
41
- Check all of the "Include these Constitutents": Inactive, deceased, no valid address
42
43
Tab 2: Output.
44
First, expand the "Constituent Information" in the left pane, and add every field to the export.  Do the export (as a CSV).
45
46
### Constituent Codes
47
48
In RE: Found at the bottom of the "Bio 2" tab.
49
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.
50
51
Export as _one to many_, below.
52
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.
53
54
No need to export these fields:
55
System Record ID
56
Import ID
57
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.
58
59
### Solicit Codes
60
61
These can map to groups - but also may map to privacy preferences or custom fields (e.g. Email Only, Do Not Solicit)
62
63
SQL to extract solicit codes:
64
```
65
SELECT RECORDSID AS external_identifier, LONGDESCRIPTION as solicit_code FROM CONSTITUENT_SOLICITCODES JOIN TABLEENTRIES ON SOLICIT_CODE = TABLEENTRIES.TABLEENTRIESID WHERE TABLEENTRIES.ACTIVE = -1
66
```
67
68
In my copy of RE, the CODETABLESID is 5044, so to get a list of all solicit codes, use:
69
```
70
SELECT LONGDESCRIPTION, ACTIVE FROM TABLEENTRIES WHERE CODETABLESID = 5044 ORDER BY SEQUENCE;
71
```
72
73
### Addresses
74
75
SQL tables: ADDRESS, CONSTIT_ADDRESS
76
77
Addresses are a many-to-many relationship in RE.
78 2 Jon Goldberg
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.
79
80
Source: http://www.kb.blackbaud.co.uk/articles/Article/41141
81
The address Indicator field is located on the CONSTIT_ADDRESS table and can have the following values: 
82
~~~
83
0 = Alternate
84
1 = Contact
85
2 = Preferred (Primary Address entered on the Bio 1 tab of the constituent record)
86
3 = Business
87
4 = Spouse Business
88
5 = Spouse Preferred
89
6 = Spouse Alternate
90
7 = Relationship
91
~~~
92
93 3 Jon Goldberg
`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.
94
95 4 Jon Goldberg
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.
96
97
Here's the SQL I use for getting address data in Kettle:
98
99
~~~sql
100
SELECT
101
  ADDRESS_BLOCK
102
, CITY
103
, POST_CODE
104
, "STATE"
105
, CTY.LONGDESCRIPTION as country
106
, ADDRESS_ID
107
, ca.CONSTIT_ID
108
, PREFERRED
109
, LOC_TYPE.LONGDESCRIPTION
110
, ca.DATE_FROM
111
, ca.DATE_TO
112
, SENDMAIL
113
, ca.ID AS CAID
114
, cr.IS_EMPLOYEE
115
, cr.IS_PRIMARY AS IS_RELATIONSHIP_PRIMARY
116
, cr.RELATION_ID
117
, ca.INDICATOR
118
FROM ADDRESS a
119
LEFT JOIN TABLEENTRIES AS CTY ON CTY.TABLEENTRIESID = COUNTRY
120
JOIN CONSTIT_ADDRESS ca ON a.ID = ca.ADDRESS_ID
121
LEFT JOIN TABLEENTRIES AS LOC_TYPE ON ca."TYPE" = LOC_TYPE.TABLEENTRIESID
122
LEFT JOIN RECORDS r ON ca.CONSTIT_ID = r.ID
123
-- This next join is needed to handle address sharing with the business
124
LEFT JOIN CONSTIT_RELATIONSHIPS cr ON ca.ID = cr.CONSTIT_ADDRESS_ID AND ca.CONSTIT_ID = cr.CONSTIT_ID
125
WHERE INDICATOR <> 7 AND ADDRESS_BLOCK IS NOT NULL
126
~~~
127
128 2 Jon Goldberg
### Primary Business Information 
129
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.
130 1 Jon Goldberg
131
### Phones/E-mail/websites
132
133
RE is a child of the 90's, so a) phones are tied to addresses, not contacts, and b) e-mails and websites are a type of phone.
134
135
Notes:
136
* You can NOT have duplicate phone types in RE, so no need to try and catch multiple "Home" numbers!
137
* Oh - except that one contact can have two home phone numbers on two different addresses.
138
* Don't forget to filter out duplicate numbers/e-mails/etc. when someone puts the same phone number on two different addresses.
139
140
This SQL gets me a useful list of phones and e-mail for further processing in Kettle:
141
```
142
SELECT DISTINCT
143
  CONSTITADDRESSID
144
, CONSTIT_ID
145
, PHONETYPEID
146
, CONSTIT_ADDRESS_PHONES."SEQUENCE"
147
, NUM
148
, DO_NOT_CALL
149
, TEXT_MSG
150
FROM CONSTIT_ADDRESS_PHONES
151
LEFT JOIN PHONES ON CONSTIT_ADDRESS_PHONES.PHONESID = PHONES.PHONESID
152
LEFT JOIN CONSTIT_ADDRESS ON CONSTITADDRESSID = CONSTIT_ADDRESS.ID
153
```
154
155
### Relationships
156
157
Relevant SQL table: CONSTIT_RELATIONSHIPS
158
159
Relationships are different in Civi and RE in the following significant ways:
160
* Relationships don't have to have a relationship type.
161
* 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".
162
* 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.
163
* There need not be a relationship type at all.  This doesn't make sense, except that:
164
* There are hardcoded fields for IS_SPOUSE, HON_MEM_ACKNOWLEDGE, IS_HEADOFHOUSEHOLD, and SOFTCREDIT_GIFTS.
165
166
Because relationships aren't necessarily reciprocal, I find it helpful to take my list of invalid relationships and do BOTH of the following:
167
* Look up the RELATIONSHIP_TYPE against the `name_b_a` field in `civicrm_relationship_type`.
168
* Look up the RECIP_RELATIONSHIP_TYPE against both `name_a_b` and `name_b_a` in `civicrm_relationship_type`.
169
170
### Solicitor Relationships
171
172
Solicitor relationships are stored in a different table.  I used this SQL to extract them:
173
```
174
SELECT
175
CONSTIT_ID
176
, SOLICITOR_ID
177
, TABLEENTRIES.LONGDESCRIPTION as solicitor_type
178
, AMOUNT
179
, NOTES
180
, cs."SEQUENCE" as weight
181
FROM CONSTIT_SOLICITORS cs
182
LEFT JOIN TABLEENTRIES ON cs.SOLICITOR_TYPE = TABLEENTRIES.TABLEENTRIESID
183
ORDER BY weight
184
```
185
186
### Attributes
187
188
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.
189
190
Valuable information about the setup of the attributes is available in RE from *Config > Attributes*.
191
192
* The analogous field to `civicrm_custom_field` is `AttributeTypes`.
193
* `AttributeTypes.CODETABLESID` gives a lookup for the RE "option group" that contains valid options for that attribute.
194
* All constituent attribute data is stored in the table `ConstituentAttributes`.  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.
195
 
196
Here's my preliminary SQL to export attributes from RE:
197
```
198
SELECT
199
ca.PARENTID as external_identifier
200
, ca.ATTRIBUTETYPESID
201
, at.DESCRIPTION as Category
202
, TABLEENTRIES.LONGDESCRIPTION as Description
203
, TEXT
204
, NUM
205
, DATETIME
206
, CURRENCY
207
, "BOOLEAN"
208
, COMMENTS
209
, ca.ATTRIBUTEDATE
210
FROM ConstituentAttributes ca
211
JOIN AttributeTypes at ON ca.ATTRIBUTETYPESID = at.ATTRIBUTETYPESID
212
LEFT JOIN TABLEENTRIES ON ca.TABLEENTRIESID = TABLEENTRIES.TABLEENTRIESID
213
```
214
215
*note:*  In the SQL above, "PARENTID" and not "ConstitID" is the correct foreign key to link this to the contact.
216
217
To get a list of option values out of RE for the attributes, use this SQL:
218
```
219
SELECT
220
DESCRIPTION
221
, at.CODETABLESID
222
, LONGDESCRIPTION
223
FROM TABLEENTRIES te 
224
LEFT JOIN AttributeTypes at ON te.CODETABLESID = at.CODETABLESID
225
ORDER BY DESCRIPTION
226
```
227
228
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:
229
```
230
SELECT ATTRIBUTETYPESID, PARENTID, COUNT(LONGDESCRIPTION)
231
FROM ConstituentAttributes ca
232
JOIN TABLEENTRIES te ON ca.TABLEENTRIESID = te.TABLEENTRIESID
233
GROUP BY PARENTID, ATTRIBUTETYPESID
234
HAVING COUNT(LONGDESCRIPTION) > 1
235
ORDER BY ATTRIBUTETYPESID
236
```
237
238
*note:*  In Civi 4.5+, you could conceivable 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.
239
240
### Salutations/addressee info
241
242
RE stores contact salutations and addressee info in two places.
243
244
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`.
245
246
An unlimited number of non-primary salutations can be stored in the `CONSTITUENT_SALUTATION` table.
247
248
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".
249
250
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.
251
252
### Other constituent tables:
253
254
Skip these tables:
255
* Spouse
256
* Gifts
257
* First Gift, Last gift, Largest Gift
258
* Actions
259
* First Action, Last Action
260
* Summary Information
261
262
## Contribution-related exports
263
264
### Contributions/Gifts
265
266
Contributions (in RE parlance: Gifts) are complicated beasts!
267
268
Here are some relevant database tables and their equivalent in Civi:
269
GIFT	civicrm_contribution
270
GiftSplit	civicrm_line_item
271
CAMPAIGN	Roughly maps to Campaign.  Your mapping may vary and/or include custom fields.
272
APPEAL	Also roughly maps to Campaign (or Source).  Your mapping may vary and/or include custom fields.
273
FUND	Roughly maps to Financial Type, but you might choose to import as a custom field instead.
274
275
See "Campaigns, Appeals, Packages" below for more.
276
277
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).
278
279
Note that gift type is hardcoded into a function called "TranslateGiftType) - so you may want to include that function in your SQL, e.g.:
280
```
281
SELECT
282
gs.GiftId
283
, g.CONSTIT_ID
284
, gs.Amount
285
, g.DTE as gift_date
286
, FUND.DESCRIPTION as fund
287
, CAMPAIGN.DESCRIPTION as campaign
288
, APPEAL.DESCRIPTION as appeal
289
, g.PAYMENT_TYPE
290
, g.ACKNOWLEDGE_FLAG
291
, g.CHECK_NUMBER
292
, g.CHECK_DATE
293
, g.BATCH_NUMBER
294
, g.ANONYMOUS
295
, gst.LONGDESCRIPTION as giftsubtype
296
, g.TYPE
297
, DBO.TranslateGiftType(g.TYPE) as type2
298
FROM GiftSplit gs
299
LEFT JOIN FUND on gs.FundId = FUND.id
300
LEFT JOIN APPEAL on gs.AppealId = APPEAL.id
301
LEFT JOIN CAMPAIGN on gs.CampaignId = CAMPAIGN.id 
302
LEFT JOIN GIFT g on gs.GiftId = g.ID
303
LEFT JOIN TABLEENTRIES gst on g.GIFTSUBTYPE = gst.TABLEENTRIESID
304
```
305
306
(See here: http://www.re-decoded.com/2013/07/payment-type-or-payment-method-id-reference/#more-714)
307
308
Payment Type is also hard-coded, it seems:
309
1	Cash
310
2	Personal Check
311
3	Business Check
312
4	Credit Card
313
5 	Standing Order
314
6	Direct Debit
315
7	Voucher
316
8	Other
317
318
### Soft Credits
319
320
Stored in GIFTSOFTCREDIT.  RE does NOT have the concept of a soft credit type - which is fine.
321
```
322
SELECT
323
, GiftId
324
, ConstitId
325
, Amount
326
, 'Soft Credit' as soft_credit_type
327
FROM GiftSoftCredit
328
```
329
330
### Solicitor, Gift
331
332
(Important!  Gift solicitors are different from Contact Solicitors)
333
334
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:
335
```
336
SELECT
337
ParentId as gift_id
338
, SolicitorId as soft_creditee_external_identifier
339
, Amount
340
, 'Solicitor' as soft_credit_type
341
FROM GiftSolicitor
342
```
343
344
### In Honor/Memorial Of (aka Tributes)
345
346
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.  
347
348
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.
349
350
```
351
```
352
SELECT
353
```
354
gt.GIFT_ID
355
, gt.TRIBUTE_TYPE
356
, t.DESCRIPTION
357
, t.RECORDS_ID as tributee_extenal_identifier
358
, te.LONGDESCRIPTION as tribute_type
359
FROM GIFT_TRIBUTE gt
360
JOIN TRIBUTE t ON gt.TRIBUTE_ID = t.ID
361
LEFT JOIN TABLEENTRIES te on gt.TRIBUTE_TYPE = te.TABLEENTRIESID
362
```
363
364
### Pledges
365
366
Here are the relevant tables and their equivalents in Civi:
367
GIFT
368
Installment
369
InstallmentPayment
370
371
`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.  Many organizations will specify a pledge in the Gift Type field - you can also tell 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.  
372
373
`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.
374
375
*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.
376
377
The INSTALLMENT_FREQUENCY list is hard-coded:
378
|1| Annually|
379
|2| Every 6 Months|
380
|3| Every 3 Months|
381
|4| Every 2 Months|
382
|5| Every Month|
383
|6| Due Twice/Month|
384
|9| Irregular|
385
|10| Single Installment|
386
387
Some SQL:
388
```
389
/* Find all GIFT records with one or more associated Installment records.  These are pledges OR recurring gifts. */
390
SELECT DISTINCT
391
g.CONSTIT_ID
392
, g.ID as GiftId
393
, g.Amount
394
, g.DTE as receive_date
395
, FUND.DESCRIPTION as fund
396
, FUND.FUND_ID
397
, CAMPAIGN.DESCRIPTION as campaign
398
, APPEAL.DESCRIPTION as appeal
399
, g.PAYMENT_TYPE
400
, g.ACKNOWLEDGEDATE
401
, DBO.TranslateGiftType(g.TYPE) as type
402
, g.REF as note
403
,DATE_1ST_PAY
404
,g.DATEADDED
405
,g.DATECHANGED
406
,INSTALLMENT_FREQUENCY
407
,NUMBER_OF_INSTALLMENTS
408
,POST_DATE
409
,POST_STATUS
410
,REMIND_FLAG
411
,Schedule_Month
412
,Schedule_DayOfMonth
413
,Schedule_MonthlyDayOfWeek
414
,Schedule_Spacing
415
,Schedule_MonthlyType
416
,Schedule_MonthlyOrdinal
417
,Schedule_WeeklyDayOfWeek
418
,Schedule_DayOfMonth2
419
,Schedule_SMDayType1
420
,Schedule_SMDayType2
421
,NextTransactionDate
422
,Schedule_EndDate
423
,FrequencyDescription
424
, r.CONSTITUENT_ID
425
FROM Gift g
426
LEFT JOIN GiftSplit gs on g.ID = gs.GiftId
427
LEFT JOIN FUND on gs.FundId = FUND.id
428
LEFT JOIN APPEAL on gs.AppealId = APPEAL.id
429
LEFT JOIN CAMPAIGN on gs.CampaignId = CAMPAIGN.id 
430
LEFT JOIN RECORDS r ON g.CONSTIT_ID = r.ID
431
JOIN Installment i ON g.ID = i.PledgeId
432
```
433
434
Find pledge payments:
435
```
436
/* Find all pledge installments, and their related payments if they exist. */
437
SELECT
438
i.InstallmentId
439
, i.PledgeId
440
, i.AdjustmentId
441
, i.Amount as scheduled_amount
442
, i.Dte
443
, ip.Amount as actual_amount
444
, ip.PaymentId
445
, g.CONSTIT_ID
446
, g.RECEIPT_AMOUNT
447
, g.DTE as receive_date
448
, g.TYPE
449
, DBO.TranslateGiftType(g.TYPE) as type
450
FROM Installment i
451
LEFT JOIN InstallmentPayment ip ON i.InstallmentId = ip.InstallmentId
452
LEFT JOIN GIFT g ON ip.PaymentId = g.ID
453
/* Adjustments are stored in here too - when an adjustment happens, the pledge ID of the original value is blanked */
454
WHERE i.PledgeId IS NOT NULL 
455
ORDER BY i.AdjustmentId
456
/* Write-off Types: Covenant WriteOff, MG Write Off, Write Off */
457
```
458
459
## Campaigns, Appeals, Packages
460
461
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.
462
463
464
## Actions
465
466
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:
467
```
468
SELECT
469
  a.ADDED_BY
470
, a.AUTO_REMIND
471
, a.RECORDS_ID as external_identifier
472
, cr.RELATION_ID as action_contact_id
473
, a.DTE as activity_date_time
474
, LETTER.LONGDESCRIPTION as letter
475
, a.PRIORITY as priority_id
476
, a.REMIND_VALUE
477
, a.CATEGORY
478
, a.Completed
479
, a.COMPLETED_DATE
480
, a.FUND_ID
481
, a.FOLLOWUPTO_ID
482
, a.TRACKACTION_ID
483
, a.PhoneNumber as phone_number
484
, a.Remind_Frequency
485
, a.WORDDOCNAME
486
, a.APPEAL_ID
487
, a.APPEAL_LETTER_CODE
488
, a.OUTLOOK_EMAIL_SUBJECT
489
, STATUS.LONGDESCRIPTION as status
490
, TYPE.LONGDESCRIPTION as type
491
, LOCATION.LONGDESCRIPTION as location
492
, ActionNotepad.ActualNotes
493
, CAMPAIGN.DESCRIPTION as campaign
494
FROM ACTIONS a
495
LEFT JOIN TABLEENTRIES as STATUS ON a.STATUS = STATUS.TABLEENTRIESID
496
LEFT JOIN TABLEENTRIES as TYPE ON a.[TYPE] = [TYPE].TABLEENTRIESID
497
LEFT JOIN TABLEENTRIES as LOCATION ON a.[Location] = LOCATION.TABLEENTRIESID 
498
LEFT JOIN TABLEENTRIES as LETTER on a.[LETTER_CODE] = LETTER.TABLEENTRIESID
499
LEFT JOIN ActionNotepad ON a.ID = ActionNotepad.ParentId
500
LEFT JOIN CAMPAIGN on a.CAMPAIGN_ID = CAMPAIGN.id
501
LEFT JOIN CONSTIT_RELATIONSHIPS cr on a.CONTACT_ID = cr.ID
502
```
503
504
"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.
505
506
*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.
507
508
## Action Notes
509
510
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:
511
```
512
SELECT
513
  NotesID
514
, Title
515
, Description
516
, Author
517
, ActualNotes
518
, ParentId
519
, NotepadDate
520
, TABLEENTRIES.LONGDESCRIPTION as Type
521
  FROM ActionNotepad
522
  LEFT JOIN TABLEENTRIES ON ActionNotepad.NoteTypeId = TABLEENTRIES.TABLEENTRIESID
523
ORDER BY ParentId, ActionNotepad."SEQUENCE"
524
```
525
526
## Events
527
528
Events are stored fairly similarly to CiviCRM, except:
529
* locations are stored on the event record itself (which I'm not dealing with).
530
* There's fields for storing data about classes.  I haven't delved into this - I suspect that this may tie into recurring events.
531
* "Event Category" and "Event Type" might both map to Civi's "Event Type".  This isn't the case for me.
532
* 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.
533
534
Here's some SQL to pull in the most relevant data:
535
```
536
SELECT
537
  se.CAPACITY
538
, se.END_DATE
539
, se.ID
540
, se.NAME
541
, se.START_DATE
542
, se.DATE_ADDED
543
, te.LONGDESCRIPTION as activity_type
544
, se.INACTIVE
545
, se.DISPLAYONCALENDAR
546
, CAMPAIGN.DESCRIPTION as campaign
547
, se.DESCRIPTION
548
FROM SPECIAL_EVENT se
549
LEFT JOIN CAMPAIGN on se.CAMPAIGN_ID = CAMPAIGN.id 
550
LEFT JOIN TABLEENTRIES te ON se.TYPEID = te.TABLEENTRIESID
551
```
552
553
## Notes
554
555
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). 
556
557
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.
558
559
It may be possible to export the photos in the Notes using the techniques described below under "Media".
560
561
Here's the SQL I'm currently using to extract notes before doing transforms in Kettle:
562
```
563
SELECT
564
Title as subject
565
, Description
566
, Author
567
, ActualNotes
568
, ParentId
569
, cn.DateChanged
570
, LONGDESCRIPTION as NoteType
571
FROM ConstituentNotepad cn
572
LEFT JOIN TABLEENTRIES ON NoteTypeId = TABLEENTRIESID
573
```
574
575
## Media
576
577
### Exporting from RE
578
579
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.
580
581
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: ```
582
SELECT OBJECT FROM [dbo].[MEDIA]
583
```
584
585
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.
586
587
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:
588
```
589
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
590
```
591
592
### Importing into CiviCRM
593
594
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.
595
596
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:
597
```
598
$result = civicrm_api3('Attachment', 'create', array(
599
  'sequential' => 1,
600
  'name' => "drill.jpg",
601
  'mime_type' => "image/jpeg",
602
  'entity_id' => 628,
603
  'entity_table' => "civicrm_activity",
604
  'options' => array('move-file' => "/home/jon/local/civicrm-buildkit/build/d46/sites/default/files/civicrm/custom/drill.jpg"),
605
));
606
```
607
608
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!
609
610
If you use the API CSV import tool, your CSVs should look like this:
611
```
612
"name",entity_id,"entity_table","mime_type","options.move-file"
613
"100.png",87511,"civicrm_activity","image/png","/home/jon/local/lccr/wp-content/plugins/files/civicrm/custom/100.png"
614
"1000.pdf",88411,"civicrm_activity","application/pdf","/home/jon/local/lccr/wp-content/plugins/files/civicrm/custom/1000.pdf"
615
```
616
617
## And More
618
619
### Tables that Civi doesn't have a direct counterpart for
620
621
* Aliases (stores Maiden Name and d/b/a - unsure how to import into Civi just yet)
622
* 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.
623
624
625
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.
626
627
628
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.
629
630
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.
631
632
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.
633
634
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.
635
636
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".
637
638
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.
639
640
NOTE: Letters sent is incomplete, there's more than 12 letters to some folks!
641
642
GIFTS is related to constituent on the last column (Constituent System Record ID)
643
644
### Code Tables/Option Groups/Option Values
645
646
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).
647
648
## Deciphering MS SQL
649
650
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.
651
652
### Looking Up Functions
653
654
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.
655
656
### Looking Up Stored Procedures
657
658
If, in the profiler, taking a certain action shows a command like this:
659
These have a syntax like:
660
```
661
exec sp_execute 48,43,'Acknowledgee'
662
```
663
664
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:
665
```
666
declare `p1 int
667
set `p1=48
668
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'
669
select `p1
670
```
671
672
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.
673
674
675
### Addressee/Postal Greeting/E-mail greeting
676
677
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.
678
679
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:
680
```<code class="sql">
681
/****** Script for SelectTopNRows command from SSMS  ******/
682
SELECT s.ID
683
, sf1.FIELDNAME as FIELD1
684
, sf2.FIELDNAME as FIELD2
685
, sf3.FIELDNAME as FIELD3
686
, sf4.FIELDNAME as FIELD4
687
, sf5.FIELDNAME as FIELD5
688
, sf6.FIELDNAME as FIELD6
689
, sf7.FIELDNAME as FIELD7
690
, sf8.FIELDNAME as FIELD8
691
, sf9.FIELDNAME as FIELD9
692
, sf10.FIELDNAME as FIELD10     
693
, sf11.FIELDNAME as FIELD11
694
, sf12.FIELDNAME as FIELD12
695
, sf13.FIELDNAME as FIELD13
696
, sf14.FIELDNAME as FIELD14
697
, sf15.FIELDNAME as FIELD15
698
, sf16.FIELDNAME as FIELD16
699
, sf17.FIELDNAME as FIELD17
700
, sf18.FIELDNAME as FIELD18
701
, sf19.FIELDNAME as FIELD19
702
, sf20.FIELDNAME as FIELD20
703
  FROM SALUTATIONS s
704
  LEFT JOIN SALUTATION_FIELDS sf1 on CODE1 = sf1.ID
705
  LEFT JOIN SALUTATION_FIELDS sf2 on CODE2 = sf2.ID
706
  LEFT JOIN SALUTATION_FIELDS sf3 on CODE3 = sf3.ID
707
  LEFT JOIN SALUTATION_FIELDS sf4 on CODE4 = sf4.ID
708
  LEFT JOIN SALUTATION_FIELDS sf5 on CODE5 = sf5.ID
709
  LEFT JOIN SALUTATION_FIELDS sf6 on CODE6 = sf6.ID
710
  LEFT JOIN SALUTATION_FIELDS sf7 on CODE7 = sf7.ID
711
  LEFT JOIN SALUTATION_FIELDS sf8 on CODE8 = sf8.ID
712
  LEFT JOIN SALUTATION_FIELDS sf9 on CODE9 = sf9.ID
713
  LEFT JOIN SALUTATION_FIELDS sf10 on CODE10 = sf10.ID
714
  LEFT JOIN SALUTATION_FIELDS sf11 on CODE11 = sf11.ID
715
  LEFT JOIN SALUTATION_FIELDS sf12 on CODE12 = sf12.ID
716
  LEFT JOIN SALUTATION_FIELDS sf13 on CODE13 = sf13.ID
717
  LEFT JOIN SALUTATION_FIELDS sf14 on CODE14 = sf14.ID
718
  LEFT JOIN SALUTATION_FIELDS sf15 on CODE15 = sf15.ID
719
  LEFT JOIN SALUTATION_FIELDS sf16 on CODE16 = sf16.ID
720
  LEFT JOIN SALUTATION_FIELDS sf17 on CODE17 = sf17.ID
721
  LEFT JOIN SALUTATION_FIELDS sf18 on CODE18 = sf18.ID
722
  LEFT JOIN SALUTATION_FIELDS sf19 on CODE19 = sf19.ID
723
  LEFT JOIN SALUTATION_FIELDS sf20 on CODE20 = sf20.ID
724
</code>```
725
726
727
### Things I see that RE does better than Civi:
728
729
* 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.
730
* 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.
731
* 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.