Project

General

Profile

Exporting Raiser's Edge for CiviCRM » History » Version 70

Jon Goldberg, 06/28/2017 12:46 PM
Campaigns, Appeals, Packages

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