Sorting head of household and spouse so they appear next to each other

Diana Gray 2

I have been trying to create lists where both members of the household (head of household and spouse) appear next to each other.


Does anyone one have a solution? 


Thanks!

Diana

shani traum

Diana Gray:

I have been trying to create lists where both members of the household (head of household and spouse) appear next to each other.


Does anyone one have a solution? 


Thanks!

Diana

By "next to each other" I assume you mean on consecutive rows?


Sort by address? (Unless they have different preferred addresses)

 

JoAnn Strommen

What is purpose of list? Just for mailing or does it also need to be in alpha order? If order doesn't matter and you're dealing with a number of cases where different last names are used and to get the correct "Smith" next to spouse "Smith" I would sort list by address field.


Are you taking the list to Excel? If there are just some groups of names that need to be sorted, run list in alpha and then select all the Smith records and re-sort them by address.


Might help.

 

John Heizer

Diana Gray:

I have been trying to create lists where both members of the household (head of household and spouse) appear next to each other.


Does anyone one have a solution? 


Thanks!

Diana

It's very easy to do in a Constituent Export, just export the Constituent -> Name and Spouse -> Biographical -> Name. Include the "Sort Key" for the primary Constituent and Export to Excel.  Configure the Export to only include HoH.  You'll get one row for each HoH which you can then sort easily.


But since you seem to be struggling with this I have to assume that you're not using Export, so I need more information about what you mean by "list." 

Diana Gray 2

To be more clear (sorry I was a bit vague!), I
would like the rows to sort alphabetically by Head of
Household.  This means that spouses whose last name is not
the same as Head of Household will still be coupled with the Head
of Household by appearing on the row just beneath on a
spreadsheet.

 

Any suggestions?

 

Thanks again,

Diana

 

 

 

Diana Gray  
I 
Database Administrator

Flintridge Preparatory School

4543 Crown Avenue

La Ca?ada Flintridge, CA 91011

P: 818.949.5547

E:dgray@flintridgeprep.org

W: www.flintridgeprep.org

 

 

Diana Gray 2

Hi
John,

 

To be more
clear (sorry I was a bit vague!), I would like the rows to sort
alphabetically by Head of Household.  This means that
spouses whose last name is not the same as Head of Household will
still be coupled with the Head of Household by appearing on the row
just beneath on a spreadsheet.

 

Example showing
how I would like them to sort:

 

HoH     
Murphy, Kim

Spouse Murphy,
Pat

HoH     
Smith, Lee

Spouse Zhang,
Leslie

HoH     
White, Ray

Spouse
Bilstrom, Jackie

 

Any
suggestions?

 

Thanks
again,

Diana

 

 

Aaron Rothberg

Hi Diana-


Assuming all couples are made up of two full constituent records, I would export both constituents in the couple (not just HoH), but I would also include the spouse name and spouse constituent Id on the same line as the first person in the couple. That means you'll end up with something like this:


101 Aaron Rothberg 128 Aaron's Spouse

128 Aaron's Spouse 101 Aaron Rothberg


Next, after you've produced the export, add a calculated field in Excel called Lowest Id with the equation "=(IF(A1<D1,A1,D1))" without the double-quotes. This assumes A1 has the first Constituent Id and D1 has the second Constituent Id. Next, sort by the Lowest Id column and delete the D1 column data so you're looking at a list of constituents instead of a list of couples. That last step isn't necessary. It all depends on what you want to do with the data next.

Diana Gray 2

Thank you.  I will give this a try. 
It certainly looks less cumbersome than what I’ve been doing.

 

That being said, it would make things a lot
easier if there were a field that would allow this sorting
convention.  I have a need for this sort on a regular
basis.  One example is printing out a list in connection with
Events where you want to see each attending individual.  It
just makes sense to have couples appear line by line next to each
other.

 

I have thought about globally importing the
Head of House sort sort name as an attribute on both Head of
Household and Spouse record (SmithLee01 & SmitLee02).

 

Thanks again,

Diana

 

Diana Gray  
I 
Database Administrator

Flintridge Preparatory School

4543 Crown Avenue

La Ca?ada Flintridge, CA 91011

P: 818.949.5547

E:dgray@flintridgeprep.org

W: www.flintridgeprep.org

 

 

Jen Claudy

I like Aaron's idea, and I've used something similar to your Attribute idea at a former org.


Another option that just occurred to me...you could use an Additional Addr/Sal.  On the HOH record, set it to [Lastname, Firstname & SpouseLastname, SpouseFirstname - HOH] then on the Spouse record, [SpouseLastname, SpouseFirstname & Lastname, Firstname - Spouse]...they'll both end up as [Hernandez, Robert & Smith, Wendy] with the extra tag to put the HOH first.  You can either Globally Add or Export/Import data to get this on existing records and then add it to any Constituent Batches or Default Sets you use and follow up with some QC Queries to be sure every Individual Record has the Addr/Sal.


I always liked to have an Addr/Sal (I called it [Alpha Sort]) that displayed Lastname, Firstname & Spouse info...I put the Spouse name in parentheses if deceased so the name would still appear and make it very easy to determine if this is Robert married to Wendy who is deceased or a different Robert who isn't married (in other words, without her name, both would just be Hernandez, Robert).  I included nicknames and the ConstID at the end.  Also assigned this to the Titlebar of records, because it made it so easy to see at a glance who's record I had open.

 

Diana Gray 2

Brilliant!  I think this will do the
trick and save me tons of time!

 

Thanks a million,

Diana

 

Diana Gray  
I 
Database Administrator

Flintridge Preparatory School

4543 Crown Avenue

La Ca?ada Flintridge, CA 91011

P: 818.949.5547

E:dgray@flintridgeprep.org

W:
www.flintridgeprep.org

 

 

Kim Maynard

Jen Claudy:

I like Aaron's idea, and I've used something similar to your Attribute idea at a former org.


Another option that just occurred to me...you could use an Additional Addr/Sal.  On the HOH record, set it to [Lastname, Firstname & SpouseLastname, SpouseFirstname - HOH] then on the Spouse record, [SpouseLastname, SpouseFirstname & Lastname, Firstname - Spouse]...they'll both end up as [Hernandez, Robert & Smith, Wendy] with the extra tag to put the HOH first.  You can either Globally Add or Export/Import data to get this on existing records and then add it to any Constituent Batches or Default Sets you use and follow up with some QC Queries to be sure every Individual Record has the Addr/Sal.


I always liked to have an Addr/Sal (I called it [Alpha Sort]) that displayed Lastname, Firstname & Spouse info...I put the Spouse name in parentheses if deceased so the name would still appear and make it very easy to determine if this is Robert married to Wendy who is deceased or a different Robert who isn't married (in other words, without her name, both would just be Hernandez, Robert).  I included nicknames and the ConstID at the end.  Also assigned this to the Titlebar of records, because it made it so easy to see at a glance who's record I had open.

 

What a fabulous idea!  Thanks!