PRIMARY KEYS

[Kim296]

My Problem: I have three different tables that I'm trying to use in one combined report- which include: lwmain (incident reports completed), vhmain (Vehicle Description) & vhstatus (Recovered or Stolen).

The report will use information from each of these tables, but there are no common named keys that link. I need someone very smart that's willing to help me figure this out. So far this is what I've been able to connect somewhat between the tables:

vhstatus.vehiclekey = lwmain.lwmainid
AND
vhstatus.vehi_id2 = vhmain.vehi_id

My first attempt was to Group my report by vhstatus.vhstatus (Stolen), then add the vehicle description with the correct fields, then link field (vhstatus.vehi_id2 = vhmain.vehi_id)even though the field names do not match, so that I can try to show the location of the event. I tried creating a formula to pull this part together:

If ({vhstatus.vehi_id2}={vhmain.vehi_id}) then ({vhstatus.streetnbr}+ " , "+{vhstatus.street})

This did not work because it made my complete report blank. Any suggestions on how to bring the information from multiple tables, that have no matching primary keys or field names, together? Thanks.

 

[IanWaterman]

You should not be joining tables in formulae. They need to be related in data expert. Otherwise you will have a cartesian join bring back miilions of useless records.

Join your tables on the links you have identified above

then simple create a formula
@Address1
{vhstatus.streetnbr}+ " , "+{vhstatus.street}

NB if either of these fileds are null reseult will be blank, if one or the other can be null then you must test with an if then else

I find it easier to test each address field with its own formula, bit more long winded but more reliable

@StreetNbr
Is isnull({vhstatus.streetnbr}) then '' else {vhstatus.streetnbr}+ " , "

@Street
Is isnull({vhstatus.street}) then '' else {vhstatus.street}+ " , "

@PostCode
Is isnull({vhstatus.postcode}) then '' else {vhstatus.postcode}

@Address
{@StreetNbr}{@Street}{@Postcode}

Ian

 

[Kim296]

Thanks Ian,

I did try that first. This is the wierdest set-up ever. Even though the fields show the same information when viewed seperatedly, it will not let me join the fields- it says the data types are not compatible.

This is way above my head, but I know theres a way to do this. We do have a report in CAD Software that generates a single report (multiple pages) that includes all of this information from each table. I'm trying to make a list of stolen vehicles only, without all of the other information, that would be a single page or two.

If you have any other ideas, I would love to hear them. If not, I understand and appreciate what you did offer.

 

[IanWaterman]

Some database designers should be shot!! Why would they store vhstatus.vehiclekey and lwmain.lwmainid as different data types.

Some databases allow you to link BUT Crystal will not.

I have had this problem before and the only way around it is to use a view of one of the tables and then change the data type for linking. Or you could use a Crystal Command. Both these options require a reasonable level of expertise with SQL. I am guessing this is what they have done for your CAD application. Can you get a copy of that report and then modify to suit your needs?

Ian

 

[AndyMc]

Another less than ideal approach is to use subreports. Subreports will allow you to link by formula. So you can use a formula to convert the data type of one of the fields and then use that as your link.

It will be slower and you will probably have to pass values from the subreport to the main report. The subreport link will act like an outer-join.

 

[Kim296]

Thank you both for your advice. I really appreciate it!!!! I will keep working with it to get something acceptable. Thanks.