Link to home
Start Free TrialLog in
Avatar of SamB
SamB

asked on

How Do I create Multiple Links to the same Field on Crystal Reports?

Hi Experts,

I have a crystal report that contains two tables : 'tblMain' and 'tblTitles'

TblMain includes the following fields:

custID (PK) / pTitle / pFirstname / pLastname / sTitle / sFirstname / sLastname

TblTitles includes the following fields:

id (PK) / titleName

In the main app (vb.net) the tblMain fields relating to a title (pTitle & sTitle) are linked to the 'tblTitles id' and therefore don't store Mr, Mrs, Doctor etc.. but store the relevant id (integer).

When creating a crystal report I don't seem to be able to link the 'tblTiles id' field to both pTitle and sTitle - when I do I loose all the information on the report (ie. it goes blank).

I guess I'm linking incorrectly? I'm sure this is possible - I'm just not upt to it!!

Help as always much appreciated,

Sam

contains a customer names split into title / firstname / lastname. However the title field is just a number (FK) linked to the tbltitles table which contains and id (PK) & titlename field.

Avatar of m00958
m00958

In the "Database Expert" have you clicked on the "Links" tab and joined the two fields from the tables? If not you can select the relevant field from TblMain containing the title ID field and drag it on to the ID field in TblTitles.

If this link is in place and you still aren't getting any data I would suggest trying the same join in SQL to make sure it works okay there.
Avatar of SamB

ASKER

Hi m00958,

Yes I've used the 'links' tab in the 'database expert' to link both fields to the ID field - but that's when I get a blank report. If I just link one (either) it works ok, but once I have two joins to the same ID field it seems to go wrong.

I'm not sure if I have the correct Link Options set - it initially defaulted to :

Join Type 'Inner join'
Enforced join 'Not Enforced'
Link Type '='

Cheers,

Sam
I would have thought that would the right join type. I think I misunderstood exactly what was going on from the first message.

Why are you trying to link two fields from TblMain to TblTitles? What's different about pTitle and sTItle?

If you had two different values in pTitle and sTitle (e.g. pTitle = 1 and sTitle = 2) then when you try and create the join of "TblMain.sTitle = TblTitles.id AND TblMain.pTitle = TblTitles.id" then you won't get any records returned. If you only join it on one field (i.e. TblMain.sTitle = TblTitles.id OR TblMain.pTitle = TblTitles.id) then you will get some data.

You could perform the join outside of Crystal and base the report on the subsequent View/Query then you might be able to get around this. I used "SELECT * FROM TblMain M INNER JOIN TblTitles T ON M.pTitle = T.ID AND M.sTitle = T.ID" to test this.

Hope my ramblings in some way help :-)
ASKER CERTIFIED SOLUTION
Avatar of Mike McCracken
Mike McCracken

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of SamB

ASKER

Thanks mlmcc - just what I needed!