using a combined name

[zenmind]

Crystal Reports 10 and Oracle.

In a selection of records intended for mailing, all records are individuals not couples. Their spouses may or may not have been selected. Each record has an ID and a field for the spouse ID. How would I identify the existence of the spouses that have their own rows? I can dedupe the rows for purposes of a single mailpiece by combining the records on a calculated sortname but I only want to use a Mr and Mrs... combined name if both spouses have been selected (the mailing is intended for both)

Any ideas are appreciated.

 

[Turkbear]

Hi,
You could try using the table twice ( Crystal will create an alias for the second time used) and link by Spouse ID.

If you use an Equi-Join ( the default) then only those records that have matching Spouse IDs will be returned.





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Turkbear]

Hi,
That may not work, as I think about it..you would get duplicate records not just spouses' ones unless you eliminate ones from the right-most table that have the same ID as the Left table.

Try using a Group selection formula to screen out those with matching IDs -





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[zenmind]

I need some kind of flag for each record to say if their spouse is also in the record selection.

 

[pmax9999]

Hi

Not certain it is the very best way but assuming I have understood the challenge correctly I would approach it like this:

Create a formula like this and then create a group on it:

//@GroupField

If NOT Isnull(table.SpouseID})
Then
If {Table.ID} > {table.SpouseID}
Then ToText({Table.SpouseID}, '#') + '/' + ToText({table.ID}, '#')
Else ToText({Table.ID}, '#') + '/' + ToText({table.SpouseID}, '#')
Else ToText({Sheet1_.ID}, '#')


The result is that where a party and his/her spouse are both included in the data selection, they will both appear together (in consecutive rows).

A distinct count of {table.ID} in the group footer of 1 will indicate that only the individual party has been selected, and 2 will indicate that both the individual and his/her spouse are included.

The detail rows could be suppressed, and the address details constructed in the group footer.

 

[pmax9999]

Correction to formula (last line):

//@GroupField

If NOT Isnull(table.SpouseID})
Then
If {table.ID} > {table.SpouseID}
Then ToText({table.SpouseID}, '#') + '/' + ToText({table.ID}, '#')
Else ToText({table.ID}, '#') + '/' + ToText({table.SpouseID}, '#')
Else ToText({table.ID}, '#')