I have a strange issue. I have data stored in 2 tables.
Table 1: Contains data about an Individual
- IndividualID
- Birthdate
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