Active TopicsMemberlistSearchHelpRegisterLogin

Data Connectivity
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Data Connectivity
Message Icon Topic: CR XI joining Goldmine MDF tables Post Reply Post New Topic
Author Message
quakerman
Newbie
Newbie
Avatar

Joined: 23 Mar 2007
Location: United States
Online Status: Offline
Posts: 2
Quote quakerman Replybullet Topic: CR XI joining Goldmine MDF tables
    Posted: 23 Mar 2007 at 7:05pm
New to CR.  Opened 2 Goldmine tables, 'Contact1' and 'ContactHist', 1-to-many, on [AccountNo], a 16 character unique ID.
 
CR XI autojoined AccountNo (should be common to both) and RecID (should be unique to each).  I eliminated the 2nd join.  Tried every combination of joins and report construction and got the following.
 
For each Contact1.AccountNo I received display on only 1 matching ContactHist.AccountNo, namely, the first.  For inner-joins I got 2 records.  For left outer joins, I got 3300 records, but only detail on the first match.  If I filtered for other known matches, then of course I got the ContactHist.AccountNo info for that Contact1.AccountNo.  Otherwise, page after page of NULL set when doing outer joins.  Same difficulties in reverse except the record count was 72,000.
 
I converted the *.mdf files to .xls and performed a VLOOKUP, returning a confirmed 450+ matches on [AccountNo] between the 2 tables.
 
What is going on? 
 
Other Info: 
* CR XI opening up a prior report done in CR 8 returns zero records (params: DtStart,DtEnd,TechID) but when run through a CR8 executable it is fine.
* If I pull records on EITHER the Contact1 OR the ContHist to a CR11 list report is it fine.  Only when I join on AccountNo does it go wrong.
 
quakerman
IP IP Logged
BrianBischof
Admin Group
Admin Group
Avatar

Joined: 09 Nov 2006
Online Status: Offline
Posts: 2458
Quote BrianBischof Replybullet Posted: 24 Mar 2007 at 5:25pm
I don't know anything about Goldmine. But it's my guess that somehow CR isn't as compatible with it as advertised. Somehow, as you stated, it seems to only link on the first record? Very strange. I wonder if there might be some hidden characters in the field that isn't obvious? Sometimes there are filler spaces used on a field in one table but not the other. So you visually can't tell that they aren't exact. I've had that happen to me A LOT with data input by users. I'll run a routine to trim out spaces and them like magic the report works again.

You might try exporting to a text file and seeing if you notice something different about the data that you didn't notice the first time.

If the data is static and you just need this report for now, I would export the data a CSV file and then report off the CSV files. Forget trying to use Goldmine data in its native format and dealing with any possible bugs in the database driver.
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
quakerman
Newbie
Newbie
Avatar

Joined: 23 Mar 2007
Location: United States
Online Status: Offline
Posts: 2
Quote quakerman Replybullet Posted: 24 Mar 2007 at 9:15pm
Yes.  Strange.  Fields posted to the report from both sides of the join show under only 2 conditions, in all other cases they exist and are clickable but are unpopulated:
*  the first record
*  any known AccountNo match that is specifically filtered for via the Select Expert - in which case there is only 1 or 2 records anyway.
 
Opening up the MDF tables (Contact1 & ContHistory, 1-M) in Excel seems to clean up the issue as far as white spaces goes.  A VLOOKUP function between these two sheets returns 450+ records, joining 3300 and 72000 records respectively.  That's a lot better than 1.
 
Maybe I can trick Crystal Report XI's connectivity hiccup (if that's what it is here).  If I link the MDF files into Access, I can do the CR XI joins out of the Access database using Access/Excel (DAO) connection in the Database Expert.  I'm much more familiar with Access & SQL anyway.
 
It would still be live data.
 
One final question:  in CR XI, would I ever expect to see the one-to-many symbols of 1 and 'infinity' anywhere on a table join links, like I would see then in an Access or SQL relationship chart?
quakerman
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.016 seconds.
HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | CLICK PLAYS | COACH FORUMS | TROPICAL FISH

© 2004-2024 Brian Bischof. All Rights Reserved.