Church Directory - List Children on Same Line

Postby hailescenterpoint » Tue Jul 19, 2016 7:52 am

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/rdlk1gz7kj65t8w/Knowledge%20Base.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 8:53 am, edited 2 times in total.
hailescenterpoint
 
Posts: 12
Joined: Tue Jul 19, 2016 7:33 am

Re: Church Directory - List Children on Same Line

Postby Zorak » Tue Jul 19, 2016 8:44 am

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.
Zorak
Tech Support
Tech Support
 
Posts: 2493
Joined: Thu May 13, 2004 8:59 am
Location: PowerChurch Software

Re: Church Directory - List Children on Same Line

Postby hailescenterpoint » Tue Jul 19, 2016 8:59 am

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

Re: Church Directory - List Children on Same Line

Postby NeilZ » Tue Jul 19, 2016 11:36 am

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.
NeilZ
 
Posts: 8906
Joined: Wed Oct 08, 2003 12:20 am
Location: Connellsville, PA

Re: Church Directory - List Children on Same Line

Postby hailescenterpoint » Tue Jul 19, 2016 12:28 pm

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

Re: Church Directory - List Children on Same Line

Postby Zorak » Tue Jul 19, 2016 1:59 pm

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.
Zorak
Tech Support
Tech Support
 
Posts: 2493
Joined: Thu May 13, 2004 8:59 am
Location: PowerChurch Software

Re: Church Directory - List Children on Same Line

Postby hailescenterpoint » Tue Jul 19, 2016 2:30 pm

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

Re: Church Directory - List Children on Same Line

Postby Zorak » Tue Jul 19, 2016 3:23 pm

Take a new screenshot of the Report Designer window and we'll take a look.
Zorak
Tech Support
Tech Support
 
Posts: 2493
Joined: Thu May 13, 2004 8:59 am
Location: PowerChurch Software

Re: Church Directory - List Children on Same Line

Postby hailescenterpoint » Tue Jul 19, 2016 3:29 pm

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

Re: Church Directory - List Children on Same Line

Postby Zorak » Tue Jul 19, 2016 3:46 pm

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+")","")
Zorak
Tech Support
Tech Support
 
Posts: 2493
Joined: Thu May 13, 2004 8:59 am
Location: PowerChurch Software

Re: Church Directory - List Children on Same Line

Postby hailescenterpoint » Tue Jul 19, 2016 3:57 pm

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

Re: Church Directory - List Children on Same Line

Postby Zorak » Tue Jul 19, 2016 4:59 pm

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.
Zorak
Tech Support
Tech Support
 
Posts: 2493
Joined: Thu May 13, 2004 8:59 am
Location: PowerChurch Software

Re: Church Directory - List Children on Same Line

Postby hailescenterpoint » Wed Jul 20, 2016 12:37 pm

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

Re: Church Directory - List Children on Same Line

Postby Zorak » Wed Jul 20, 2016 2:28 pm

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.
Zorak
Tech Support
Tech Support
 
Posts: 2493
Joined: Thu May 13, 2004 8:59 am
Location: PowerChurch Software

Re: Church Directory - List Children on Same Line

Postby hailescenterpoint » Thu Jul 21, 2016 7:10 am

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

Next