Print Page | Close Window

CR XI joining Goldmine MDF tables

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Data Connectivity
Forum Discription: How to connect to data sources and export reports
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=395
Printed Date: 30 Apr 2024 at 8:55pm


Topic: CR XI joining Goldmine MDF tables
Posted By: quakerman
Subject: CR XI joining Goldmine MDF tables
Date 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


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

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


Print Page | Close Window