Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: Table/Link Issue Post Reply Post New Topic
Author Message
CarlK
Newbie
Newbie
Avatar

Joined: 28 Jul 2010
Location: United States
Online Status: Offline
Posts: 13
Quote CarlK Replybullet Topic: Table/Link Issue
     Posted: 20 Sep 2010 at 8:34am
Hopefully this will be the last of my Newbish questions.
 
I have two tables with mainly unrelated data that I want on the same report.  When I combine them by their common denominator (I do so manually since the names are slightly different) I receive multiples of each of the entries.
 
To be more specific; I am joining by an item number but both tables have multiple entries each for each item number.  If one of the tables has 3 entries for a certain item, and the other has 5 for example the data for the first table will repeat itself for each of the entries in the other table and vice versa.  I have been playing around with how I link the tables, but this only seem to affect item numbers that one table or the other may not have.
 
Ideally I would like a result that has the full number of entries from each of the tables where entries from the second are null for those of the first.
 
My objective is to project a forecast for demand for these item numbers and keep track of the purchase orders my company has coming in on one report where I presently have them on two.  I would greatly appreciate it if someone could either help me with this issue or suggest a better manner in which to accomplish this.
 
 
Thank you
IP IP Logged
Senthil Raj
Newbie
Newbie
Avatar

Joined: 02 Aug 2010
Location: India
Online Status: Offline
Posts: 27
Quote Senthil Raj Replybullet Posted: 21 Sep 2010 at 12:51am
use left outer join for this problem
and
try to map other similar (key) fields also to avoid multiple mapping
Live And Let Live...
IP IP Logged
CarlK
Newbie
Newbie
Avatar

Joined: 28 Jul 2010
Location: United States
Online Status: Offline
Posts: 13
Quote CarlK Replybullet Posted: 21 Sep 2010 at 2:46am
I have tried different joining strategies and just tried that again, it's not working.  Maybe I need to provide some more information.
 
I have 2 different sets of formula's summing the data for different periods based on date.  Each of these are going off of their own data set and the only real link between these fields is the item number.  The other thing I believe I negected to mention was that there are other tables mixed into this, I just am not using them in a data area and they do not have multiple entries.
 
Also I don't believe and of the other fields from the tables have information in the that would allow me to link them.
IP IP Logged
CarlK
Newbie
Newbie
Avatar

Joined: 28 Jul 2010
Location: United States
Online Status: Offline
Posts: 13
Quote CarlK Replybullet Posted: 23 Sep 2010 at 5:34am
Let me try stating what I want to do in a manner that might be clearer here.  I would like to draw fields from my report to be created from one table only without having any effect on data in the other table/tables at all. 
 
It seems to me that whenever I add in a table no matter what link structure I have thought to attempt it joins the tables in some manner that always seems to make data repeat and therefore mess up my formulas.  I would like to use data from one table or the other without any sort of joining being done, and I want them linked by one entry "Item Number."
 
I'm hoping to learn if this is possible, or a possible manner to circumvent my issue in either the joining or a selection/formula of some sort.
 
 
Thank You
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 23 Sep 2010 at 6:26am

you pretty much have to link

if you the data that youa re using in the report has no difference from row to row go into FILE > REPORT OPTIONS and mark 'Select Distinct Records' as TRUE
IP IP Logged
CarlK
Newbie
Newbie
Avatar

Joined: 28 Jul 2010
Location: United States
Online Status: Offline
Posts: 13
Quote CarlK Replybullet Posted: 23 Sep 2010 at 7:46am
I've tried selecting destinct records, all that seems to do is make the problem a minute fraction smaller and I'm not sure if the data its killing is good or bad, I'll have to dig in a bit more to see.   but it's looking like I can't find any way to solve this?  I suppose I may just have to stick with multiple reports to do this or find a solution outside of crystal.  I was hopin to get these on one page so I could add in some formula's to compare the results I'd generated from these tables automatically rather than manually.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 23 Sep 2010 at 7:57am
the select distinct only removes duplicate rows where all fields used in the report (not just shown in the canvas but used anywhere) are exactly the same.
YOu can likely do the calculations you want using Running Totals to only calculate on the rows you tell it to rather than going to other extremes.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 23 Sep 2010 at 7:58am
You will also likely want to learn how to handle cal;cualtions when seen dupes because this sort of thing happens a lot.
IP IP Logged
CarlK
Newbie
Newbie
Avatar

Joined: 28 Jul 2010
Location: United States
Online Status: Offline
Posts: 13
Quote CarlK Replybullet Posted: 24 Sep 2010 at 3:29am
I thought I just posted but I guess I must have hit the wrong button, anyway, here's the jist of it.
 
Where can I learn how to handle the calculations when dupes are present and what manner I'd have to use running totals in to get the info I'm looking for?  When I started I purchased a book, but that has proved useful for not much beyond getting me started up as little of the information in it is specific enough or detailed enough to give more than the general idea of how to do anything other than a basic report.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 24 Sep 2010 at 12:14pm
Ahh , you did the dreaded post reply outside the box and wiped out your post. Gotta love that. I always seem to do it on a particularly complicated answer.
As for how to learn it I recommend 'playing' with the RTs in sandbox reports to get a feel for them. Just my experience but they do seem to confuse the heck out of people. I on the other hand get easily confused by shared variables. I started reading all th posts that I could and then tried to mimic both the problem and the solution(s). My experience is that once i played with the RTs and got a feel for them you can get most solutions by using them in combination with formulas in the evaluate.
The first thing you need to know with RTs is that they only work on detials or footers. They act more like a when printing record formula so they cannot calculate a row that is below where you place it on the report.
Here is a classic example of how RT can save you a lot of time.
Say you have a table of patients with there patient id, demographic data and one row per doc visit. You need to do something as simple as get a count of how many are male and how may are female.
Not see with all the dupes of the patient rows because of multiple visits.
2 RTs fix it right away.
Each one is a distinctcount of the patientid with an evaluate formula where gender='M" for one and gender="F" for the other.
IP IP Logged
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