Displaying unique values only 2

[martinjt]

I'm using Crystal Reports 9.2 with an Oracle database. I need help to display unique values for a first name field of diffent name types. (For expample, Preferred Name, recognition Name, Spouse name, etc.) I'm creating a subreport that lists first names only but I don't want the names to repeat. I just need unique values only. These names all belong to one record (in this case, a couple - Husband and Wife) with multiple name types:

Name Type First Name
NT1 Jon
NT2 Mary
NT3 Mary
NT4 Jon and Mary
NT5 Jon

I can use a string array function to list them on one line like so: Jon|Mary|Mary|Jon and Mary|Jon

Or as a vertical list but either way, I just need Jon and Mary listed once. How do I test for duplicate values in this instance?

Thanks. - JT

 

[dev1212]

Check Database-> Select Distinct Records

 

[lbass]

You say these belong to one record--do you mean that NT1, NT2, etc., are different fields in the same row? Or is there a field NameType, with multiple instances?

-LB

 

[Madawc]

Assuming each line is a separare record, if you grouped by First Name, that would take care of simple duplicates. Presumably there is some higher-level group that links all of the records for the same person or couple.

You can collect details from the higher-leve group and show them all in the group footer. Try adapting something I wrote to collect postcodes:

// Accumulate using a formula field (suppressed) in the detail line.  Allow for nulls and blanks.
whileprintingrecords;
if not isnull({Recc.Postcode}) and {Recc.Postcode} <> " "
then stringvar pst := pst + {Recc.Postcode} +", "
else if length(pst) = 0 
then stringvar pst := pst + "Excluding blanks; "
else stringvar pst := pst

// Show in using a formula field in the group footer.
whileprintingrecords;
stringvar pst;
left(pst,len(pst)-2)

//  Clear using a formula field in the group header.
whileprintingrecords;
stringvar pst := "";

Note that clearing in the footer avoids problems with group headers repeating on a new page, which does clear everything for the group. Provided the 'clear' is placed in the section AFTER the display, it will do them in that order.

A problem arises with NT4 | Jon and Mary, which would be treated as a third name. Maybe you could exclude NT4 records. Or use SPLIT if the data is variable and you sometimes need it.

Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[martinjt]

Thanks. The NT1 etc. are diffent types (or instances) in one field, Name Type. I grouped on the first name and applied the accum/show/clear routine to indicate the types and got the right result:

Jon
(NT1,NT5)

Mary
(NT2,NT3)

Jon and Mary
(NT4)

This is most likely what the customer wants. I didn't bother (yet) with SPLIT as this data is not consistent (doesn't always appear as "X and Y"). Thanks again for your help! - JT