Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Technical Questions
Message Icon Topic: Linking fields -> one is null the other not Post Reply Post New Topic
Author Message
Zorgov
Newbie
Newbie
Avatar

Joined: 23 Apr 2008
Location: Canada
Online Status: Offline
Posts: 12
Quote Zorgov Replybullet Topic: Linking fields -> one is null the other not
    Posted: 07 May 2008 at 9:34am
Hi everyone,
 
I am linking two tables by complete names­. The thing is one of the names was erase from the database so the the data associate to that "blank" field is not showing anymore (because of the linking).
 
My question is :
 
Let's say the Name is John Smith and many data are associate to that name in one table. In the other table, different data are without a name (but are suppose to be related to that name). What I would want to do is to tell Crystal that if a data from one table is not found into the other table, to put this data in an "other" field, or something like that.
 
exemple
I have 5 names in one table and 4 names in the other. I want to do a link between the 4 names (that are equal) and the fifth should show in the report as well...
 
I hope my situation is clear enough! Can you help me?
IP IP Logged
BrianBischof
Admin Group
Admin Group
Avatar

Joined: 09 Nov 2006
Online Status: Offline
Posts: 2458
Quote BrianBischof Replybullet Posted: 07 May 2008 at 10:25am
You can do a left outer join on the tables (under the Links tab of the Database Expert). This way, all the fields from the leftmost table are selected even if there isn't a match with a field in the right most table.
Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a>
IP IP Logged
Zorgov
Newbie
Newbie
Avatar

Joined: 23 Apr 2008
Location: Canada
Online Status: Offline
Posts: 12
Quote Zorgov Replybullet Posted: 07 May 2008 at 11:32am
Nice it's working perfect for me! Thanks a lot Brian. Seems I need to learn a lot more in crystal LOL.
IP IP Logged
BrianBischof
Admin Group
Admin Group
Avatar

Joined: 09 Nov 2006
Online Status: Offline
Posts: 2458
Quote BrianBischof Replybullet Posted: 07 May 2008 at 12:37pm
Yep, there is always PLENTY to learn about Crystal Reports. In fact, if you check out my Encyclopedia book, you'll have more than enough information to keep you busy learning all of its features. 
Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a>
IP IP Logged
Zorgov
Newbie
Newbie
Avatar

Joined: 23 Apr 2008
Location: Canada
Online Status: Offline
Posts: 12
Quote Zorgov Replybullet Posted: 08 May 2008 at 5:55am

Finaly, it's not working as I tought...

The problem is that i see the data from the left outer join, but it's not associated to the right data. I would want something like this :
 
if you don't find data from the left outer join, put all those not find data into a section "others"
 
for example :
 
In one table I have John(1,2,3) / Mary(1,2,3) / Carl(1,2,3) and the other table I have John(3,4,5) / Carl(3,4,5) / (3,4,5) ... you see that in my second table, the third data don't have a name. But clearly i would want that John data from one table is link with the other, Carl data same thing, and the noname data should be associate to Mary data in a "other" field.
 
The result shoud look like this :
John(1,2,3,4,5,6) / Carl(1,2,3,4,5,6) / Other(1,2,3,4,5,6)
 
I hope my example will help you understand my problem!
IP IP Logged
Lugh
Senior Member
Senior Member
Avatar

Joined: 14 Nov 2007
Online Status: Offline
Posts: 377
Quote Lugh Replybullet Posted: 08 May 2008 at 6:08am
When you do an outer join, any fields from the table that doesn't have a matching record are simply NULL.  So, the simple solution is to check for NULL values, and replace them with "OTHER."  This can be done through a formula, possibly through a Top N grouping (depending on your data and what you want to show), or by defining a default value and having Crystal show the default instead of NULL.

Now, if you want Crystal to know that, if Mary doesn't have any records in Table B, and there are records in Table B that don't have a name, then those records belong to Mary....  Well, that's a lot trickier.  In a single case, it's relatively easy.  Check for NULLs on each side, and match them up.  But, what happens if there are two names that don't have matching records?  How do you sort out which ones go where?  In that case, I think your efforts would honestly be better spent cleaning up your data before it ever reaches Crystal.  Because, while I imagine it could be done, it's going to be a tough row to hoe.


IP IP Logged
Zorgov
Newbie
Newbie
Avatar

Joined: 23 Apr 2008
Location: Canada
Online Status: Offline
Posts: 12
Quote Zorgov Replybullet Posted: 08 May 2008 at 6:32am
It is exactly what I think! it's a tricky case. But lets say I would want to simply put all the not matched cases together. So lets say :
 
Table A : John(1,2,3) / Carl(1,2,3) / Mary (1,2,3)
Table B : John(4,5,6) / (4,5,6) / (4,5,6)
Results shoud read : John(1,2,3,4,5,6) / Others(1,2,3,8,10,12)
 
How could a clear the data in that case, I could easily do this manually, but I would want to find a way that I don't have to clean up each time.
 
Thanks for you help! I hope you will be able to help me on this one too!
IP IP Logged
Lugh
Senior Member
Senior Member
Avatar

Joined: 14 Nov 2007
Online Status: Offline
Posts: 377
Quote Lugh Replybullet Posted: 08 May 2008 at 7:05am
I think the simplest solution would be to create a formula that tested for NULLs.

IF ISNULL({MyReport.AName}) OR ISNULL({MyReport.BName}) THEN
    "Other"
Else
    {MyReport.AName}

Then simply group on this formula.  This will, I think, give you the results you want.  You can additionally create another group on AName that will help sort out the results under "Other" if you'd like.


IP IP Logged
Zorgov
Newbie
Newbie
Avatar

Joined: 23 Apr 2008
Location: Canada
Online Status: Offline
Posts: 12
Quote Zorgov Replybullet Posted: 08 May 2008 at 8:08am
Thanks a lot, seems like there are exceptions in the database that I need to take in consideration. I will think about all this and verify the NULLs at the same time.
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.029 seconds.