Active TopicsMemberlistSearchHelpRegisterLogin

Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design
Message Icon Topic: Linking two dbase fields that differ slightly Post Reply Post New Topic
Author Message
funkyrobot
Newbie
Newbie


Joined: 10 Mar 2009
Location: United Kingdom
Online Status: Offline
Posts: 13
Quote funkyrobot Replybullet Topic: Linking two dbase fields that differ slightly
     Posted: 09 Jul 2009 at 6:00am
Hi all, another 8.5 problem.
 
I am trying to get two fields talking to each other in order to get a report printed. The problem I am having is that one field has values of 'X00123456' and the other field has '123456'.
 
The data I am trying to link resides within an MS Access database and consists of two tables, one called 'Purchase Orders' and one called 'QC check'. Purchase orders has the 'PO' field as 'X00...' and QC Check simply has the 'Load' field as '123...' (PO and Load are really the same information, apart from the 'X00' prefix).
 
Is there any way I can link these two as I need to display supplier information for the QC checks that is held in the 'Purchase Orders' table. The only difference between the values is the 'X00' prefix, but it's causing me a lot of confustion!
 
I am trying to link then in the Visual Linking expert but this causes an error as the fields are of different lenghts. I have also tried to make a formula to link them but have had no luck with that either.
 
I'm still fairly new to Crystal, so is this something that can be done?
 
Many thanks for any help offered.
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
Quote lockwelle Replybullet Posted: 09 Jul 2009 at 6:24am
Depending on how your report is to operate, you might try a subreport, but it will impact performance.  One would think that a formula that prepends the X00 to the field would have a chance of working, for a subreport.  I don't know about joining the tables inside of Crystal as I don't use it.  I would suggest stored procedures, which could easily handle this situation, but Access doesn' support stored procs, or at least not very well.
 
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 09 Jul 2009 at 6:51am
Perhaps using a command to select the data from Purchase orders and create a new field triming your linking field and casting that to an int to match the same field type as your other table?
IP IP Logged
funkyrobot
Newbie
Newbie


Joined: 10 Mar 2009
Location: United Kingdom
Online Status: Offline
Posts: 13
Quote funkyrobot Replybullet Posted: 09 Jul 2009 at 7:19am
Thanks for your responses. I was thinking of some way of adding the X00 to the load numbers, but wasn't too sure how to do this.
 
Dblank, I am sorry but i'm still fairly new to Crystal. Could you expand on your response for me?
 
Thanks again both of you.
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
Quote lockwelle Replybullet Posted: 09 Jul 2009 at 7:27am
Again, good idea.  The command could return both the original and the trimmed value.  Can you then use it to link the two tables?
 
I'm curious, but I think that I would try this.
 
A command is a SQL command (usually a SELECT) statement.  In the Database Expert, usually right above your datatables is a line that says "Add Command" or something like that, which give you access to the Command Object that DBlank referenced.
IP IP Logged
funkyrobot
Newbie
Newbie


Joined: 10 Mar 2009
Location: United Kingdom
Online Status: Offline
Posts: 13
Quote funkyrobot Replybullet Posted: 09 Jul 2009 at 7:39am
Originally posted by lockwelle

Again, good idea.  The command could return both the original and the trimmed value.  Can you then use it to link the two tables?
 
I'm curious, but I think that I would try this.
 
A command is a SQL command (usually a SELECT) statement.  In the Database Expert, usually right above your datatables is a line that says "Add Command" or something like that, which give you access to the Command Object that DBlank referenced.
 
I am working in 8.5 though (v old, i know lol), and I don't think you can add a command in SQL in that version?
 
Thanks.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 09 Jul 2009 at 7:42am
Do as lockwelle said to find the Command option then create a SQL statment to pull your data. This is rough and it actually conmbines your 2 tables into one Command but it wil be something like:
 
SELECT     dbo.PurchaseOrders.*, dbo.QCCheck.*
FROM         dbo.PurchaseOrders INNER JOIN
                      dbo.QCCheck ON cast((right (dbo.PurchaseOrders.PO, 8)) as smallint) = dbo.QCCheck.Load
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 09 Jul 2009 at 7:45am
Don't know about v8.5...
Can you search Help for COMMAND and see if it gives you any indication of it?
IP IP Logged
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

HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | PRIVACY | CLICK PLAYS | BAEZL

© 2004-2026 Brian Bischof. All Rights Reserved.