Export Addressee problems

Options
I'm trying to create a format in the Addressee configuration that will allow me to select a couple with the same last name but differentiate if the couple doesn't use the same last name?  I'm doing an export and am not sure how I can get this achieved?


Example:  


Two couples:  Jeff and Jane Johnson, and Mark Smith and Maddie Jones


My export is exporting as Mark and Maddie Smith instead of Mark Smith and Maddie Jones, but I still want Jeff and Jane Johnson to export in that format (not Jeff Johnson and Jane Johnson)


Any ideas?


Julie

 
Tagged:

Comments

  • JoAnn Strommen
    JoAnn Strommen ✭✭✭✭✭
    Ancient Membership Facilitator 4 Name Dropper Photogenic
    As Cathleen Mai‍ and Melissa Graves‍ said. Not sure how experienced in RE you are, Julie, so will add our example. I have addressee configuration called "Joint - Infomal". For Jeff and Jane Johnson's record I chose the "canned" format that includes William & Margaret Smith. On Mark Smith's record I choose that same addressee but then from my list of formats choose the one I created for records with different last names. On my table of addressee formats it looks like William Smith & Margaret Adams.  (Check in Spouse box required for both.)


    In my export/query or report, I choose that specific addressee "Joint-Informal" and it will pull in the format specified on the RE record.
  • Since I'm knee-deep in fixing up these things in my own database, and everyone else has talked about using the Addressee Configuration, here are the steps I used to globally fix some of the "Mrs. and Mr." problems we had. Depending on the number of records you have or that you need to have the combined addressee for, this may be a lot of extra work, or it may save you a lot of time. I suppose it also depends on your comfort with export/import and Excel formulas. :)


    First is to make sure you have the configurations you want to use, and you have created a name for the Addressee/Salutation format that can be selected on the Add/Sal tab in a constituent record. Call it "Couple Addressee" or whatever works for you. My steps assume that it matters what the titles are, so it would be "Formal Couple Addressee". When I export, I use addressee processing to pull the Formal Couple Addresee if it exists. If it doesn't exist, I use the Primary Addressee.


    1. Export your constituents with ID, Title1, Last Name, Gender, Spouse ID, Spouse Title1, Spouse Last Name, Spouse Gender

    2. In your new excel file, create a new column to check if Last Name and Last Name are the same

    2a. Yes? Label these A.

    2b. No? Label these B.

    3. Identify the Addressee ID that you want for the A group or the B group, Const First or Spouse First (Go into Config, Add/Sal, then right-click and get the report to PDF) Copy these ID numbers out and label them so you can easily know which one you're referencing (I have a little notecard next to my monitor, and I used colored markers to visually group the Spouse First vs. Constituent First sets)

    4. Add a new column to find out which Add/Sal you want.

    4a. GROUP A: If Gender = M and Spouse Gender = F, then ID#A/Const First. If F and M, then ID#A/Spouse First. If both M or both F or neither, you pick

    4b. GROUP B: If Gender = M and Spouse Gender = F, then ID#B/Const First. If F and M, then ID#B/Spouse First. If both M or both F or neither, you pick

    5. Add another new column. Vlookup using the spouse ID to make sure if one clumn is the Const First version, the other is Spouse First. This way you can deduplicate even if you aren't using Head of Household processing or it goes wrong. If you don't have any constituent spouses or you don't care, you can skip this step.

    6. If you care about titles, for GROUP A, add another new column to Concatenate Titl1 and Spouse Titl1. Look for any "Mr. and Dr.", "Dr. and Dr.", "Dr. and Reverend", etc. - anything that shouldn't have the standard Mr. and Mrs. Last Name format. Manually select a name format ID that matches how you want to handle them. Make sure if there is a constituent spouse, the spouse's ID matches/is in the opposite order.

    7. You may want to do the same thing for GROUP B, if you are always listing Dr., Rev., etc before Mr/Mrs.


    Now, if all the IDs for constituent spouses match up and the other IDs are assigned properly, you can use the Add/Sal ID column to create an import and set the new Couple Addresee name in bulk. All you need is the Constituent ID, Addressee/Salutation Type, and Addressee/Salutation ID.


    You can also create several Default Sets for addressee/salutation types that will automatically load the Format + ID onto a specific record. You can make each Set match up to a different combination, then just select the Default Set you want to use while creating or updating a record. Once you've done the global import, this is the best way I know of to quickly assign a lot of addressee/salutation formats to any one record.


    I prefer the import process to Global Add or Global Change, because it lets me do several different combinations all at once, instead of multiple Global Changes for each query. It also allows me to override in any situations where someone really shouldn't use one of the defaults, since I can export additional information that might be useful to review.


    I really hope you or someone else finds this useful! I've been trying to fix problems like this with names for months now, and to simplify some of the weirder configurations people created in the past, and it was a lot of touch-and-go until I ultimately decided to do an export like this and let the Excel formulas do most of the work.


    (Update: I created an excel file to sort of show you what I mean. It's very basic and doesn't take into consideration MM or FF pairs. Yellow columns are the ones to import back into RE. Columns A-H are the export. Red columns are the formulas I used to identify the addressee/salutation ID. If I were actually processing this, I would have a few more helper columns to look for non-MF pairs and some more calculations to automatically select the AddSalID for me.)

Categories