Author |
Message |
Zorgov
Newbie
Joined: 23 Apr 2008
Location: Canada
Online Status: Offline
Posts: 12
|
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 Logged |
|
BrianBischof
Admin Group
Joined: 09 Nov 2006
Online Status: Offline
Posts: 2458
|
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 Logged |
|
Zorgov
Newbie
Joined: 23 Apr 2008
Location: Canada
Online Status: Offline
Posts: 12
|
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 .
|
IP Logged |
|
BrianBischof
Admin Group
Joined: 09 Nov 2006
Online Status: Offline
Posts: 2458
|
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 Logged |
|
Zorgov
Newbie
Joined: 23 Apr 2008
Location: Canada
Online Status: Offline
Posts: 12
|
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 Logged |
|
Lugh
Senior Member
Joined: 14 Nov 2007
Online Status: Offline
Posts: 377
|
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 Logged |
|
Zorgov
Newbie
Joined: 23 Apr 2008
Location: Canada
Online Status: Offline
Posts: 12
|
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 Logged |
|
Lugh
Senior Member
Joined: 14 Nov 2007
Online Status: Offline
Posts: 377
|
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 Logged |
|
Zorgov
Newbie
Joined: 23 Apr 2008
Location: Canada
Online Status: Offline
Posts: 12
|
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 Logged |
|
|