Project

General

Profile

Exporting Raisers Edge for CiviCRM » History » Version 40

Jon Goldberg, 12/26/2018 06:47 PM

1 5 Jon Goldberg
{{last_updated_at}} by {{last_updated_by}}
2 1 Jon Goldberg
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 7 Jon Goldberg
```sql
65
SELECT RECORDSID AS external_identifier, LONGDESCRIPTION as solicit_code 
66
FROM CONSTITUENT_SOLICITCODES JOIN TABLEENTRIES ON SOLICIT_CODE = TABLEENTRIES.TABLEENTRIESID 
67
WHERE TABLEENTRIES.ACTIVE = -1
68 1 Jon Goldberg
```
69
70
In my copy of RE, the CODETABLESID is 5044, so to get a list of all solicit codes, use:
71 7 Jon Goldberg
```sql
72
SELECT LONGDESCRIPTION, ACTIVE 
73
FROM TABLEENTRIES 
74
WHERE CODETABLESID = 5044 
75
ORDER BY SEQUENCE;
76 1 Jon Goldberg
```
77
78
### Addresses
79
80
SQL tables: ADDRESS, CONSTIT_ADDRESS
81
82 2 Jon Goldberg
Addresses are a many-to-many relationship in RE.
83
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.
84
85
Source: http://www.kb.blackbaud.co.uk/articles/Article/41141
86
The address Indicator field is located on the CONSTIT_ADDRESS table and can have the following values: 
87
~~~
88
0 = Alternate
89
1 = Contact
90
2 = Preferred (Primary Address entered on the Bio 1 tab of the constituent record)
91
3 = Business
92
4 = Spouse Business
93
5 = Spouse Preferred
94
6 = Spouse Alternate
95
7 = Relationship
96
~~~
97 3 Jon Goldberg
98
`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.
99 4 Jon Goldberg
100
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.
101
102
Here's the SQL I use for getting address data in Kettle:
103
104
~~~sql
105
SELECT
106
  ADDRESS_BLOCK
107
, CITY
108
, POST_CODE
109
, "STATE"
110
, CTY.LONGDESCRIPTION as country
111
, ADDRESS_ID
112
, ca.CONSTIT_ID
113
, PREFERRED
114
, LOC_TYPE.LONGDESCRIPTION
115
, ca.DATE_FROM
116
, ca.DATE_TO
117
, SENDMAIL
118
, ca.ID AS CAID
119
, cr.IS_EMPLOYEE
120
, cr.IS_PRIMARY AS IS_RELATIONSHIP_PRIMARY
121
, cr.RELATION_ID
122
, ca.INDICATOR
123
FROM ADDRESS a
124
LEFT JOIN TABLEENTRIES AS CTY ON CTY.TABLEENTRIESID = COUNTRY
125
JOIN CONSTIT_ADDRESS ca ON a.ID = ca.ADDRESS_ID
126
LEFT JOIN TABLEENTRIES AS LOC_TYPE ON ca."TYPE" = LOC_TYPE.TABLEENTRIESID
127
LEFT JOIN RECORDS r ON ca.CONSTIT_ID = r.ID
128
-- This next join is needed to handle address sharing with the business
129
LEFT JOIN CONSTIT_RELATIONSHIPS cr ON ca.ID = cr.CONSTIT_ADDRESS_ID AND ca.CONSTIT_ID = cr.CONSTIT_ID
130
WHERE INDICATOR <> 7 AND ADDRESS_BLOCK IS NOT NULL
131
~~~
132 2 Jon Goldberg
133
### Primary Business Information 
134 1 Jon Goldberg
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.
135
136
### Phones/E-mail/websites
137 6 Jon Goldberg
138 1 Jon Goldberg
Email and websites are stored in the `PHONES` table along with phone numbers.
139 6 Jon Goldberg
140
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.
141 1 Jon Goldberg
142
Notes:
143
* You can NOT have duplicate phone types in RE, so no need to try and catch multiple "Home" numbers!
144
* Oh - except that one contact can have two home phone numbers on two different addresses.
145
* Don't forget to filter out duplicate numbers/e-mails/etc. when someone puts the same phone number on two different addresses.
146 6 Jon Goldberg
147
This SQL gets me a useful list of phones and e-mail for further processing in Kettle (for RE 7.96):
148
149 1 Jon Goldberg
```sql
150 6 Jon Goldberg
SELECT DISTINCT
151 1 Jon Goldberg
PHONES.CONSTIT_ID
152
, NUM
153 6 Jon Goldberg
, DO_NOT_CALL
154
, LONGDESCRIPTION AS location_type
155
, PHONES."SEQUENCE"
156
, PHONES.PHONESID
157
FROM PHONES
158
LEFT JOIN TABLEENTRIES ON PHONETYPEID = TABLEENTRIESID
159
LEFT JOIN RECORDS r ON r.ID = PHONES.CONSTIT_ID
160
WHERE PHONES.INACTIVE = 0
161
AND CONSTIT_RELATIONSHIPS_ID IS NULL
162
--Sequence so we get primary number correct
163 1 Jon Goldberg
ORDER BY PHONES.PHONESID, PHONES."SEQUENCE"
164
```
165
166
### Relationships
167
168
Relevant SQL table: CONSTIT_RELATIONSHIPS
169
170
Relationships are different in Civi and RE in the following significant ways:
171
* Relationships don't have to have a relationship type.
172
* 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".
173
* 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.
174
* There need not be a relationship type at all.  This doesn't make sense, except that:
175
* There are hardcoded fields for IS_SPOUSE, HON_MEM_ACKNOWLEDGE, IS_HEADOFHOUSEHOLD, and SOFTCREDIT_GIFTS.
176
177
Because relationships aren't necessarily reciprocal, I find it helpful to take my list of invalid relationships and do BOTH of the following:
178
* Look up the RELATIONSHIP_TYPE against the `name_b_a` field in `civicrm_relationship_type`.
179
* Look up the RECIP_RELATIONSHIP_TYPE against both `name_a_b` and `name_b_a` in `civicrm_relationship_type`.
180
181
### Solicitor Relationships
182
183
Solicitor relationships are stored in a different table.  I used this SQL to extract them:
184 8 Jon Goldberg
```sql
185 1 Jon Goldberg
SELECT
186
CONSTIT_ID
187
, SOLICITOR_ID
188
, TABLEENTRIES.LONGDESCRIPTION as solicitor_type
189
, AMOUNT
190
, NOTES
191
, cs."SEQUENCE" as weight
192
FROM CONSTIT_SOLICITORS cs
193
LEFT JOIN TABLEENTRIES ON cs.SOLICITOR_TYPE = TABLEENTRIES.TABLEENTRIESID
194
ORDER BY weight
195
```
196
197
### Attributes
198
199
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.
200
201
Valuable information about the setup of the attributes is available in RE from *Config > Attributes*.
202
203
* The analogous field to `civicrm_custom_field` is `AttributeTypes`.
204
* `AttributeTypes.CODETABLESID` gives a lookup for the RE "option group" that contains valid options for that attribute.
205 25 Jon Goldberg
* 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.
206 1 Jon Goldberg
 
207
Here's my preliminary SQL to export attributes from RE:
208 9 Jon Goldberg
```sql
209 1 Jon Goldberg
SELECT
210 25 Jon Goldberg
at.DESCRIPTION as Category
211 1 Jon Goldberg
, TABLEENTRIES.LONGDESCRIPTION as Description
212
, TEXT
213
, "BOOLEAN"
214
, COMMENTS
215
, ca.ATTRIBUTEDATE
216 25 Jon Goldberg
, ca.PARENTID
217 1 Jon Goldberg
FROM ConstituentAttributes ca
218
JOIN AttributeTypes at ON ca.ATTRIBUTETYPESID = at.ATTRIBUTETYPESID
219
LEFT JOIN TABLEENTRIES ON ca.TABLEENTRIESID = TABLEENTRIES.TABLEENTRIESID
220 25 Jon Goldberg
-- This UNION adds non-constituent attributes
221
UNION 
222
SELECT
223
at.DESCRIPTION as Category
224
, TABLEENTRIES.LONGDESCRIPTION as Description
225
, TEXT
226
, "BOOLEAN"
227
, COMMENTS
228
, ra.ATTRIBUTEDATE
229
, cr.RELATION_ID AS PARENTID
230
FROM RELATIONSHIPATTRIBUTES ra
231
JOIN AttributeTypes at ON ra.ATTRIBUTETYPESID = at.ATTRIBUTETYPESID
232
LEFT JOIN TABLEENTRIES ON ra.TABLEENTRIESID = TABLEENTRIES.TABLEENTRIESID
233
JOIN CONSTIT_RELATIONSHIPS cr ON ra.PARENTID = cr.ID
234 1 Jon Goldberg
```
235
236
To get a list of option values out of RE for the attributes, use this SQL:
237 9 Jon Goldberg
```sql
238 1 Jon Goldberg
SELECT
239
DESCRIPTION
240
, at.CODETABLESID
241
, LONGDESCRIPTION
242
FROM TABLEENTRIES te 
243
LEFT JOIN AttributeTypes at ON te.CODETABLESID = at.CODETABLESID
244
ORDER BY DESCRIPTION
245
```
246
247
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:
248 9 Jon Goldberg
```sql
249 1 Jon Goldberg
SELECT ATTRIBUTETYPESID, PARENTID, COUNT(LONGDESCRIPTION)
250
FROM ConstituentAttributes ca
251
JOIN TABLEENTRIES te ON ca.TABLEENTRIESID = te.TABLEENTRIESID
252
GROUP BY PARENTID, ATTRIBUTETYPESID
253
HAVING COUNT(LONGDESCRIPTION) > 1
254
ORDER BY ATTRIBUTETYPESID
255
```
256
257 26 Jon Goldberg
*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.
258 1 Jon Goldberg
259
### Salutations/addressee info
260
261
RE stores contact salutations and addressee info in two places.
262
263
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`.
264
265
An unlimited number of non-primary salutations can be stored in the `CONSTITUENT_SALUTATION` table.
266
267
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".
268
269
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.
270
271 28 Jon Goldberg
Salutation types can be found with:
272
```sql
273
SELECT te.LONGDESCRIPTION as label
274
FROM TABLEENTRIES te
275
JOIN CODETABLES ct ON te.CODETABLESID = ct.CODETABLESID
276
WHERE ct.NAME = 'Addr/Sal Types'
277
ORDER BY te.SEQUENCE
278
```
279
280 27 Jon Goldberg
Joseph Lacey's [Salutations extension](https://github.com/josephlacey/com.jlacey.salutations) gives CiviCRM a salutations interface comparable to Raiser's Edge.
281 28 Jon Goldberg
282
283 27 Jon Goldberg
284 1 Jon Goldberg
### Other constituent tables:
285
286
Skip these tables:
287
* Spouse
288
* Gifts
289
* First Gift, Last gift, Largest Gift
290
* Actions
291
* First Action, Last Action
292
* Summary Information
293
294
## Contribution-related exports
295
296
### Contributions/Gifts
297
298
Contributions (in RE parlance: Gifts) are complicated beasts!
299
300
Here are some relevant database tables and their equivalent in Civi:
301
GIFT	civicrm_contribution
302
GiftSplit	civicrm_line_item
303
CAMPAIGN	Roughly maps to Campaign.  Your mapping may vary and/or include custom fields.
304
APPEAL	Also roughly maps to Campaign (or Source).  Your mapping may vary and/or include custom fields.
305 29 Jon Goldberg
FUND	Roughly maps to Financial Type, but you might choose to import as a custom field instead.  If you need accounting integration, FUND *must* be imported as a financial type.
306 1 Jon Goldberg
307
See "Campaigns, Appeals, Packages" below for more.
308
309
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).
310
311
Note that gift type is hardcoded into a function called "TranslateGiftType) - so you may want to include that function in your SQL, e.g.:
312 10 Jon Goldberg
```sql
313 1 Jon Goldberg
SELECT
314
gs.GiftId
315
, g.CONSTIT_ID
316
, gs.Amount
317
, g.DTE as gift_date
318
, FUND.DESCRIPTION as fund
319
, CAMPAIGN.DESCRIPTION as campaign
320
, APPEAL.DESCRIPTION as appeal
321
, g.PAYMENT_TYPE
322
, g.ACKNOWLEDGE_FLAG
323
, g.CHECK_NUMBER
324
, g.CHECK_DATE
325
, g.BATCH_NUMBER
326
, g.ANONYMOUS
327
, gst.LONGDESCRIPTION as giftsubtype
328
, g.TYPE
329
, DBO.TranslateGiftType(g.TYPE) as type2
330
FROM GiftSplit gs
331
LEFT JOIN FUND on gs.FundId = FUND.id
332
LEFT JOIN APPEAL on gs.AppealId = APPEAL.id
333
LEFT JOIN CAMPAIGN on gs.CampaignId = CAMPAIGN.id 
334
LEFT JOIN GIFT g on gs.GiftId = g.ID
335
LEFT JOIN TABLEENTRIES gst on g.GIFTSUBTYPE = gst.TABLEENTRIESID
336
```
337
338
(See here: http://www.re-decoded.com/2013/07/payment-type-or-payment-method-id-reference/#more-714)
339
340
Payment Type is also hard-coded, it seems:
341
1	Cash
342
2	Personal Check
343
3	Business Check
344
4	Credit Card
345
5 	Standing Order
346
6	Direct Debit
347
7	Voucher
348
8	Other
349
350
### Soft Credits
351
352
Stored in GIFTSOFTCREDIT.  RE does NOT have the concept of a soft credit type - which is fine.
353 11 Jon Goldberg
```sql
354 1 Jon Goldberg
SELECT
355
, GiftId
356
, ConstitId
357
, Amount
358
, 'Soft Credit' as soft_credit_type
359
FROM GiftSoftCredit
360
```
361
362
### Solicitor, Gift
363
364
(Important!  Gift solicitors are different from Contact Solicitors)
365
366
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:
367 12 Jon Goldberg
```sql
368 1 Jon Goldberg
SELECT
369
ParentId as gift_id
370
, SolicitorId as soft_creditee_external_identifier
371
, Amount
372
, 'Solicitor' as soft_credit_type
373
FROM GiftSolicitor
374
```
375
376
### In Honor/Memorial Of (aka Tributes)
377
378 40 Jon Goldberg
In CiviCRM, In Honor/Memorial of is considered a form of soft credit.  RE has a much richer Tribute experience, assuming you have the Tributes module installed:
379
* Tributes are their own entity. (table is `TRIBUTE`).
380
* Tributes have acknowledgees (e.g. give in memory of someone, acknowledge their child or spouse) (table is `TRIBUTE_ACKNOWLEDGEE`).
381
* Gifts are made against the tribute, not the contact (so a person can have multiple tributes, each tracked separately) (table is `GIFT_TRIBUTE`).
382
* A gift might not be acknowledged to every acknowledgee (e.g. I make a gift in memory of my grandparent; I might want the gift acknowledged to my parent but not my sibling) (table is `GIFT_TRIBUTE_ACKNOWLEDGEE`).
383 1 Jon Goldberg
384 40 Jon Goldberg
By comparison, CiviCRM has just `civicrm_contribution` and `civicrm_contribution_soft`.
385
386
Some of the RE model can be replicated with custom fields on soft credits.  I have a CiviCRM extension (still in testing, not released yet - but ask me for it) that exposes Soft Credit custom fields to the contribution screen and to search.
387
388 1 Jon Goldberg
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.
389
390 13 Jon Goldberg
SELECT
391 1 Jon Goldberg
```sql
392
gt.GIFT_ID
393
, gt.TRIBUTE_TYPE
394
, t.DESCRIPTION
395 40 Jon Goldberg
, t.RECORDS_ID as tributee_external_identifier
396 1 Jon Goldberg
, te.LONGDESCRIPTION as tribute_type
397
FROM GIFT_TRIBUTE gt
398
JOIN TRIBUTE t ON gt.TRIBUTE_ID = t.ID
399
LEFT JOIN TABLEENTRIES te on gt.TRIBUTE_TYPE = te.TABLEENTRIESID
400
```
401
402
### Pledges
403
404
Here are the relevant tables and their equivalents in Civi:
405 23 Jon Goldberg
```
406 1 Jon Goldberg
GIFT
407
Installment
408
InstallmentPayment
409 23 Jon Goldberg
```
410 1 Jon Goldberg
411 23 Jon Goldberg
`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.  
412 1 Jon Goldberg
413
`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.
414
415
*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.
416
417
The INSTALLMENT_FREQUENCY list is hard-coded:
418 14 Jon Goldberg
```
419
1 Annually
420
2 Every 6 Months
421
3 Every 3 Months
422
4 Every 2 Months
423
5 Every Month
424
6 Due Twice/Month
425
9 Irregular
426
10 Single Installment
427
```
428 1 Jon Goldberg
429
Some SQL:
430 14 Jon Goldberg
```sql
431 1 Jon Goldberg
/* Find all GIFT records with one or more associated Installment records.  These are pledges OR recurring gifts. */
432
SELECT DISTINCT
433
g.CONSTIT_ID
434
, g.ID as GiftId
435
, g.Amount
436
, g.DTE as receive_date
437
, FUND.DESCRIPTION as fund
438
, FUND.FUND_ID
439
, CAMPAIGN.DESCRIPTION as campaign
440
, APPEAL.DESCRIPTION as appeal
441
, g.PAYMENT_TYPE
442
, g.ACKNOWLEDGEDATE
443
, DBO.TranslateGiftType(g.TYPE) as type
444
, g.REF as note
445
,DATE_1ST_PAY
446
,g.DATEADDED
447
,g.DATECHANGED
448
,INSTALLMENT_FREQUENCY
449
,NUMBER_OF_INSTALLMENTS
450
,POST_DATE
451
,POST_STATUS
452
,REMIND_FLAG
453
,Schedule_Month
454
,Schedule_DayOfMonth
455
,Schedule_MonthlyDayOfWeek
456
,Schedule_Spacing
457
,Schedule_MonthlyType
458
,Schedule_MonthlyOrdinal
459
,Schedule_WeeklyDayOfWeek
460
,Schedule_DayOfMonth2
461
,Schedule_SMDayType1
462
,Schedule_SMDayType2
463
,NextTransactionDate
464
,Schedule_EndDate
465
,FrequencyDescription
466
, r.CONSTITUENT_ID
467
FROM Gift g
468
LEFT JOIN GiftSplit gs on g.ID = gs.GiftId
469
LEFT JOIN FUND on gs.FundId = FUND.id
470
LEFT JOIN APPEAL on gs.AppealId = APPEAL.id
471
LEFT JOIN CAMPAIGN on gs.CampaignId = CAMPAIGN.id 
472
LEFT JOIN RECORDS r ON g.CONSTIT_ID = r.ID
473
JOIN Installment i ON g.ID = i.PledgeId
474
```
475
476
Find pledge payments:
477 14 Jon Goldberg
```sql
478 1 Jon Goldberg
/* Find all pledge installments, and their related payments if they exist. */
479
SELECT
480
i.InstallmentId
481
, i.PledgeId
482
, i.AdjustmentId
483
, i.Amount as scheduled_amount
484
, i.Dte
485
, ip.Amount as actual_amount
486
, ip.PaymentId
487
, g.CONSTIT_ID
488
, g.RECEIPT_AMOUNT
489
, g.DTE as receive_date
490
, g.TYPE
491
, DBO.TranslateGiftType(g.TYPE) as type
492
FROM Installment i
493
LEFT JOIN InstallmentPayment ip ON i.InstallmentId = ip.InstallmentId
494
LEFT JOIN GIFT g ON ip.PaymentId = g.ID
495
/* Adjustments are stored in here too - when an adjustment happens, the pledge ID of the original value is blanked */
496
WHERE i.PledgeId IS NOT NULL 
497
ORDER BY i.AdjustmentId
498
/* Write-off Types: Covenant WriteOff, MG Write Off, Write Off */
499
```
500 23 Jon Goldberg
501 24 Jon Goldberg
#### Matching gifts
502
Gifts are connected to their matching gift via the `GiftMatchingGift` table, and appear in both the matched person's and matcher's "gifts" tab.
503
504 23 Jon Goldberg
#### Pledges as contributions
505
If you're working with an organization that wants to use CiviAccounts, you'll want to track pledges as pending contributions.
506 1 Jon Goldberg
507 30 Jon Goldberg
### Funds
508
Funds correspond to financial types, but have far more data - start/end dates, categories - and can also have attributes (custom fields) and relationships to contacts (e.g. "endowment establisher").
509
510
The FUND table has the main data, but `FundAttributes` and `CONSTIT_Funds` contain the attributes and relationships respectively.
511
512 1 Jon Goldberg
## Campaigns, Appeals, Packages
513
514
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.
515
516
## Actions
517
518
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:
519 15 Jon Goldberg
```sql
520 1 Jon Goldberg
SELECT
521
  a.ADDED_BY
522
, a.AUTO_REMIND
523
, a.RECORDS_ID as external_identifier
524
, cr.RELATION_ID as action_contact_id
525
, a.DTE as activity_date_time
526
, LETTER.LONGDESCRIPTION as letter
527
, a.PRIORITY as priority_id
528
, a.REMIND_VALUE
529
, a.CATEGORY
530
, a.Completed
531
, a.COMPLETED_DATE
532
, a.FUND_ID
533
, a.FOLLOWUPTO_ID
534
, a.TRACKACTION_ID
535
, a.PhoneNumber as phone_number
536
, a.Remind_Frequency
537
, a.WORDDOCNAME
538
, a.APPEAL_ID
539
, a.APPEAL_LETTER_CODE
540
, a.OUTLOOK_EMAIL_SUBJECT
541
, STATUS.LONGDESCRIPTION as status
542
, TYPE.LONGDESCRIPTION as type
543
, LOCATION.LONGDESCRIPTION as location
544
, ActionNotepad.ActualNotes
545
, CAMPAIGN.DESCRIPTION as campaign
546
FROM ACTIONS a
547
LEFT JOIN TABLEENTRIES as STATUS ON a.STATUS = STATUS.TABLEENTRIESID
548
LEFT JOIN TABLEENTRIES as TYPE ON a.[TYPE] = [TYPE].TABLEENTRIESID
549
LEFT JOIN TABLEENTRIES as LOCATION ON a.[Location] = LOCATION.TABLEENTRIESID 
550
LEFT JOIN TABLEENTRIES as LETTER on a.[LETTER_CODE] = LETTER.TABLEENTRIESID
551
LEFT JOIN ActionNotepad ON a.ID = ActionNotepad.ParentId
552
LEFT JOIN CAMPAIGN on a.CAMPAIGN_ID = CAMPAIGN.id
553
LEFT JOIN CONSTIT_RELATIONSHIPS cr on a.CONTACT_ID = cr.ID
554
```
555
556
"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.
557
558
*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.
559
560
## Action Notes
561
562
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:
563 16 Jon Goldberg
```sql
564 1 Jon Goldberg
SELECT
565
  NotesID
566
, Title
567
, Description
568
, Author
569
, ActualNotes
570
, ParentId
571
, NotepadDate
572
, TABLEENTRIES.LONGDESCRIPTION as Type
573
  FROM ActionNotepad
574
  LEFT JOIN TABLEENTRIES ON ActionNotepad.NoteTypeId = TABLEENTRIES.TABLEENTRIESID
575
ORDER BY ParentId, ActionNotepad."SEQUENCE"
576
```
577
578
## Events
579
580
Events are stored fairly similarly to CiviCRM, except:
581
* locations are stored on the event record itself (which I'm not dealing with).
582
* There's fields for storing data about classes.  I haven't delved into this - I suspect that this may tie into recurring events.
583
* "Event Category" and "Event Type" might both map to Civi's "Event Type".  This isn't the case for me.
584
* 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.
585
586
Here's some SQL to pull in the most relevant data:
587 17 Jon Goldberg
```sql
588 1 Jon Goldberg
SELECT
589
  se.CAPACITY
590
, se.END_DATE
591
, se.ID
592
, se.NAME
593
, se.START_DATE
594
, se.DATE_ADDED
595
, te.LONGDESCRIPTION as activity_type
596
, se.INACTIVE
597
, se.DISPLAYONCALENDAR
598
, CAMPAIGN.DESCRIPTION as campaign
599
, se.DESCRIPTION
600
FROM SPECIAL_EVENT se
601
LEFT JOIN CAMPAIGN on se.CAMPAIGN_ID = CAMPAIGN.id 
602
LEFT JOIN TABLEENTRIES te ON se.TYPEID = te.TABLEENTRIESID
603
```
604
605 31 Jon Goldberg
## Memberships
606 36 Jon Goldberg
While I haven't delved deeply into memberships, it's worth noting that RE uses SQL Views to generate the `Memberships` View, which combines the `MEMBER`, `MEMBER_JUNCTION` and `MembershipTransaction` and does some filtering to only show the most recent membership info.  `RecID` is the foreign key to `RECORDS.ID`.
607 1 Jon Goldberg
608 39 Jon Goldberg
* `MEMBER` is comparable to `civicrm_membership`, with `MEMBER_JUNCTION` allowing a many-to-many relationship between memberships and contacts.
609
* `MembershipTransaction` is comparable to `civicrm_membership_log` in that it tracks each change to the membership status.
610
* `MembershipCategory` is comparable to `civicrm_membership_type`. `MembershipCategory.CategoryID` is a foreign key to `TABLEENTRIES`, which stores the value that maps to `civicrm_membership_type.name`.  The category (membership type) can be found at `MembershipTransaction.Category`.
611
* `TransactionGift` is comparable to `civicrm_membership_payment`.  It stores the "Linked Gift" record.
612 1 Jon Goldberg
613 38 Jon Goldberg
`Memberships.CurrentTransactionID` is helpful because it gives you the correct record in `MembershipTransaction` to pull info from such as category and expiration date.
614 32 Jon Goldberg
615 1 Jon Goldberg
## Notes
616
617
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). 
618
619
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.
620
621
It may be possible to export the photos in the Notes using the techniques described below under "Media".
622
623
Here's the SQL I'm currently using to extract notes before doing transforms in Kettle:
624 18 Jon Goldberg
```sql
625 1 Jon Goldberg
SELECT
626
Title as subject
627
, Description
628
, Author
629
, ActualNotes
630
, ParentId
631
, cn.DateChanged
632
, LONGDESCRIPTION as NoteType
633
FROM ConstituentNotepad cn
634
LEFT JOIN TABLEENTRIES ON NoteTypeId = TABLEENTRIESID
635
```
636 22 Jon Goldberg
637
## Localization formats
638
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:
639
```sql
640
SELECT LONGDESCRIPTION, COUNTRY_CODES.*
641
  FROM COUNTRY_CODES
642
  JOIN TABLEENTRIES ON COUNTRY_CODES.COUNTRYCODESID = TABLEENTRIES.TABLEENTRIESID
643
```
644
645 37 Jon Goldberg
Address formats (stored in Civi in `civicrm_address_format`) are stored in `COUNTRY_LABELS`.  You can add as 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:
646 22 Jon Goldberg
```
647
1 = Address lines
648
2 = City
649
3 = State/Province
650
4 = Postcode
651
5 = Country
652
6 = Country abbreviation
653
9 = County
654
101 = :
655
102 = ,
656
103 = -
657
104 = .
658
105 = /
659
106 = <space>
660
```
661
662 21 Jon Goldberg
## Financial accounts
663
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).
664
The relevant fields are:
665
* `FUND` (relates to `GIFT.FundId`).
666
* `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).
667
* `GLDISTRIBUTIONDETAIL` (Contains GL Account codes, etc. Looks like it contains some Quickbooks integration fields. Most similar to `civicrm_financial_account`.)
668
* `GLDISTRIBUTION` (This also links to `FUNDDISTRIBUTION` but AFAICT has no data that needs migrating to CiviCRM).
669
670
## Misc
671
672
Some RE tables and what they're for (and their equivalents in Civi if applicable):
673
`GENERAL_INFORMATION`: Similar to both `civicrm_domain` and `civicrm_settings`.  Version number, Organization name, license number, general settings, etc.
674 1 Jon Goldberg
675
## Media
676
677
### Exporting from RE
678
679
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.
680
681 19 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: 
682
```sql
683 1 Jon Goldberg
SELECT OBJECT FROM [dbo].[MEDIA]
684
```
685
686
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.
687
688
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:
689
```
690
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
691
```
692
693
### Importing into CiviCRM
694
695
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.
696
697
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:
698 19 Jon Goldberg
```php
699 1 Jon Goldberg
$result = civicrm_api3('Attachment', 'create', array(
700
  'sequential' => 1,
701
  'name' => "drill.jpg",
702
  'mime_type' => "image/jpeg",
703
  'entity_id' => 628,
704
  'entity_table' => "civicrm_activity",
705
  'options' => array('move-file' => "/home/jon/local/civicrm-buildkit/build/d46/sites/default/files/civicrm/custom/drill.jpg"),
706
));
707
```
708
709
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!
710
711
If you use the API CSV import tool, your CSVs should look like this:
712
```
713
"name",entity_id,"entity_table","mime_type","options.move-file"
714
"100.png",87511,"civicrm_activity","image/png","/home/jon/local/lccr/wp-content/plugins/files/civicrm/custom/100.png"
715
"1000.pdf",88411,"civicrm_activity","application/pdf","/home/jon/local/lccr/wp-content/plugins/files/civicrm/custom/1000.pdf"
716
```
717
718
## And More
719
720
### Tables that Civi doesn't have a direct counterpart for
721
722
* Aliases (stores Maiden Name and d/b/a - unsure how to import into Civi just yet)
723
* 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.
724
725
726
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.
727
728
729
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.
730
731
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.
732
733
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.
734
735
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.
736
737
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".
738
739
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.
740
741
NOTE: Letters sent is incomplete, there's more than 12 letters to some folks!
742
743
GIFTS is related to constituent on the last column (Constituent System Record ID)
744
745
### Code Tables/Option Groups/Option Values
746
747
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).
748
749
## Deciphering MS SQL
750
751
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.
752
753
### Looking Up Functions
754
755
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.
756
757
### Looking Up Stored Procedures
758
759
If, in the profiler, taking a certain action shows a command like this:
760
These have a syntax like:
761 19 Jon Goldberg
```sql
762 1 Jon Goldberg
exec sp_execute 48,43,'Acknowledgee'
763
```
764
765
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:
766 19 Jon Goldberg
```sql
767 1 Jon Goldberg
declare `p1 int
768
set `p1=48
769
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'
770
select `p1
771
```
772
773
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.
774
775
### Addressee/Postal Greeting/E-mail greeting
776
777
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.
778
779
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:
780 19 Jon Goldberg
```sql
781 1 Jon Goldberg
/****** Script for SelectTopNRows command from SSMS  ******/
782
SELECT s.ID
783
, sf1.FIELDNAME as FIELD1
784
, sf2.FIELDNAME as FIELD2
785
, sf3.FIELDNAME as FIELD3
786
, sf4.FIELDNAME as FIELD4
787
, sf5.FIELDNAME as FIELD5
788
, sf6.FIELDNAME as FIELD6
789
, sf7.FIELDNAME as FIELD7
790
, sf8.FIELDNAME as FIELD8
791
, sf9.FIELDNAME as FIELD9
792
, sf10.FIELDNAME as FIELD10     
793
, sf11.FIELDNAME as FIELD11
794
, sf12.FIELDNAME as FIELD12
795
, sf13.FIELDNAME as FIELD13
796
, sf14.FIELDNAME as FIELD14
797
, sf15.FIELDNAME as FIELD15
798
, sf16.FIELDNAME as FIELD16
799
, sf17.FIELDNAME as FIELD17
800
, sf18.FIELDNAME as FIELD18
801
, sf19.FIELDNAME as FIELD19
802
, sf20.FIELDNAME as FIELD20
803
  FROM SALUTATIONS s
804
  LEFT JOIN SALUTATION_FIELDS sf1 on CODE1 = sf1.ID
805
  LEFT JOIN SALUTATION_FIELDS sf2 on CODE2 = sf2.ID
806
  LEFT JOIN SALUTATION_FIELDS sf3 on CODE3 = sf3.ID
807
  LEFT JOIN SALUTATION_FIELDS sf4 on CODE4 = sf4.ID
808
  LEFT JOIN SALUTATION_FIELDS sf5 on CODE5 = sf5.ID
809
  LEFT JOIN SALUTATION_FIELDS sf6 on CODE6 = sf6.ID
810
  LEFT JOIN SALUTATION_FIELDS sf7 on CODE7 = sf7.ID
811
  LEFT JOIN SALUTATION_FIELDS sf8 on CODE8 = sf8.ID
812
  LEFT JOIN SALUTATION_FIELDS sf9 on CODE9 = sf9.ID
813
  LEFT JOIN SALUTATION_FIELDS sf10 on CODE10 = sf10.ID
814
  LEFT JOIN SALUTATION_FIELDS sf11 on CODE11 = sf11.ID
815
  LEFT JOIN SALUTATION_FIELDS sf12 on CODE12 = sf12.ID
816
  LEFT JOIN SALUTATION_FIELDS sf13 on CODE13 = sf13.ID
817
  LEFT JOIN SALUTATION_FIELDS sf14 on CODE14 = sf14.ID
818
  LEFT JOIN SALUTATION_FIELDS sf15 on CODE15 = sf15.ID
819
  LEFT JOIN SALUTATION_FIELDS sf16 on CODE16 = sf16.ID
820
  LEFT JOIN SALUTATION_FIELDS sf17 on CODE17 = sf17.ID
821
  LEFT JOIN SALUTATION_FIELDS sf18 on CODE18 = sf18.ID
822
  LEFT JOIN SALUTATION_FIELDS sf19 on CODE19 = sf19.ID
823
  LEFT JOIN SALUTATION_FIELDS sf20 on CODE20 = sf20.ID
824 20 Jon Goldberg
```
825 1 Jon Goldberg
826
827
### Things I see that RE does better than Civi:
828
829
* 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.
830
* 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.
831
* 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.