Link Question

[BradW]

Good evening, and thanks for your help!

I have contact who wants to generate a report from two different databases. One is written in FoxPro and the other is Oracle. There is a common field that would be used to link the relevant tables from each database, however in FoxPro is is formatted as text and in Oracle it is a number. Both contain a record number that appears on both systems. THe problem is that Crystal blows up because of the different field formats between these two systems. Is there any way to force this link or to create a formula (ToNumber or ToText come to mind) and use that as one of the linking fields?

Again, thanks in advance for all help!

 

[BurnsORegan]

BradW

I believe your link is going to have to be in a sub-report. If you create the data required from the second database in a sub report and then create a formula : {@LinkField}

ToText(yourfield) formula

THen link the two reports on the sub-report link to that field.

Hope this helps

BurnsORegan

 

[Mike Lewis]

Bradw,

If the Foxpro table is in a database (a DBC) and you are accessing it via ODBC, add a view to the database. Make the view identical to the table, but add a new field to contain the key as a number. Then, use the view instead of the table in the report.

If you need any help with those steps, come back and I'll talk you though it. If the table is not in a DBC, I'll see if I can come up with another answer.

Mike


Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[BurnsORegan]

MikeLewis

I have read your response to the problem and am interested to find out how you create the view? Is this a Database function or a crystal function?

BurnsORegan

 

[Mike Lewis]

BurnsORegan,

Creating the view is something you would have to do in the database. You will need access to the database, and permission to do create views.

In the case of the Foxpro database, you will need to have a copy Visual Foxpro available in order to create the view.

If you can do that, I'll give you more details on how to do it. If you can't do in Foxpro, you could create the view in Oracle, but I can't tell you how to do that. But other forum members can, I expect.

Mike


Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[BradW]

Thanks to both of you for the help. Mike - I don't have FoxPro available AND it is a commercial database that I don't think I should tinker with.

Burns - I'll try the sub report routine and see how that works. Thanks for the help!