Church Directory - List Children on Same Line

Moderators: Moderators, Tech Support

hailescenterpoint
Posts: 12
Joined: Tue Jul 19, 2016 8:33 am

Church Directory - List Children on Same Line

Post by 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. :wall:

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?
Last edited by hailescenterpoint on Tue Jul 19, 2016 9:53 am, edited 2 times in total.

Zorak
Tech Support
Tech Support
Posts: 3006
Joined: Thu May 13, 2004 9:59 am
Location: PowerChurch Software
Contact:

Re: Church Directory - List Children on Same Line

Post by 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
Posts: 12
Joined: Tue Jul 19, 2016 8:33 am

Re: Church Directory - List Children on Same Line

Post by 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
Posts: 10209
Joined: Wed Oct 08, 2003 1:20 am
Location: Dexter NM
Contact:

Re: Church Directory - List Children on Same Line

Post by NeilZ »

hailescenterpoint wrote: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
Neil Zampella

Using PC+ since 1999.

hailescenterpoint
Posts: 12
Joined: Tue Jul 19, 2016 8:33 am

Re: Church Directory - List Children on Same Line

Post by hailescenterpoint »

NeilZ wrote:
hailescenterpoint wrote: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
Tech Support
Tech Support
Posts: 3006
Joined: Thu May 13, 2004 9:59 am
Location: PowerChurch Software
Contact:

Re: Church Directory - List Children on Same Line

Post by Zorak »

hailescenterpoint wrote: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
Posts: 12
Joined: Tue Jul 19, 2016 8:33 am

Re: Church Directory - List Children on Same Line

Post by hailescenterpoint »

Zorak wrote:
hailescenterpoint wrote: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
Tech Support
Tech Support
Posts: 3006
Joined: Thu May 13, 2004 9:59 am
Location: PowerChurch Software
Contact:

Re: Church Directory - List Children on Same Line

Post by Zorak »

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

hailescenterpoint
Posts: 12
Joined: Tue Jul 19, 2016 8:33 am

Re: Church Directory - List Children on Same Line

Post by hailescenterpoint »

Zorak wrote: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
Tech Support
Tech Support
Posts: 3006
Joined: Thu May 13, 2004 9:59 am
Location: PowerChurch Software
Contact:

Re: Church Directory - List Children on Same Line

Post by 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
Posts: 12
Joined: Tue Jul 19, 2016 8:33 am

Re: Church Directory - List Children on Same Line

Post by hailescenterpoint »

Zorak wrote: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
Tech Support
Tech Support
Posts: 3006
Joined: Thu May 13, 2004 9:59 am
Location: PowerChurch Software
Contact:

Re: Church Directory - List Children on Same Line

Post by 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
Posts: 12
Joined: Tue Jul 19, 2016 8:33 am

Re: Church Directory - List Children on Same Line

Post by hailescenterpoint »

Zorak wrote: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. :wall: 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. :D

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
Tech Support
Tech Support
Posts: 3006
Joined: Thu May 13, 2004 9:59 am
Location: PowerChurch Software
Contact:

Re: Church Directory - List Children on Same Line

Post by 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
Posts: 12
Joined: Tue Jul 19, 2016 8:33 am

Re: Church Directory - List Children on Same Line

Post by hailescenterpoint »

Zorak wrote: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?

Post Reply