nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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

Laura Quist

Hi all,

I'm looking for some advice on updating/cleaning up additional addressee/salutation fields on individual relationship records. I am doing great in the general clean up, but now we're into the hard stuff. I have 20k + relationship records that have at least 6 additional add/sal fields,most of which are editable. From what I can see, there is no good way to globally update or even use the import function to change this directly;I don't see that those specific add/sal fields are listed in the fields that can be changed through those tools. My thought is that i may have to actually replace the relationship records, as in, add the relationships again without the additional fields, and then delete all of the original relationship records. This feels scary/risky to me, but I don't see any other way to do it except go into every single one of those records and change in manually, which will take the rest of my life plus some of the next one!

Any thoughts on that idea, or other ideas? I'm SOO close to having the add/sal table and config under control, but it's these relationship records that are just out of my reach!

Find more posts tagged with

RaisersEdge7 Databaseview

Comments

JoAnn Strommen

Feel your pain. Edit box can be a friend but not very often.

If you add new relationships, you're going to have to/should look at existing add/sal to match in case something specific was requested. If I had to do it, I'd probably export all the needed add/sal info and look at importing it back in.

That said, how do you use the add/sal on relationships? I also was greatly concerned about it and set my procedures only to find that for individuals, we never used the fields at previous org. Six months in here and haven't used here either. Does your org send communication pieces addressed to the relationship person? I found the only ones I really needed to be concerned about were business contacts as we did use those. Just a thought.

Dariel Dixon

While it is difficult to import addressee data, you can import addressee format changes. I recently had to import these, which is more troublesome than necessary. You'll have to print out your addressee config table code list (or something like this, I forgot what it's called). Each addressee in your list has an addressee code number assigned to it, and to only place to find that number is in this list. The numbers are in a confusing order (assuming there was an order to them at all...mine were all over the place), however the codes should print out in the order of the addressee/salutation table. From there, the addressees can be updated to the corresponding code number.

For instance, you can import in an addressee number of 18 which may be [Title 1] and [Spouse Title 1] [First Name] [Middle Initial] [Last Name].

I didn't even have any idea that this code table report existed, but it was kinda silly that the report was the only place those code numbers could be found.

Laura Quist

JoAnn Strommen:

That said, how do you use the add/sal on relationships? I also was greatly concerned about it and set my procedures only to find that for individuals, we never used the fields at previous org. Six months in here and haven't used here either. Does your org send communication pieces addressed to the relationship person? I found the only ones I really needed to be concerned about were business contacts as we did use those. Just a thought.

That's a great point, and from what I can tell there is no specific rhyme or reason to the individual relationship addressees or salutations, even the primary. There are a few standards I will put in place for the the primary add and sal on relationship records once I've removed all of the extraneous add and sal fields, ex: when the constituent spouse is deceased I'd like to set a primary add and sal for the relationship record spouse with just the relationship records title and name, as the living spouse would become the recipient of any mailers.

Laura Quist

Dariel Dixon:

While it is difficult to import addressee data, you can import addressee format changes. I recently had to import these, which is more troublesome than necessary. You'll have to print out your addressee config table code list (or something like this, I forgot what it's called). Each addressee in your list has an addressee code number assigned to it, and to only place to find that number is in this list. The numbers are in a confusing order (assuming there was an order to them at all...mine were all over the place), however the codes should print out in the order of the addressee/salutation table. From there, the addressees can be updated to the corresponding code number.

For instance, you can import in an addressee number of 18 which may be [Title 1] and [Spouse Title 1] [First Name] [Middle Initial] [Last Name].

I didn't even have any idea that this code table report existed, but it was kinda silly that the report was the only place those code numbers could be found.

I found all of the above to be true! If you know of a way to update additional addressee and salutations fields for relationship records, let me know because I was able to do so for constituents but not relationships. I must be missing something?

Keri Barnhart

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

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.
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.)
Change any "close but not quite" formats to be what you need and add new formats if necessary for your policies.
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.
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.
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.)

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.
Make sure to use an Individual Relationships query for relationships and Constituent query for constituents in this step.
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.
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.

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.
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.
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
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.
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...)

Keri Barnhart

JoAnn Strommen:

That said, how do you use the add/sal on relationships? I also was greatly concerned about it and set my procedures only to find that for individuals, we never used the fields at previous org. Six months in here and haven't used here either. Does your org send communication pieces addressed to the relationship person? I found the only ones I really needed to be concerned about were business contacts as we did use those. Just a thought.

I have found over the years that putting a few basic Add/Sal Types on a relationship record helps out a bit if we ever convert them to constituents in the future. I also try to link non-constituent records together as much as possible, so a donor's sister might be the next contact for an organization. Setting the add/sals to a default set when creating the relationship saves some steps later.

But it sounds like Laura is of your mind and partly just wanted to delete the extra add/sal types, but wasn't sure how. Other folks at her org may have found the extra types useful for some reason or another, perhaps they used relationships for teams for events or something, and the add/sal formats were used because they didn't have the event module for nametags.

JoAnn Strommen

I couldn't agree more that it's best to put all data on a record when creating it. I did have a default set for individual relationships. Just not as many as records.

At previous org, where I found I was missing add/sal was for most all of the business contacts. Several lists were imported without the info and at that point it couldn't be done by global add. Don't know if it can now.

Laura Quist

Wonderfully detailed response, Kerri! It both confirmed a few things I've already done as well as gave some direction on what I'd like to do next. Thank you so much! I'm copying this into my procedures document.

JoAnn I will definitely be mindful of business contacts as I move forward. I'd want those records to have at least a primary addressee and salutation chosen.

Keri Barnhart

I'm glad you found it useful! I was working with 100,000 individual constituents and up to 7 additional add/sal types, so I made the decision not to bother with individual relationships unless they were contacts, spouses, or a VIP's VIP. We were converting the default from "Mr. William H. Smith, Jr." to just "William H. Smith, Jr." and there were so many "editable" on women that I had to hunt down to find out why. It turns out that the default RE add/sal formats for couples with different last names could use some tweaks, especially if you want to use nicknames/preferred names for both.

I can't say how frustrating it was when Citrix updated in the middle of the project and I could no longer use shift+F2 to load the default sets!!! There was a group of several hundred that I was reviewing one-by-one because they had too many editables and odd matching and I was using a small laptop, and instead of a Global Change to add the defaults, I was using default sets. On the other hand, this did lead to getting more efficient with the excel sheets on my tiny screen.

Alan French

Dariel Dixon:

Each addressee in your list has an addressee code number assigned to it, and to only place to find that number is in this list. The numbers are in a confusing order (assuming there was an order to them at all...mine were all over the place)

They seem to be numbered according to the order in which they were created - ours are all over the place too but the ones that survive from when we first moved to RE have lower numbers than ones I've created more recently.

Toni Baril

Keri,

Have you found a way to delete Additional Address/Salutations from Individual Relationships? I found where I can Add or Replace but not Delete. At my organization, someone added a Publication name to the Individual on an Organization instead of using the Org Name or an Alias for this purpose.

Thanks,

Toni