Individual Relationship Addressee/salutation clean up - 20k+ records!!

I did this project last year at my previous organization.


It was actually surprisingly easy to do the individual relationships! I thought it was going to be a nightmare, but in the end, I was able to use a combination of Import, queries, and Global Change to do what I needed to. I was focused on non-constituent individual relationships that pulled as Primary Individual Relationship or Organizational Contact, but the process was the same for all of them.


So here's a very long detailed explanation of what you need to do. Laura, you might already have gone through this or have chosen different processes, but I want to leave something for future us.

 

---

GATHER YOUR RESOURCES/PREP YOUR DATA

1. Start by plotting out what add/sal formats you want to use, which ones in the system are redundant or bad data, and what your policies are regarding everything around names.
2. Go into your Add/Sal Config and identify any name formats that do NOT fit your scheme/policies.
   • If the format is close to one you want but don't have, leave it for now.
   • If it's not close or is nearly the same as another, try to delete it. (If you converted all your suffixes to use commas, for example, you might have redundant formats that insert an extra comma)
   • If it deletes: awesome, easy!
   • If it doesn't delete, take note - there's a record somewhere using this format and you will have to remove/change it on the record. (Could be a relationship record, event name tag/place setting, membership name on card, and at least one other I never figured out.)
3. Change any "close but not quite" formats to be what you need and add new formats if necessary for your policies.
4. While you're in the Add/Sal Config, export your name formats. The numbers match up to when the format was added to Raiser's Edge (or it's the original default order for RE)
   • The PDF report is massive and shows you exactly what settings are used for each format. I found this useful for puzzling out exactly why one seemingly identical formats existed (we had many formats duplicated with/without the auto comma!)
   • Exporting the Add/Sal Grid to CSV will give you a nice, clean list of just the format and the ID.
   • Make a note of the formats you want to use as the defaults. Get a post-it note or big whiteboard or something and write the defaults' purpose with the ID.
     • I found "Informal - Individual - Nickname - 67" was more useful than "Bill Smith - 67", because with this many updates, you're working with data regex, not unique individuals.
     • Include whatever makes this format unique - "Formal - First initial Middle Name - With Spouse - 50" might be "Mr. and Mrs. W. Henry Smith"
     • I used half pages of cardstock and color coded my defaults, too. This let me stick them under my monitor for quick reference/typing, and the color coding made it easier to find With Spouse or Formal/Title at a glance.
     • I also created a reference matrix for what the default forms would look like for different name data. If we didn't have a title, or if their spouse had a different name, etc. This was both to match the final Add/Sal tab to the ID list and to trouble-shoot later if something didn't look right.
5. Run some audit queries to identify things like records that have bad data or the fields aren't complete enough for your formats. Fix these or give them an attribute to weed them out.
   • I had decided that if someone only had a First Initial for their first name field, I would merge it with the middle name and leave the middle name blank. I made that change in this step. This helped reduce the number of records using the secondary default.
6. Set up some queries to segment your records to speed up the processing time. If some records aren't receiving all the name formats, go ahead and split them out. For example, if you're doing constituents as well as relationships, you might choose to segment out constituents who do not have spouses. These will be your default queries.
   • In your constituent query output, you will need:
     • Constituent Import ID
     • Relationship Import ID
     • Relationship Name Fields (all of them - title, suffix, nickname, everything)
     • Relationship Add/Sal Fields
       • Primary Addressee Text
       • Primary Addressee is Editable?
       • Primary Salutation Text
       • Primary Salutation is editable?
       • Specific Addressee/Salutation Text
       • Specific Add/Sal Import ID
       • Specific Add/Sal Editable
   • You may find it useful to include the constituent's name and ID as well, for cross-checking or if there's spouse names involved.
   • I found that sometimes relationships would show up on a relationship query, but not on a constituent query. I started a topic about it at one point, but I don't want to search it out now. Suffice that you may want to do both to check if the counts are different. Ultimately, I used Individual Relationship queries for Global Change and Constituent Queries for my export/import/cross-check needs.
   • Your specific queries may vary according to your clean-up and segmentation.
     • I used the following criteria in different combinations:
     • NO add/sal set (everything is blank)
     • ONLY PRIMARY add/sal are set
     • Primary and additional add/sal are present but NONE are Editable = Yes
     • The add/sal that I want to change in this round is already set correctly. (I'll explain how to do this one.)
     • Individual is a spouse, constituent, or contact
     • Individual has/does not have specific name fields (no middle name, first name is an initial only, etc.)
     • Attribute flags for this record is a hopeless mess or was manually changed and not to mess with it again
   • You may find that it's impossible to properly query for a particular name format to verify whether it's in use. This is where you temporarily modify your Add/Sal configuration to include unique identifiers at the beginning of each format. I found that sometimes it was easy to do one at a time, and at others to group multiples with the same prefix - it really depends on the number and the purpose. In your query, you will use the criteria "Addressee/Salutation text BEGINS WITH or CONTAINS" to find the unique identifier.

CLEAN THE EASY STUFF

Now that you've got the prep work done, you can start making changes. If you have a lot of them marked "editable", you might want to go to the next section first. (I noticed that sometimes the global changes or Config format change would make changes to the ID, but still be overwritten by "editable". I didn't pay enough attention to see what happened specifically, but there were times that the underlying format gave me clues about why someone decided it needed to be editable - often because there was an extra comma, or a nickname wasn't showing up properly.)

1. Add the correct add/sals to all records who do not have them. Use a segmented query and Global Change (Add). Or, include "is blank" with the "is wrong" groups for the following steps.
2. Make sure to use an Individual Relationships query for relationships and Constituent query for constituents in this step.
3. Update your Primary Addressee and Salutation
   • Query: Primary Addressee = specific name format that is wrong, perhaps include anything else about the name fields that would influence which format is correct
   • Global Change: Replace Primary Addressee name format with the correct format
   • If you have a big group of Wrong Format that all get changed to the same format, you could use Add and overwrite the data. I preferred to do smaller chunks to prevent massive errors and also to save processing time.
4. Find Add/Sals that are easy to update
   • Query: only one Add/Sal type at a time, be sure to use whatever criteria you need to get the right segment
   • Global Change: ADD Add/Sal, being sure to Overwrite Value (this is NOT a replace option, so you will need to Overwrite existing values)

THE MORE COMPLICATED ONES

Once you've done the above, you should be left with your Editables and oddballs that are too difficult to identify via query only. You're gonna have to break out the Excel and Import, sorry. This section is why I suggested creating a chart of your IDs and their purpose.

1. Export the query of the segment you want to work with (see my list above of the bare minimum output). I use a constituent query for this part.
2. You will now set up excel formulas to identify whether the editable name format matches any of the formats. If so, create an import using these rows: Editable = No, ID = the correct ID (you will use the Add/Sal Import ID for this)
   • IF [Editable Add/Sal] = first character of [First Name] + . + [ ]  + [Middle Name] + [Last Name] + [Suffix] THEN assign format ID 58
   • You may need to create a lot of different formulas. I had a few basic concatenations in a helper column and then used nested IFs to identify the correct format ID.
   • I had multiple columns for these formulas so that I could run all of the Primary and custom add/sals in one import.
   • I also used a lot of sorting and filtering on the table to narrow down any editable names that I could not match. (Often women who remarried and changed their names!)
   • Anything that was too tricky, I opened in RE and handled manually or flagged it for a manual review.
3. If the Add/Sal type is NOT blank and you want to get rid of it:
   • Create a new Add/Sal type called DELETEME (you may need multiple! you can only have one instance of an Add/Sal Type per record)
   • When importing the data, add the column for Add/Sal type = DELETEME using the Add/Sal ImportID of the original.
   • Use Global Change to delete the format DELETEME from all records
   • Delete the line from your Add/Sal Types Table
4. When it comes to constituents and spouses, I always exported all the name fields for BOTH and ran the excel concatenate+match formulas on them. I specifically did not use HOH or gender to determine the name formats for this project, but if that were relevant, I would have been sure to include those fields in the output to put into my Excel formulas.
5. Import your cleaned up data back into RE
   • If you're not importing everything all at once, make sure you're splitting the file!
   • Also don't forget to convert your formulas to text!
   • It helps to label your columns to match the import fields. ImportID, IRImportID, IRPrimAdd, etc. There's a massive PDF with tables of import information that I downloaded from the RE Support site to refer to, to save time.
   • When importing, you will need to group the additional Add/Sals in the 3rd tab. The first group is 00, then 01, 02, etc. Labeling the columns helps speed this part up.

The reason these are more complicated is because you often have to scrutinize them more carefully to identify whether the Editable was because it was the only way someone could get what they needed (no editing rights on the fields), or if they just didn't know about the drop-down.


Once you've got all the records fixed up, you can try deleting the extra Types or Formats from the respective Config sections. If they don't delete, there's some stray data somewhere. You may need to go into Events and Memberships. This is a lot more complicated than relationships, so I hope you don't need to. I never did get around to fixing the membership names on cards before leaving the org, but it looked like it was going to be a lot of manual edits.

 

---

Most of the work here is in the prep, research, and getting the import file configured. But once you do that, it's straightforward.


Good luck!


(edited because in review I realized I forgot to include the step to add new Add/Sal formats if you needed new ones! kind of important not to forget...)