Print Page | Close Window

Linking databases with formula field

Printed From: Crystal Reports Book
Category: Crystal Reports .NET 2003
Forum Name: Data Connectivity
Forum Discription: How to connect to data sources and export reports
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=3169
Printed Date: 28 Apr 2024 at 1:27pm


Topic: Linking databases with formula field
Posted By: 05438560
Subject: Linking databases with formula field
Date Posted: 14 May 2008 at 3:13am
Hi, Im trying to link an Access database with another database. The only link between the two is a the result of a formula field in one of the databases and a field in the other. So i cant use the visual linking expert. I have been trying to do it in a formula field with this formula:
 
NumberVar NumShamrock;
NumShamrock = CDbl ( mailto:%7b@Shamrock - {@Shamrock Number});
If ({Sheet1.S Number}=NumShamrock) then
ToText({Sheet1.Current Criticality});
But this doesnt work, I would appreciate any advice


Replies:
Posted By: Lugh
Date Posted: 14 May 2008 at 4:21am
OK, first of all, are you really linking two databases, or just two tables in a single database?  I'm assuming, from the description, that you are just working with tables.

Second, Crystal will evaluate the links long before it evaluates any formulas.  So, you can't use formulas to modify how Crystal pulls data from the database (either with linking or in the Select Expert).

The simplest solution is to create a query in Access.  You can then use this query as the data source for your report.

If you don't have that option, you will need to use the Add Command feature in the Database Expert to use SQL to define the join.  I would need to know what your @ShamrockNumber formula looks like to give you a hint on how to structure the SQL.

Posted By: 05438560
Date Posted: 14 May 2008 at 4:58am
Thanks for your reply. The "Shamrock number is the last four or five digits of a number in the database. The formula is just to display these numbers.
 
if (Length ({TBL_HISTORY.PLANT_ITEM_CODE})=12)
then (right ({TBL_HISTORY.PLANT_ITEM_CODE},4))
else if (Length ({TBL_HISTORY.PLANT_ITEM_CODE})=13)
then (right ({TBL_HISTORY.PLANT_ITEM_CODE},5))
else (right ({TBL_HISTORY.PLANT_ITEM_CODE},5))
 
One of the databases is just an excel spreadsheet i opened in access. The other is a database from a data logging program we use. I dont think I can use this in access to make a query?

Posted By: Lugh
Date Posted: 14 May 2008 at 10:12am
Sure you can.  Look into creating linked tables in Access (which is, oddly enough, totally different from linking tables in Crystal).  These are aliases to tables in other locations (including Excel spreadsheets) that can be used just like regular tables.  I think that you'll find that maintaining them in Access is a bit easier than working multiple datasources in Crystal.

So, your join is going to be a bit tricky, because it's got an IF in there.  But, this should work (note that you will not be able to view it in the Query Builder in Access, and will only be able to see the SQL):


SELECT TBL_HISTORY.*, Sheet1.*
FROM TBL_HISTORY
JOIN Sheet1
ON Sheet1.[S Number] = 
    IIF(LEN(TBL_HISTORY.PLANT_ITEM_CODE)=12,
        RIGHT(TBL_HISTORY.PLANT_ITEM_CODE,4),
        RIGHT(TBL_HISTORY.PLANT_ITEM_CODE,5))


Obviously, you will need to add a WHERE clause, and you will probably want to actually specify the field in the SELECT clause.  But, that should get you started.

Posted By: NeedMorHP
Date Posted: 03 Nov 2010 at 9:17am
I have a similar issue, except that I am linking to SAP.

I have two tables, one has a field that is four characters (A041) and the other table has a two character field (A0).
I am using live data from SAP and cannot create a query unless I run a transaction export the data for the two different tables then import it into Access then link the report to Access using the MID formula. This is a lot of steps for a work around and when the data changes in SAP I would have to redo these steps again.

So, what would be my solution?


-------------
Jack A. Jones, Jr.
CPS Energy
Program/System Analyst
Asset Management-EDS


Print Page | Close Window