Active TopicsMemberlistSearchHelpRegisterLogin

Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design
Message Icon Topic: How to link a concatenated field Post Reply Post New Topic
Author Message
duffey01
Newbie
Newbie
Avatar

Joined: 04 May 2009
Location: Australia
Online Status: Offline
Posts: 39
Quote duffey01 Replybullet Topic: How to link a concatenated field
     Posted: 28 Jul 2009 at 6:50pm
Hi All
 
Using CRXI I have concatendated two fields to make one field that gives the appearance of a field in a separate table. Is there a way to link these two fields? I need info from the second table and the only 'link' I can find is to concatenate the two fields from the first table but when I place a field from the second table my report is blank.
 
Is this possible???
 
Thanks
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 29 Jul 2009 at 6:31am

If these are from the same DB structure I would guess that there is an intermediate table that both link to. However if you need to do this I would do it in a view or stored procedure and test the results there. If you cannot do that use a COMMAND in Crystal to replace one table and concatenate your field there then join the command to the otrher table.

You will have to make sure it is exactly the same. A simple error like an extra space or a missing space in when adding the 2 fields will cause none to be joined.
Hope this helps.
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
Quote lockwelle Replybullet Posted: 29 Jul 2009 at 6:32am
can you elaborate? 
 
You should be able to do that, but there are some caveats, like if you trimmed the values to get the new field, you need to trim the values in the join, and I not sure that the Crystal has such abilities (ie your field is char(10) but only 2 characters are used, the value returned is 'xx        ')
 
HTH
IP IP Logged
duffey01
Newbie
Newbie
Avatar

Joined: 04 May 2009
Location: Australia
Online Status: Offline
Posts: 39
Quote duffey01 Replybullet Posted: 30 Jul 2009 at 10:06pm

Hi DBlank

Unfornuately, there is no intermediate table to link to, (that is why I have to do what I'm doing) As for the rest of your suggestions, I have no experience at all with these so I will see what I can find out and give them a try.

 

Thanks very much
IP IP Logged
duffey01
Newbie
Newbie
Avatar

Joined: 04 May 2009
Location: Australia
Online Status: Offline
Posts: 39
Quote duffey01 Replybullet Posted: 30 Jul 2009 at 10:19pm
Hi Lockwelle
 
Thanks for your reply. I will try to elaborate coherently. I need to link 'receiptno'. This is made up of {rec_no} '12345' and {rec_seq_no} '1'. I need to link this to {document} 12345/1' in a different table. {document} only appears once in the database, all other references are {rec_no} and {rec_seq_no} The bulk of the info I need to display on my report is in the same table as {document}.  When I first concatenated the two fields it displayed '12345/$1.00" and I created a formula for it to display how I wanted. I assume this is what you called 'trimmed'.
 
I will have a look at stored procedures as DBlank suggested and see how I go.
 
Thanks very much
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
Quote lockwelle Replybullet Posted: 31 Jul 2009 at 6:16am

What you've done makes sense, now how to join.

I do everything in stored procs, so I would say that is the way to go, but I understand DBlank's suggestion, and if you know something about SQL, and don't want to/can't make a stored proc, it is a viable solution...with caveats, that are probably doable.
 
For a command object...go to database, set database locations (or database expert) look at the connection that you are using, right under the name should be a 'Add Command'.  This allows you to add a SQL command, like "select * from table".  Here you could select everything from the first table AND a concatenated field of rec_no and rec_seq_no.
 
Then in the links tab, link to this new command object table to the table with document.
 
The caveats are, I think (I don't use this portion of Crystal), that parameters might be an issue (probably not...the command object, I believe was made to support the selection of parameter options, but we're not using it in that context here)...it's subtle, but if you filter records out of the dataset via the Report/Selection Formulas it will probably be fine....you'll find out if there are issues or not.
 
Hope this helps.
IP IP Logged
duffey01
Newbie
Newbie
Avatar

Joined: 04 May 2009
Location: Australia
Online Status: Offline
Posts: 39
Quote duffey01 Replybullet Posted: 02 Aug 2009 at 4:59pm

Hi lockwelle

It's reassuring to know that I'm doing something right. I will look at the Add Command and let you know how I go.

 

Thanks very much

 
IP IP Logged
duffey01
Newbie
Newbie
Avatar

Joined: 04 May 2009
Location: Australia
Online Status: Offline
Posts: 39
Quote duffey01 Replybullet Posted: 03 Aug 2009 at 9:12pm
Dblank
 
I have a bit more info now about 'add command' I am asking too much if you could please expand on replacing one table and joining the command to the other??
 
Thanks in advance
IP IP Logged
duffey01
Newbie
Newbie
Avatar

Joined: 04 May 2009
Location: Australia
Online Status: Offline
Posts: 39
Quote duffey01 Replybullet Posted: 03 Aug 2009 at 9:21pm
Ignore me Dblank, I looked elsewhere in the forum and found a previous post that tackles a similar problem. I will try that and see how I go.
 
Thanks
duffey
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.