Data Connectivity
 Crystal Reports Forum : Crystal Reports .NET 2003 : Data Connectivity
Message Icon Topic: Linking databases with formula field Post Reply Post New Topic
Author Message
05438560
Newbie
Newbie


Joined: 07 May 2008
Online Status: Offline
Posts: 2
Quote 05438560 Replybullet Topic: Linking databases with formula field
    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 ({@Shamrock Number});
If ({Sheet1.S Number}=NumShamrock) then
ToText({Sheet1.Current Criticality});
But this doesnt work, I would appreciate any advice
IP IP Logged
Lugh
Senior Member
Senior Member
Avatar

Joined: 14 Nov 2007
Online Status: Offline
Posts: 377
Quote Lugh Replybullet 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.


IP IP Logged
05438560
Newbie
Newbie


Joined: 07 May 2008
Online Status: Offline
Posts: 2
Quote 05438560 Replybullet 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?


Edited by 05438560 - 14 May 2008 at 5:00am
IP IP Logged
Lugh
Senior Member
Senior Member
Avatar

Joined: 14 Nov 2007
Online Status: Offline
Posts: 377
Quote Lugh Replybullet 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.


IP IP Logged
NeedMorHP
Newbie
Newbie
Avatar

Joined: 03 Nov 2010
Location: United States
Online Status: Offline
Posts: 1
Quote NeedMorHP Replybullet 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
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.047 seconds.