Print Page | Close Window

Linking fields -> one is null the other not

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=3122
Printed Date: 29 Apr 2024 at 7:09pm


Topic: Linking fields -> one is null the other not
Posted By: Zorgov
Subject: Linking fields -> one is null the other not
Date 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?



Replies:
Posted By: BrianBischof
Date 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>


Posted By: Zorgov
Date 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.


Posted By: BrianBischof
Date 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>


Posted By: Zorgov
Date 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!


Posted By: Lugh
Date 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.




Posted By: Zorgov
Date 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!


Posted By: Lugh
Date 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.




Posted By: Zorgov
Date 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.



Print Page | Close Window