0

I have a strange issue. I have data stored in 2 tables.

Table 1: Contains data about an Individual

  • IndividualID
  • Birthdate
  • Email

Table 2: Contains the Individual names split into 3 fields each having a key.

  • IndividualID
  • NameType
  • Name

This means say for NameType = 1, I would get the Surname, for NameType = 2, I would get the Middle Name, and for NameType = 3 I get the FirstName.

I need to create a Crystal Report that will list this individual's name in one line, such as

Surname + FirstName + MiddleName

The problem I experience is related to how to pull this data out and the joins.

If I just create the Join between these 2 tables as a left outer join, then I cannot select which NameType to select. This means tht sometimes I will get the Surname, other times the MiddleName, etc.. Seems as tho the join is not consistent. Furthermore, I cannot iterate over the expected 3 values so seems I can only pull the same one every time.

I have tried to fix this by Adding a Command which would in turn select each of the names from the second table. However, this report is meant to be integrated into something called Raiser's Edge which does not support Command objects. In other words, Command Objects are not an option for me.

So what are my options here? I prefer not using a subreport which I know would work, but has performance overhead. Is there any other way to do this?

Thanks

Steve Kiss
  • 1,108
  • 3
  • 13
  • 24

1 Answers1

0

The easiest way is to group on IndividualID and then keep track of first, middle, and last names via variables. There's no need to worry about record selection or joins, really. Try something like this:

//Formula for group header
whileprintingrecords;
stringvar first := "";
stringvar middle := "";
stringvar last := "";

//Formula for details section
whileprintingrecords;
stringvar first;
stringvar middle;
stringvar last;
select {table2.NameType}
case 1 : first := {table2.Name}
case 2 : middle := {table2.Name}
case 3 : last := {table2.Name}

//Formula for group footer (the section to be displayed)
whileprintingrecords;
stringvar first;
stringvar middle;
stringvar last;

last + ", " + first + " " + middle
Ryan
  • 7,212
  • 1
  • 17
  • 30