Project

General

Profile

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

Jon Goldberg, 06/10/2015 09:05 AM

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 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.
8 1 Jon Goldberg
9 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
10
11 23 Jon Goldberg
h2. Export tool - general guide.
12
13
The Raiser's Edge Export tool is on the left toolbar when you first enter Raiser's Edge.
14
15 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).
16
17 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.
18 3 Jon Goldberg
19 1 Jon Goldberg
h2. Constituent Based Exports
20
21 21 Jon Goldberg
h3. Contact Information
22 1 Jon Goldberg
23 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".
24 1 Jon Goldberg
25 23 Jon Goldberg
h3. SQL
26
27 1 Jon Goldberg
If extracting directly from SQL, @SELECT * FROM RECORDS@.
28 21 Jon Goldberg
29 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.
30 21 Jon Goldberg
31 23 Jon Goldberg
h3. Export tool (NOTE: This ONLY gets constituents).
32
33 1 Jon Goldberg
Tab 1. General:
34
- Include all records.
35
- Head of Household processing: Export both constituents separately.
36
- Check all of the "Include these Constitutents": Inactive, deceased, no valid address
37
38
Tab 2: Output.
39
First, expand the "Constituent Information" in the left pane, and add every field to the export.  Do the export (as a CSV).
40 6 Jon Goldberg
41 3 Jon Goldberg
h3. Constituent Codes
42 15 Jon Goldberg
43 19 Jon Goldberg
In RE: Found at the bottom of the "Bio 2" tab.
44
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.
45 3 Jon Goldberg
46 1 Jon Goldberg
Export as _one to many_, below.
47 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.
48
49
No need to export these fields:
50
System Record ID
51
Import ID
52 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.
53 6 Jon Goldberg
54
h3. Solicit Codes
55
56
Export as _one to many_, below.
57
These can map to groups - but also may map to privacy preferences or custom fields (e.g. Email Only, Do Not Solicit)
58
Export the "Solicit Code" only (along with the Constituent's System Record ID, of course).
59 3 Jon Goldberg
60 20 Jon Goldberg
h3. Addresses
61
62
SQL tables: ADDRESS, CONSTIT_ADDRESS
63
64
Addresses are a many-to-many relationship in RE.
65 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.
66 20 Jon Goldberg
67 25 Jon Goldberg
h3. Phones/E-mail/websites
68 1 Jon Goldberg
69 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.
70 25 Jon Goldberg
71 26 Jon Goldberg
Notes:
72
* You can NOT have duplicate phone types in RE, so no need to try and catch multiple "Home" numbers!
73
* Oh - except that one contact can have two home phone numbers on two different addresses.
74
* Don't forget to filter out duplicate numbers/e-mails/etc. when someone puts the same phone number on two different addresses.
75 22 Jon Goldberg
76
This SQL gets me a useful list of phones and e-mail for further processing in Kettle:
77
<pre>
78
SELECT DISTINCT
79
  CONSTITADDRESSID
80
, CONSTIT_ID
81
, PHONETYPEID
82
, CONSTIT_ADDRESS_PHONES."SEQUENCE"
83
, NUM
84
, DO_NOT_CALL
85
, TEXT_MSG
86
FROM CONSTIT_ADDRESS_PHONES
87
LEFT JOIN PHONES ON CONSTIT_ADDRESS_PHONES.PHONESID = PHONES.PHONESID
88
LEFT JOIN CONSTIT_ADDRESS ON CONSTITADDRESSID = CONSTIT_ADDRESS.ID
89
</pre>
90
91 9 Jon Goldberg
h3. Relationships
92
93 29 Jon Goldberg
Relevant SQL table: CONSTIT_RELATIONSHIPS
94
95 9 Jon Goldberg
Relationships are different in Civi and RE in the following significant ways:
96
* Relationships don't have to have a relationship type.
97 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".
98 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.
99 30 Jon Goldberg
* There need not be a relationship type at all.  This doesn't make sense, except that:
100
* There are hardcoded fields for IS_SPOUSE, HON_MEM_ACKNOWLEDGE, IS_HEADOFHOUSEHOLD, and SOFTCREDIT_GIFTS.
101
102 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:
103
* Look up the RELATIONSHIP_TYPE against the @name_b_a@ field in @civicrm_relationship_type@.
104
* Look up the RECIP_RELATIONSHIP_TYPE against both @name_a_b@ and @name_b_a@ in @civicrm_relationship_type@.
105 9 Jon Goldberg
106 53 Jon Goldberg
h3. Solicitor Relationships
107
108
Solicitor relationships are stored in a different table.  I used this SQL to extract them:
109
<pre>
110
SELECT
111
CONSTIT_ID
112
, SOLICITOR_ID
113
, TABLEENTRIES.LONGDESCRIPTION as solicitor_type
114
, AMOUNT
115
, NOTES
116
, cs."SEQUENCE" as weight
117
FROM CONSTIT_SOLICITORS cs
118
LEFT JOIN TABLEENTRIES ON cs.SOLICITOR_TYPE = TABLEENTRIES.TABLEENTRIESID
119
ORDER BY weight
120
</pre>
121
122 10 Jon Goldberg
h3. Attributes
123
124
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.
125
126 11 Jon Goldberg
Valuable information about the setup of the attributes is available in RE from *Config > Attributes*.
127 1 Jon Goldberg
128 33 Jon Goldberg
* The analogous field to @civicrm_custom_field@ is @AttributeTypes@.
129
* @AttributeTypes.CODETABLESID@ gives a lookup for the RE "option group" that contains valid options for that attribute.
130 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.
131
 
132 33 Jon Goldberg
Here's my preliminary SQL to export attributes from RE:
133
<pre>
134
SELECT
135 36 Jon Goldberg
ca.PARENTID as external_identifier
136
, ca.ATTRIBUTETYPESID
137
, at.DESCRIPTION as Category
138
, TABLEENTRIES.LONGDESCRIPTION as Description
139 33 Jon Goldberg
, TEXT
140
, NUM
141
, DATETIME
142
, CURRENCY
143
, "BOOLEAN"
144 35 Jon Goldberg
, COMMENTS
145 33 Jon Goldberg
, ca.ATTRIBUTEDATE
146
FROM ConstituentAttributes ca
147
JOIN AttributeTypes at ON ca.ATTRIBUTETYPESID = at.ATTRIBUTETYPESID
148 36 Jon Goldberg
LEFT JOIN TABLEENTRIES ON ca.TABLEENTRIESID = TABLEENTRIES.TABLEENTRIESID
149 33 Jon Goldberg
</pre>
150
151 34 Jon Goldberg
*note:*  In the SQL above, "PARENTID" and not "ConstitID" is the correct foreign key to link this to the contact.
152
153 38 Jon Goldberg
To get a list of option values out of RE for the attributes, use this SQL:
154
<pre>
155
SELECT
156
DESCRIPTION
157
, at.CODETABLESID
158
, LONGDESCRIPTION
159
FROM TABLEENTRIES te 
160
LEFT JOIN AttributeTypes at ON te.CODETABLESID = at.CODETABLESID
161
ORDER BY DESCRIPTION
162
</pre>
163
164 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:
165
<pre>
166
SELECT ATTRIBUTETYPESID, PARENTID, COUNT(LONGDESCRIPTION)
167
FROM ConstituentAttributes ca
168
JOIN TABLEENTRIES te ON ca.TABLEENTRIESID = te.TABLEENTRIESID
169
GROUP BY PARENTID, ATTRIBUTETYPESID
170
HAVING COUNT(LONGDESCRIPTION) > 1
171
ORDER BY ATTRIBUTETYPESID
172
</pre>
173
174 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.
175 10 Jon Goldberg
176 49 Jon Goldberg
h3. Salutations/addressee info
177 48 Jon Goldberg
178 49 Jon Goldberg
RE stores contact salutations and addressee info in two places.
179 48 Jon Goldberg
180 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@.
181 1 Jon Goldberg
182 49 Jon Goldberg
An unlimited number of non-primary salutations can be stored in the @CONSTITUENT_SALUTATION@ table.
183 48 Jon Goldberg
184 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".
185
186
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.
187 48 Jon Goldberg
188 3 Jon Goldberg
h3. Other constituent tables:
189 5 Jon Goldberg
190 2 Jon Goldberg
Skip these tables:
191
* Spouse
192
* Gifts
193
* First Gift, Last gift, Largest Gift
194
* Actions
195 1 Jon Goldberg
* First Action, Last Action
196
* Summary Information
197
198 41 Jon Goldberg
h2. Contribution-related exports
199
200
h3. Contributions/Gifts
201
202
Contributions (in RE parlance: Gifts) are complicated beasts!
203
204
Here are some relevant database tables and their equivalent in Civi:
205
GIFT	civicrm_contribution
206
GiftSplit	civicrm_line_item
207
CAMPAIGN	Roughly maps to Campaign.  Your mapping may vary and/or include custom fields.
208
APPEAL	Also roughly maps to Campaign (or Source).  Your mapping may vary and/or include custom fields.
209
FUND	Roughly maps to Financial Type.  Your mapping may vary and/or include custom fields.
210
211
Note that gift type is hardcoded into a function called "TranslateGiftType) - so you may want to include that function in your SQL, e.g.:
212
<pre>
213
SELECT
214
gs.GiftId
215
, g.CONSTIT_ID
216
, gs.Amount
217
, g.DTE as gift_date
218
, FUND.DESCRIPTION as fund
219
, CAMPAIGN.DESCRIPTION as campaign
220
, APPEAL.DESCRIPTION as appeal
221
, g.PAYMENT_TYPE
222
, g.ACKNOWLEDGE_FLAG
223
, g.CHECK_NUMBER
224
, g.CHECK_DATE
225
, g.BATCH_NUMBER
226
, g.ANONYMOUS
227
, gst.LONGDESCRIPTION as giftsubtype
228
, g.TYPE
229
, DBO.TranslateGiftType(g.TYPE) as type2
230
FROM GiftSplit gs
231
LEFT JOIN FUND on gs.FundId = FUND.id
232
LEFT JOIN APPEAL on gs.AppealId = APPEAL.id
233
LEFT JOIN CAMPAIGN on gs.CampaignId = CAMPAIGN.id 
234
LEFT JOIN GIFT g on gs.GiftId = g.ID
235
LEFT JOIN TABLEENTRIES gst on g.GIFTSUBTYPE = gst.TABLEENTRIESID
236
</pre>
237
238 42 Jon Goldberg
Payment Type is also hard-coded, it seems:
239
1	Cash
240
2	Personal Check
241
3	Business Check
242
4	Credit Card
243
6	Direct Debit
244
8	Other
245
246
h3. Soft Credits
247
248
Stored in GIFTSOFTCREDIT.  RE does NOT have the concept of a soft credit type - which is fine.
249
<pre>
250
SELECT
251
, GiftId
252
, ConstitId
253
, Amount
254
, 'Soft Credit' as soft_credit_type
255
FROM GiftSoftCredit
256
</pre>
257
258 52 Jon Goldberg
h3. Solicitor, Gift
259
260
(Important!  Gift solicitors are different from Contact Solicitors)
261 42 Jon Goldberg
262
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:
263
<pre>
264
SELECT
265
ParentId as gift_id
266
, SolicitorId as soft_creditee_external_identifier
267
, Amount
268
, 'Solicitor' as soft_credit_type
269
FROM GiftSolicitor
270
</pre>
271
272 41 Jon Goldberg
h3. In Honor/Memorial Of (aka Tributes)
273
274
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.  
275
276
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.
277
278
<pre>
279
SELECT
280
gt.GIFT_ID
281
, gt.TRIBUTE_TYPE
282
, t.DESCRIPTION
283
, t.RECORDS_ID as tributee_extenal_identifier
284
, te.LONGDESCRIPTION as tribute_type
285
FROM GIFT_TRIBUTE gt
286
JOIN TRIBUTE t ON gt.TRIBUTE_ID = t.ID
287
LEFT JOIN TABLEENTRIES te on gt.TRIBUTE_TYPE = te.TABLEENTRIESID
288
</pre>
289
290 51 Jon Goldberg
h2. Actions
291
292
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:
293
<pre>
294
SELECT
295
  a.ADDED_BY
296
, a.AUTO_REMIND
297
, a.RECORDS_ID as external_identifier
298
, cr.RELATION_ID as action_contact_id
299
, a.DTE as activity_date_time
300
, LETTER.LONGDESCRIPTION as letter
301
, a.PRIORITY as priority_id
302
, a.REMIND_VALUE
303
, a.CATEGORY
304
, a.Completed
305
, a.COMPLETED_DATE
306
, a.FUND_ID
307
, a.FOLLOWUPTO_ID
308
, a.TRACKACTION_ID
309
, a.PhoneNumber as phone_number
310
, a.Remind_Frequency
311
, a.WORDDOCNAME
312
, a.APPEAL_ID
313
, a.APPEAL_LETTER_CODE
314
, a.OUTLOOK_EMAIL_SUBJECT
315
, STATUS.LONGDESCRIPTION as status
316
, TYPE.LONGDESCRIPTION as type
317
, LOCATION.LONGDESCRIPTION as location
318
, ActionNotepad.ActualNotes
319
, CAMPAIGN.DESCRIPTION as campaign
320
FROM ACTIONS a
321
LEFT JOIN TABLEENTRIES as STATUS ON a.STATUS = STATUS.TABLEENTRIESID
322
LEFT JOIN TABLEENTRIES as TYPE ON a.[TYPE] = [TYPE].TABLEENTRIESID
323
LEFT JOIN TABLEENTRIES as LOCATION ON a.[Location] = LOCATION.TABLEENTRIESID 
324
LEFT JOIN TABLEENTRIES as LETTER on a.[LETTER_CODE] = LETTER.TABLEENTRIESID
325
LEFT JOIN ActionNotepad ON a.ID = ActionNotepad.NotesId
326
LEFT JOIN CAMPAIGN on a.CAMPAIGN_ID = CAMPAIGN.id
327
LEFT JOIN CONSTIT_RELATIONSHIPS cr on a.CONTACT_ID = cr.ID
328
</pre>
329
330
"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.
331
332 54 Jon Goldberg
h2. Events
333
334
Events are stored fairly similarly to CiviCRM, except:
335
* locations are stored on the event record itself (which I'm not dealing with).
336
* There's fields for storing data about classes.  I haven't delved into this - I suspect that this may tie into recurring events.
337
* "Event Category" and "Event Type" might both map to Civi's "Event Type".  This isn't the case for me.
338
* 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.
339
340
Here's some SQL to pull in the most relevant data:
341
<pre>
342
SELECT
343
  se.CAPACITY
344
, se.END_DATE
345
, se.ID
346
, se.NAME
347
, se.START_DATE
348
, se.DATE_ADDED
349
, te.LONGDESCRIPTION as activity_type
350
, se.INACTIVE
351
, se.DISPLAYONCALENDAR
352
, CAMPAIGN.DESCRIPTION as campaign
353
, se.DESCRIPTION
354
FROM SPECIAL_EVENT se
355
LEFT JOIN CAMPAIGN on se.CAMPAIGN_ID = CAMPAIGN.id 
356
LEFT JOIN TABLEENTRIES te ON se.TYPEID = te.TABLEENTRIESID
357
</pre>
358
359 45 Jon Goldberg
h2. Notes
360
361
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). 
362
363
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.
364
365
Once I revisit the "Media" data, I may figure out a way to extract photos, which I could then add to notes as attachments.
366
367 46 Jon Goldberg
Here's the SQL I'm currently using to extract notes before doing transforms in Kettle:
368
<pre>
369
SELECT
370
Title as subject
371
, Description
372
, Author
373
, ActualNotes
374
, ParentId
375
, cn.DateChanged
376
, LONGDESCRIPTION as NoteType
377
FROM ConstituentNotepad cn
378
LEFT JOIN TABLEENTRIES ON NoteTypeId = TABLEENTRIESID
379
</pre>
380
381 43 Jon Goldberg
h2. And More
382
383 7 Jon Goldberg
h3. Tables that Civi doesn't have a direct counterpart for
384 5 Jon Goldberg
385 3 Jon Goldberg
* Aliases (stores Maiden Name and d/b/a - unsure how to import into Civi just yet)
386 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.
387 2 Jon Goldberg
388
389
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.
390
391 1 Jon Goldberg
392
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.
393
394
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.
395
396
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.
397
398
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.
399
400
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".
401
402
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.
403
404
NOTE: Letters sent is incomplete, there's more than 12 letters to some folks!
405
406
GIFTS is related to constituent on the last column (Constituent System Record ID)
407 8 Jon Goldberg
408 13 Jon Goldberg
h3. Code Tables/Option Groups/Option Values
409
410 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).
411
412
h2. Deciphering MS SQL
413
414
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.
415
416
h3. Looking Up Functions
417
418
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.
419
420
h3. Looking Up Stored Procedures
421
422 18 Jon Goldberg
If, in the profiler, taking a certain action shows a command like this:
423 17 Jon Goldberg
These have a syntax like:
424 1 Jon Goldberg
<pre>
425 18 Jon Goldberg
exec sp_execute 48,43,'Acknowledgee'
426 1 Jon Goldberg
</pre>
427
428 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:
429
<pre>
430
declare @p1 int
431
set @p1=48
432
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'
433
select @p1
434
</pre>
435
436 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.
437 17 Jon Goldberg
438 13 Jon Goldberg
439 14 Jon Goldberg
h3. Addressee/Postal Greeting/E-mail greeting
440
441
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.
442
443 8 Jon Goldberg
See also:
444
http://support.littlegreenlight.com/kb/migration/migrating-from-the-raisers-edge-to-lgl
445 14 Jon Goldberg
446
h3. Things I see that RE does better than Civi:
447
448
* 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.
449
* 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.
450 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