Formatting the household names

[John Dykema]

Do you folks at PowerChurch recommend entering both first names into the Household Key Firstname field? On pg 61 of the v10 manual, it shows both names entered but then in item 3 it says it's mainly the Primary contact person (which tells me that only one should be entered). We don't enter both names in that field.
Why am I asking?
I'm trying to create a custom directory and here, they like our names in the directory to show as:
JOHNSON, David & Mary
As I'm sure most of you do, we enter "David & Mary Johnson" in the Mailing name field. I don't see any way to pick our the two spouses names from the Mailing name field. Furthermore not every family has two spouses.
Does anyone know if there's a way of picking out those spouses names with a substring or something like that from the Mailing field?
Any other recommendations?

[Lee L]

How about the "salutation" field? We put the two first names in that field formatted: David & Mary. If you also do this, the only thing you might have to worry about is whether you had used diminutives (Dave & Mary). I see it's an available field in the Family Mailing List custom reports.

[John Dykema]

Thanks, that should work. I'll have to look at the shorted names.

[John Dykema]

Opps. Lee, I just thought of something. Our salutation field is like: "Dave & Mary". We want to put an "*" behind the name for members i.e. JACKSON, Dave* & Mary* and I don't want to have "*" in the Salutation.
I suppose I could put the "directory" first names into the Notes field of the Mailing record or some such thing. WYO?

[Jeff]

John,

When we create the church directory we look at information from the Profiles to get the name and personal status. You can also do this in the custom reports, but is more complex.

You will need to group records in the report layout so you can group families together. Take a look at this example, it will show you how to set up the output and create the groups in the report layout. http://www.powerchurch.com/support/answ ... icle_id=26

In this example I am assuming you have the following fields in the following order:

Household Key Last Name
Household Key First Name
Address Line 1
Address Line 2
City + State + Zip
Family Phone 1
Profile Last name
Profile First name
Directory Sequence
Personal Status Desc

You will want to sort the query in the following order:
Household Key last name
household key first name
Address line 1
Directory Sequence

Run the query and let it create the report.
Click Edit form to get to the report layout.

Create a group for the family
Goto Report -> Data Grouping
Click Add and enter the following expression: lastname_a + firstname_a + address

This will add a new group header and footer bands for the family.
Expand the group footer band and move the address and phone number fields into this band.
Delete all the other fields from the detail band and shrink the detail band so there is no space left in it.

Create variables to create the names.
You are going to need 5 variables to handle creating all your names. When using variables you need to remember that all the variables are run each time a new detail band is processed.

Go to Reports -> Variables and add each of the following variables in this order:

Name: m.currentname
Initial Value: ""
Reset value based on: Report
Calculation type: None
Value to store: IIF(UPPER(lastname_b) <> UPPER(lastname_a), ALLTRIM (lastname_b) + ", " , "") + ALLTRIM(firstname_b) + IIF("MEMBER" $ UPPER( status_desc), "*", "")

** you are probably going to need to change this variable for your needs. The part that says "IIF("MEMBER" $ UPPER(status_desc), "*", "")" is putting an asterick next to people whose profile status contains the word member. As this is it won't work for status's of non-member or spouse of member etc. because it is looking for the word member anywhere in the string.

Name: m.name1
Initial Value: ""
Reset value based on: Group: lastname_a...
Calculation type: None
Value to store: IIF( pers_no = 1, m.currentname, m.name1)

Name: m.name2
Initial Value: ""
Reset value based on: Group: lastname_a...
Calculation type: None
Value to store: IIF( pers_no = 2, m.currentname, m.name2)

Name: m.parents
Initial Value: ""
Reset value based on: Group: lastname_a...
Calculation type: None
Value to store: UPPER(ALLTRIM(lastname_a)) + ", " + m.name1 + IIF( NOT EMPTY(M.NAME2), ' & ' + m.name2, "")

Name: m.kids
Initial Value: ""
Reset value based on: Group: lastname_a...
Calculation type: None
Value to store: m.kids + IIF(pers_no < 3, "", IIF(EMPTY(m.kids), "", ", ")+m.currentname)

Close the variables dialog.
Use the field tool to add a field for the parents variable. For the expression enter "m.parents"
Add another field to display the children. The expression to show the children will be "m.kids"

I know that is a lot at once, especially if you are not comfortable with report variables.

[John Dykema]

Thanks, Jeff, I'll give this a try.