Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design
Message Icon Topic: Linking 1 to many to 1 Post Reply Post New Topic
Author Message
Tomsss
Newbie
Newbie


Joined: 02 Jul 2009
Location: Canada
Online Status: Offline
Posts: 20
Quote Tomsss Replybullet Topic: Linking 1 to many to 1
     Posted: 09 Sep 2009 at 8:00am
I am having a problem linking my tables correctly.
 
Basiclly there is one table with information-Service call table).
 
There is a second table linked to the first by service call ID.
 
This table has tasks that have been assigned to the serivce call. There can be one or many tasks. Each task has the same Equipment ID assigned to it.
 
A thrid table contains Equipment Information. It is linked to the second table by Equipment ID as the first table does not have the equipment ID.
 
There is a forth table that contains costs which is linked to the first table by Service call ID.
 
If I then do a grouping based on Equipment ID in the Equipment information table  the costs from the last table are duplicated based on the number of tasks in table 2.
 
Is there any way to fix this in the linking?
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
Quote hilfy Replybullet Posted: 09 Sep 2009 at 11:22am
You would have this same issue if you ran all of this in a single SQL statement - there's no way I know of to link these tables and get a single cost.  Assuming that you want to add the costs together and get the correct total cost, there is a way do that.
 
1.  Create a Cost formula that looks something like this:
 
If PreviousIsNull({Table1.CallID}) or {Table1.CallID} <> Previous {Table1.CallID} then {Table4.Cost} else 0.
 
2.  Sum this formula instead of the cost field.
 
Basically what the formula does,  is it only gives you the Cost value if you're on the first CallID (PreviousIsNull()) or the current CallID is not the same as the CallID in the previous record.  So, you'll only get the cost once per CallID.
 
-Dell
IP IP Logged
Tomsss
Newbie
Newbie


Joined: 02 Jul 2009
Location: Canada
Online Status: Offline
Posts: 20
Quote Tomsss Replybullet Posted: 09 Sep 2009 at 1:44pm
will give it a try thank you.
IP IP Logged
Tomsss
Newbie
Newbie


Joined: 02 Jul 2009
Location: Canada
Online Status: Offline
Posts: 20
Quote Tomsss Replybullet Posted: 10 Sep 2009 at 7:35am
work like a charm (though you forgot a (   ) in the previous area). lol. Thank you very much.
IP IP Logged
Tomsss
Newbie
Newbie


Joined: 02 Jul 2009
Location: Canada
Online Status: Offline
Posts: 20
Quote Tomsss Replybullet Posted: 10 Sep 2009 at 10:11am
I cannot total this field. If I right click  summary or running total is not avialable and if I try to ad it under running total this formula is not available.
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
Quote hilfy Replybullet Posted: 14 Sep 2009 at 6:33am
Ok, try it this way:
 
NumberVar totalCost;
If PreviousIsNull({Table1.CallID}) then
  totalCost := {Table4.Cost}
else if {Table1.CallID} <> Previous {Table1.CallID} then
  totalCost := totalCost  + {Table4.Cost} ;
totalCost
 
-Dell
 
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