I'm new to RE and my organization.  Please forgive me if this is so fundamental.

We are a school and when we write our queries, sometimes both members of a couple are constituents and  are in the query.  Or perhaps only one member meets the criteria.  I want the salutation and addressee processing to be smart enough on our reports or exports so that:

If both husband and wife/partner are in the selection, use couple salutation and addressee, and print only one row.

If only one member of the couple is in the selection/export, print the individual salutation/addressee.

My understanding is that we currently handle this manually, by editing the export file.  I want to automate the process.

Thanks,

Dwight

Dwight Seuser:

I'm new to RE and my organization.  Please forgive me if this is so fundamental.

We are a school and when we write our queries, sometimes both members of a couple are constituents and  are in the query.  Or perhaps only one member meets the criteria.  I want the salutation and addressee processing to be smart enough on our reports or exports so that:

If both husband and wife/partner are in the selection, use couple salutation and addressee, and print only one row.

If only one member of the couple is in the selection/export, print the individual salutation/addressee.

My understanding is that we currently handle this manually, by editing the export file.  I want to automate the process.

If only the software were smart enough!!!

This may help - some work to set up but it might easier than repeatedly manually cleaning an export file.  My idea would be to create a new addressee type and salutation type.  Say you name them "school mailing add" / "school mailing sal".  When you create the addressee, format it to include constituent and spouse when there is spouse, smart feature.  You can populate this globally through a global change.  (One glitch might be if you have records where spouse is a relationship but you don't want spouse included in mailing.)  You may need to do several queries to run global change, one where both husband and wife are in selection, one where only one in export.  Do same process to set up salutation.

In export, leave the send to head of household box checked. When you have the addressee and saluation set, in export output select 'addressee/salutation' > 'addressee'. After they appear in output box, select 'format' at bottom of window or right click and select 'format'  Choose 'from individual' and in drop down list of addressee types choose your "school mailing add".  Do same process to add salutation to output and format it. 

I'm thinking this should work - I haven't tried it.  Just depends on how often you need the export to decide which is less work. Hope this helps or sparks an idea for a help from someone else.

Wouldn't the proposed solution of globally formatting a new addressee/salutation type need to be done for each mailing?  Sometimes the spouse would be included, sometimes they wouldn't be included so wouldn't you need to do this again for each query?

I've asked this question of Blackbaud before and been told there is no way for the system to automatically handle this type of spouse processing.   So I don't see any way to make this work in an automated fashion other than to manipulate the final export file.  I would love for Blackbaud to include this as a processing feature since it's a very common scenario we all run into.

Here's how I've been doing this manually with exported data.

1) I export the constituent's RE ID into column 1 and their spouse RE ID into column 2 (along with all the other address data and the single and joint addressee/salutations).  Then create a blank column 3.

2) Then in column 3 I enter a lookup formula that looks in the values in column 2 to see if these IDs are anywhere in column 1 and then places a code in column 3.  This will find any spouses that are also included in the query criteria.

3) I then do a copy/paste special=values in column 3 and can sort the spreadsheet on the column 3 values. This will put all the constituents w/spouses included at the top, and you can then manually move over the joint addressee/salutation for these records.

The only issue I've found with this is if you have any of these "joint" constituents with a mailing address of business then you would be mailing to them jointly at a business address which is probably not appropriate.  So for this group I have to manually check the list for business addresses, check their records to see if there is a home address that can be used instead, and manually replace the address. If there is no home address, then I revert the addressee/salutation back to the single constituent.

This is how I do it. As mentionned, there is no way to automate the process but I find it fairly begnign once you are used to it, even if it is to be redone at each mailing.

 You need to export twice, with two different queries.  Query 1 = Single individuals  Export 1 = Primary Addressee/Sal   Query 2 = Individuals where both spouses meet the criteria and are included in the mailing  Export 2 = Head of Household, Additional Addressee/Sal (a "spouse" or "joint" addresee)

To know which couple are included,  I temporarily code with a solicitation code.  Let say you mail to all people with a summary total of above $500.  Globally add a temporary solicitation code of "Mailing" for them.  Then create a query where Spouse Solicitation code = Mailing AND Individual also equal MAILING.  Use with Export 2.

This assume you have a permanent "joint" salutation available on each record where there is a spouse.  Good luck.

We're moving to an automated solution whereby we record our Alumni's mailing preferences as "joint" or "single" *per* type of mail we send out. So, for example, we might send out our alumni magazine jointly addressed, but might wish to send out mailings relating to a graduate's subject of study or class to a single addressee/salutation.

 We'll be using the memberships module to record each mailing (i.e. a membership per magazine/newsletter) and setting up a membership attribute of "joint" if a joint mailing.

Where a couple wish to receive joint mailings, the primary partner has the membership (mailing) recorded on their record with the attribute "joint".  The primary partner then has two addressees (single and joint) and two salutations (single and joint).  We will process the export via a Crystal Report, which will allow us to select the correct addressee/salutation based on the membership attribute.

General mailings would follow the pattern of the main magazine mailing (i.e. joint or single).  If we created a query to pull out, for example, by class or subject of study, we could perform two queries - one to pull out couples who match by subject or year, and one overall query (then just subtract query 1 from 2 before exporting).  Two exports then solve the addressee/salutation problem - one exporting joint addressees, and one single addressees.

Hope that makes sense!  I've used that method successfully at a charity I worked for previously.

Jon

p.s. if you don't have the membership module, you could do this equally as well using Attributes for your mailings/mailing preferences.

p.s.  I like the idea of just creating an Addressee/Salutation per mailing (someone suggested that on the Forum) too...the only advantage the membership module has is that it will record a history of when/whether the constituent received mailings...and could also be used to record if multiple copies are required etc (again, through an attribute).

There are so many ways to approach this issue, and like anything else it depends on your type of organization, your type of records and the most common scenarios that you run into.

What I don't like about these special "coding" approaches is how are they maintained? What if the couple divorces or one of them deceases? Do you then have to go into the record and know all the places you have to make adjustments?

I've always tried to take as systematic and low-maintenance approach as possible. For your mailing, is there an across-the-board decision you can make for your mailing that says: for this specific mailing "If they are married, send to them jointly" or "Send to everyone singly whether or not they are married or not."  If you can do that, that solves a lot of issues.

Sometimes there are complications for alumni or membership situations -- but you can make a single attribute that identifies them as a "2-alum household" or "2-member household" that you can then use operationally in your mailing processing.  That to me is easier to maintain then starting to code for each type of mailing.

To handle multiple scenarios, I now work from a standard export that outputs all sorts of extra information, and I can do just about anything with it.  I export out the spouse import ID and constituent ID and in Excel use vlookup commands to find if the spouse of someone also met the main criteria on the list.  I also export out the single and joint versions of addresse/salutations so based on what I want to do, I can choose to use single or joint salutations for different data.  Anyway, that's what's working the best for me now and gives me the most flexibility, without having to put permanent coding on any of the records.

 Just one other approach...

Gina Gerhard