Church Directory - List Children on Same Line

[hailescenterpoint]

Hello!
I am familiarizing myself with Custom Reports. I am not familiar with all the functions and am having a hard time finding a function that will help me list a group on the same line right after the other with commas and parentheses.

Our church is so large, we need to use all the space we have to fit it in a 60 page directory. If you look at the half page directory report that is available in Powerchurch, it lists the children under the family name and phone number. Every year I have to go in and download it into Word. Then move "Home Phone" to the line below and move the children to the line under the parents. It is a very tedious task that I hope Custom Reports can solve.

https://www.dropbox.com/s/rdlk1gz7kj65t ... e.PNG?dl=0 <--- link to report designer screenshot

This is what I would like to happen:
Last Name, Husband & Wife
(Child 1, Child 2, Child 3, Child etc)
Phone number: 000.123.4567
Address
City, State Zip

This is what custom reports is outputting currently:
Last Name, Husband & Wife
Child 1
Child 2
Child 3
Child etc
Phone number: 000.123.4567
Address
City, State Zip

Is there a way to make the children list on the same line (with parentheses and commas) instead of on new lines for each name?

[Zorak]

Create a variable called m.children. The next few lines will show you how to build the expression in a step-by-step manner, which will hopefully be easier to understand.

A very simple expression for that variable to list comma separated names would be:

m.children+", "+ALLTRIM(firstname_b)

But, you don't want to add mom and dad in there, so instead use the pers_no (directory sequence) field to include only 3 and higher.

IIF(pers_no>2,m.children+", "+ALLTRIM(firstname_b),m.children)

Also, you don't want the first character to be a comma, so you would first have to check to see if m.children is still empty.

IIF(pers_no>2,m.children+IIF(NOT EMPTY(m.children),", ","")+ALLTRIM(firstname_b),m.children)

When adding the variable in the report output, you can add the parentheses.

IIF(NOT EMPTY(m.children),"("+m.children+")","")

That will not print anything if there are no children in the family. Otherwise, they print out comma separated, wrapped in parentheses.

[hailescenterpoint]

That sounds very logical I would love to try it.

This might sound like a dumb question... how and where do I create a variable?

Thank you so much for your help!

[NeilZ]

That sounds very logical I would love to try it.

This might sound like a dumb question... how and where do I create a variable?

Thank you so much for your help!

This Knowledgebase article may help. Review step 6, it shows how to create a variable for use.

https://www.powerchurch.com/support/224 ... unt-report

[hailescenterpoint]

That sounds very logical I would love to try it.

This might sound like a dumb question... how and where do I create a variable?

Thank you so much for your help!

This Knowledgebase article may help. Review step 6, it shows how to create a variable for use.

https://www.powerchurch.com/support/224 ... unt-report

Ok I put it all in the correct spots... I think. I saved it and had a few errors which I think I fixed. I ran the report and it still looks the same. The children are still on separate lines. :/

[Zorak]

I ran the report and it still looks the same. The children are still on separate lines. :/

In the original screen print you shared, you had the firstname_b field in the Detail band. Remove that (if you haven't already) and put the m.children field down in the group footer with the address and phone.

[hailescenterpoint]

I ran the report and it still looks the same. The children are still on separate lines. :/

In the original screen print you shared, you had the firstname_b field in the Detail band. Remove that (if you haven't already) and put the m.children field down in the group footer with the address and phone.

\
I did what you said, but it only shows one child's name instead of the entire list of children. It doesn't even show the parentheses or quotation marks.
Thanks for all your help.

[Zorak]

Take a new screenshot of the Report Designer window and we'll take a look.

[hailescenterpoint]

Take a new screenshot of the Report Designer window and we'll take a look.

Fur sure!
https://www.dropbox.com/s/qatf6jwm2gyd0 ... d.PNG?dl=0

Thank you!

[Zorak]

The expression that you have on the report is the expression that should be in your m.children variable.

The expression that should be on the report output is:

IIF(NOT EMPTY(m.children),"("+m.children+")","")

[hailescenterpoint]

The expression that you have on the report is the expression that should be in your m.children variable.

The expression that should be on the report output is:

IIF(NOT EMPTY(m.children),"("+m.children+")","")

Ahhh. Ok. I made the switch. Now it shows the parentheses around one name. No comma and no more names. Thanks for the help!

[Zorak]

OK, on the Report Properties screen on the Variables tab, there are two fields: Value to store and Initial value.

Initial Value should be "" and Value to Store should be the expression I mentioned previously. The third field Reset Value Based On should be set to your family group (lastname + firstname). It sounds like it might be resetting the m.children variable on every record instead of letting it build up over the course of the whole family group.

[hailescenterpoint]

OK, on the Report Properties screen on the Variables tab, there are two fields: Value to store and Initial value.

Initial Value should be "" and Value to Store should be the expression I mentioned previously. The third field Reset Value Based On should be set to your family group (lastname + firstname). It sounds like it might be resetting the m.children variable on every record instead of letting it build up over the course of the whole family group.

Thank you for your help! That is exactly what I was intending.
After looking over the preview I realized that the church has some children and spouses with different last names. Is there a way to accommodate that? I also noticed that I didn't have the family name and parent's names formatted correctly. sorry. I played around with that this morning and ended up having the start the entire report from scratch again because I tried including salutation in the fields. The computer returned an error saying that if I included it the report would be edited.. well.. it was edited and all my formatting was gone. :/ Anyway... can you help me with the last name issue?

For instance, I would like this:
Doe, John & Jane Smith-Doe
(John Smith, Jane Doe, Jill Block)
Home Phone: 000.000.0000
Address
Address2
City, State Zip

I know the "& Jane Smith-Doe" would have to be in an IFF statement. Right? I am going to try write out how my mind thinks it would work. Again.. I am no expert.
lastname_a+IFF(pers_no=2, " & "+firstname_b IFF(lastname_b<>lastname_a, lastname_b,""),"")
I am probably waaaay off... but I thought I'd give a try.

Any ideas on the children's last name? So basically, if the child's name is different than the household name, I would like their last name to show. If it is the same, I don't want it to show.

[Zorak]

You are on the right track.

The syntax for IIF (Inline IF) is as follows:

Code: Select all

IIF(thing to check for, if that is true do this, otherwise do this)

You are getting into some more complicated stuff here, but the logic breaks down the same way that my earlier explanation did. Start with what you want to print, then work your way around it, step by step adding more conditions and formatting.

[hailescenterpoint]

You are on the right track.

The syntax for IIF (Inline IF) is as follows:

Code: Select all

IIF(thing to check for, if that is true do this, otherwise do this)

You are getting into some more complicated stuff here, but the logic breaks down the same way that my earlier explanation did. Start with what you want to print, then work your way around it, step by step adding more conditions and formatting.

Ignoring the different last name idea...

How would I format the top line to read: Last Name, Spouse & Spouse?

I've been playing around with it but it only shows the "last name, spouse" and not the other spouse. :/

Code: Select all

Qmf_temp.last_first + IIF(pers_no=2, " & "+ Qmf_temp.firstname_b, "")

What am I missing?