Want changes to our custom Christmas list

[John Dykema]

We ask the congregation to send cards to our shutins, sick, etc. I set an activity code of 9004 to all those I want on this list and we distribute the list. If only one person of a family has this code.....NO problem.
If both spouses have the code then I get a line printed for each of them and with the same address, i.e.
Jane Doe 123 Elm st.
John Doe 123 Elm st.
I'd like to see it print out as
Jane & John Doe 123 Elm st. but I can't figure out how to do that. You may ask 'why don't you just code one of the shutins'. Well, then the other spouse will not be on the list and may not receive a christmas card. You know how it goes??
Anyone know how I could get both spouses on one line if both have the same Actv. code?
Thanks.

[Jeff]

You are going to have to handle this like a church directory.

Sort the records by family names and address
In the report layout create a group for each family
Build a variable to hold the names in the family
The detail band would be empty and all the output would be in the group footer

Take a look at church directory examples and apply them to activities.

Another option, would it work to just print mailing labels to this group using the built-in label process? This will automatically combine the names to the family in that group.

[John Dykema]

I checked out the labels option and that does work the way I want but not in the format I like.
I may tackle the "directory" option when I get in the mood .
Thanks much for your help.

[John Dykema]

Well, I tackled it but I'm loosing the game. I did what you suggested and grouped on the usual Last-a, first, and address, then created a variable:
IIF((Qmf_temp.pers_no = 1), Qmf_temp.firstname_b,"")
for the name of the spouse1 if he's got the skills code and then for spouse 2 if she's been selected (filtered).
I then created a var. m.formattednames which looks like:
IIF((m.name1 > " " AND m.name2 > " "),Qmf_temp.nameline, IIF((m.name1 > " "), Qmf_temp.firstname_b + " "+ Qmf_temp.lastname_b, IIF ((m.name2 > " "), Qmf_temp.firstname_b + " " + Qmf_temp.lastname_b, Qmf_temp.nameline))
I'm getting a "Function Name is missing )." error on this variable.
If I try to run the report it gives me:
Report could not be generated because of a Function is missing ).
I've spent too much time of this one and I don't have many or hairs to pull out.
Please advise. Thanks.

[Tracy]

Well, I tackled it but I'm loosing the game. I did what you suggested and grouped on the usual Last-a, first, and address, then created a variable:
IIF((Qmf_temp.pers_no = 1), Qmf_temp.firstname_b,"")
for the name of the spouse1 if he's got the skills code and then for spouse 2 if she's been selected (filtered).
I then created a var. m.formattednames which looks like:
IIF((m.name1 > " " AND m.name2 > " "),Qmf_temp.nameline, IIF((m.name1 > " "), Qmf_temp.firstname_b + " "+ Qmf_temp.lastname_b, IIF ((m.name2 > " "), Qmf_temp.firstname_b + " " + Qmf_temp.lastname_b, Qmf_temp.nameline))
I'm getting a "Function Name is missing )." error on this variable.
If I try to run the report it gives me:
Report could not be generated because of a Function is missing ).
I've spent too much time of this one and I don't have many or hairs to pull out.
Please advise. Thanks.

When I work with long inline ifs like this I break it down in an editor like Windows Notepad as such:

IIF
(
..(
....m.name1 > " " AND m.name2 > " "
..)
..,Qmf_temp.nameline, IIF
..(
....(
......m.name1 > " "
....)
...., Qmf_temp.firstname_b + " "+ Qmf_temp.lastname_b, IIF
....(
......(
........m.name2 > " "
......)
......, Qmf_temp.firstname_b + " " + Qmf_temp.lastname_b, Qmf_temp.nameline
....)
..)

I usually use an editor which shows the white space with dots. I used periods above to show the level of parenthesis.

[John Dykema]

Thanks, Tracy.
I know that each parenthesis has to be paired up. In my example I see I have 11 paran's. That's not good. I've tried all kinds of combinations, removing, adding etc to no avail. I still get the error. I've verified the two variables m.name1 and m.name2 so I know those are good. It's the m.formattedname that's the culprit. I did spot one line that was incorrect. The m.name1 must return qmf_temp_firstname_A, not firstname_B, the way I had it in my example.
Is there something obvious to you what could be wrong with this variable?

[Tracy]

John,
Not paring the parenthesis, quotes (single or double), missing a comma and sometimes missing a + concatenator will bring this error up. Copy the existing variable string you have perhaps another pair of eyes will see the trouble.

[John Dykema]

OK,
variables:
m.name1- IIF((Qmf_temp.pers_no = 1), Qmf_temp.firstname_b,"")
m.name2- IIF((Qmf_temp.pers_no = 2), Qmf_temp.firstname_b,"")

Now I finally verify clean with the following m.formattedname variable:
IIF (NOT EMPTY(m.name1) AND (m.name2), Qmf_temp.nameline," ")
IIF(NOT EMPTY(m.name1), m.name1 + " "+ Qmf_temp.lastname_b," ")
IF(NOT EMPTY(m.name2), m.name2 + " " + Qmf_temp.lastname_b, " ")


Now I get "This report could not be generated because of the error- Syntax error"
This is really a simple report and the variables are clean. What gives?

[Tracy]

Code: Select all

IIF (NOT EMPTY(m.name1) AND (m.name2), Qmf_temp.nameline," ")
IIF(NOT EMPTY(m.name1), m.name1 + " "+ Qmf_temp.lastname_b," ")
IF(NOT EMPTY(m.name2), m.name2 + " " + Qmf_temp.lastname_b, " ")

You're comparing a logical to a string on the first line. Then none are concatenated together. Last IF should be IIF. So one of the second two reasons is throwing the Syntax error.

Code: Select all

IIF (NOT EMPTY(m.name1) AND EMPTY(m.name2), Qmf_temp.nameline," ") + IIF(NOT EMPTY(m.name1), m.name1 + " " + Qmf_temp.lastname_b," ") + IIF(NOT EMPTY(m.name2), m.name2 + " " + Qmf_temp.lastname_b, " ")

And I am reading between the lines, perhaps you are actually looking for this:

Code: Select all

IIF (EMPTY(m.name1) AND EMPTY(m.name2), Qmf_temp.nameline," ") + IIF(NOT EMPTY(m.name1), m.name1 + " " + Qmf_temp.lastname_b," ") + IIF(NOT EMPTY(m.name2), m.name2 + " " + Qmf_temp.lastname_b, " ") 

When both m.name1 and m.name2 are empty, use the nameline, otherwise, use one or the other.
Which can be simplfied to:

Code: Select all

IIF (EMPTY(m.name1) AND EMPTY(m.name2), nameline, IIF(NOT EMPTY(m.name1), m.name1 + " " + lastname_b, m.name2 + " " + lastname_b)

[John Dykema]

Tracy, thanks for all your time on this one. I programmed in COBOL for many years but this language is the hardest for me. Anyway, I did use you're last suggested paragraph and it did work after I added another ) on the end but doesn't give the results we want.
Isn't it possible to code somehow "if name.1 and name2 are not empty"? I just can't get the syntax right. In fact, they will NEVER be BOTH empty because of the filter I'm using, but both spouces may be coded to receive a christmas card, in which case I'll use the Nameline. The rest of the paragraph is clear to me.
Thanks again.

[Jeff]

The easiest way to see if both variable are empty is to use:

Code: Select all

EMPTY(m.name1+m.name2)

If both variables are empty, this will return "true"

Sorry read you post again, and this is not what you are loking for.

[Jeff]

How is the m.name1 variable getting populated? Will it always have a value? If so, we really only need to worry about if the second variable as an entry or not.

To see if both have a value you would have to use:

Code: Select all

(NOT EMPTY(m.name1)) AND (NOT EMPTY(m.name2))

[John Dykema]

Jeff, let me summarize what I've done so far. As per your first response way above, I've grouped families the standard method.
I set up 2 variables to capture the spouses names: They are populated as
m.name1- IIF((Qmf_temp.pers_no = 1), Qmf_temp.firstname_b,"")
m.name2- IIF((Qmf_temp.pers_no = 2), Qmf_temp.firstname_b,"")
They will always have the initial value in both vars of "" and both verify clean. They will never both be blank because of my Edit Record filters.

Now, to get my group footer name field to print as:
John Doe, (if only the first spouse get a christmas card and)
Jane Doe, (if only the second spouse gets a card), and
John & Jane Doe (if BOTH spouses are coded to get a card). Seems simple enough doesn't it?

Both Tracy and you have been gracious to suggest coding and I've combined them to be :
IIF(NOT EMPTY(m.name1)) AND (NOT EMPTY(m.name2)), Qmf_temp.nameline, IIF(NOT EMPTY(m.name1), m.name1 + " " + Qmf_temp.lastname_b, m.name2 + " " + Qmf_temp.lastname_b))

I'm still getting a missing comma error. Please advise. Thanks.

[Tracy]

I'm noticing a couple of things now.

Code: Select all

m.name1- IIF((Qmf_temp.pers_no = 1), Qmf_temp.firstname_b,"")
m.name2- IIF((Qmf_temp.pers_no = 2), Qmf_temp.firstname_b,"")

should be

Code: Select all

m.name1- IIF((Qmf_temp.pers_no = 1), Qmf_temp.firstname_b,m.name1)
m.name2- IIF((Qmf_temp.pers_no = 2), Qmf_temp.firstname_b,m.name2)

because m.name1 will become to blank when pers_no = 2, the initial value of the variable needs to be "", and reset at the grouping

next your variable formatedname should be this

Code: Select all

IIF((NOT EMPTY(m.name1)) AND (NOT EMPTY(m.name2)), Qmf_temp.nameline, IIF(NOT EMPTY(m.name1), m.name1 + " " + Qmf_temp.lastname_b, m.name2 + " " + Qmf_temp.lastname_b))

If both names have something in it, use the nameline value, otherwise use the individuals name.

[John Dykema]

Now I understand how the m.name1 & 2 vars should get populated. That blew right past me. Thanks.
I also copied/pasted the last variable into my program and now it works . Now all I have to do is Alltrim the names and I'll be done.
Note, when I do the verify test on the last variable I still get an Operator/Operand Mismatch error. Could that be a gliche in the system?

Also, if I were to buy a manual on this Report generator language, which one would you recommend?
Thanks for being patient and your time.
John