Talk with the Author
 Crystal Reports Forum : General Information : Talk with the Author
Message Icon Topic: Duplicate Values Post Reply Post New Topic
Page  of 2 Next >>
Author Message
Odinsisfet
Newbie
Newbie
Avatar

Joined: 15 Dec 2009
Location: United States
Online Status: Offline
Posts: 7
Quote Odinsisfet Replybullet Topic: Duplicate Values
    Posted: 15 Dec 2009 at 7:19pm
I am creating a report that uses the Sales Database for a class.  I am including the InvoiceNum, InvoiceDate, and AmountBilled.  I am grouping by Account Number and Invoice Number.  When I add the payment amount field to the report I get duplicate invoice number fields with multiple payment amounts.  How can I combine these payment amounts to reflect one payment amount?  I need to subtract the payment amount from the billed amount to get the amount due field.  I cannot do this effectively without combining all payment amount fields. 

Any help would be appreciated.

Example:

< ="Content-" content="text/; charset=utf-8">< name="ProgId" content="Word.">< name="Generator" content="Microsoft Word 12">< name="Originator" content="Microsoft Word 12"><>

INUM               IDATE                                  AMOUNT              PAY_AMT

211030518901

      00101        12/16/2002  12:00:00AM        3,455.55                3,450.00

      00102        1/18/2003  12:00:00AM            222.44                   665.00

      00103        3/4/2003  12:00:00AM           4,567.00                4,540.00

      00202        12/26/2002  12:00:00AM        5,565.00                   700.00

      00203        1/30/2003  12:00:00AM          5,666.00                   756.00

      00204        3/7/2003  12:00:00AM           7,777.00                   600.00

      00204        3/7/2003  12:00:00AM           7,777.00                   700.00

      00205        3/23/2003  12:00:00AM            222.00                   222.00

      33347        11/13/2002  12:00:00AM        8,889.00                2,000.00

      33347        11/13/2002  12:00:00AM        8,889.00                1,000.00

      33347        11/13/2002  12:00:00AM        8,889.00                   500.00

      44455        2/28/2003  12:00:00AM          4,523.00                4,546.00

      44456        3/16/2003  12:00:00AM          2,223.00                   500.00

      45646        4/17/2003  12:00:00AM          3,334.00                3,000.00

      45646        4/17/2003  12:00:00AM          3,334.00                   600.00

      55544        1/19/2003  12:00:00AM          4,546.00                3,000.00

      56342        12/21/2002  12:00:00AM        1,112.00                1,112.00

      88844        12/24/2002  12:00:00AM        4,545.00                4,500.00

 


Jen
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3701
Quote hilfy Replybullet Posted: 16 Dec 2009 at 10:58am
Since this is for a class, I can't give you the exact answer.  However, to point you in the right direction, you need to look at the Summary functions, in particular, SUM.  You can use these in formulas in addition to inserting summaries directly into the report.
 
 
-Dell
IP IP Logged
Odinsisfet
Newbie
Newbie
Avatar

Joined: 15 Dec 2009
Location: United States
Online Status: Offline
Posts: 7
Quote Odinsisfet Replybullet Posted: 16 Dec 2009 at 12:28pm
I already have a summary field that looks at the payment amount by the group invoice number.  It will summarize the payment amount by invoice.  I still have three fields in my details section with the exact same summarized amount under the same invoice number.  How can I get it to only display one amount per invoice?  I enev tried averaging the payment amount and then subtracting the sum form that.  Still three fields when I only want one!  Here is what it looks like now:
 
The duplicated fields I only want to display once, so my summary fields in my groop footer are accurate.
 
INVOICE NUMBER INVOICE DATE AMOUNT Amount Paid AMOUNT DUE
00101  12/16/02 $3,455.55 $3,450.00 $5.55
00102  1/18/03 $222.44 $665.00 $-442.56
00103  3/4/03 $4,567.00 $4,540.00 $27.00
00104  3/15/03 $7,776.00 $0.00 $7,776.00
00202  12/26/02 $5,565.00 $700.00 $4,865.00
00203  1/30/03 $5,666.00 $756.00 $4,910.00
00204  3/7/03 $7,777.00 $1,300.00 $6,477.00
00204  3/7/03 $7,777.00 $1,300.00 $6,477.00
00205  3/23/03 $222.00 $222.00 $0.00
23345  6/5/03 $4,445.00 $0.00 $4,445.00
33347  11/13/02 $8,889.00 $3,500.00 $5,389.00
33347  11/13/02 $8,889.00 $3,500.00 $5,389.00
33347  11/13/02 $8,889.00 $3,500.00 $5,389.00
44455  2/28/03 $4,523.00 $4,546.00 $-23.00
44456  3/16/03 $2,223.00 $500.00 $1,723.00
45646  4/17/03 $3,334.00 $3,600.00 $-266.00
45646  4/17/03 $3,334.00 $3,600.00 $-266.00
56342  12/21/02 $1,112.00 $1,112.00 $0.00
77777  7/25/03 $254.33 $0.00 $254.33
88844  12/24/02 $4,545.00 $4,500.00 $45.00
Customer Balance $75,789.32 29,391.00
 
Suppressing is not enough because it still sums the fields when suppressed.  Thus, my totals are off.


Edited by Odinsisfet - 16 Dec 2009 at 12:30pm
Jen
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3701
Quote hilfy Replybullet Posted: 16 Dec 2009 at 12:46pm

On the Database menu, have you tried turning on "Select Distinct Records"?

-Dell

IP IP Logged
Odinsisfet
Newbie
Newbie
Avatar

Joined: 15 Dec 2009
Location: United States
Online Status: Offline
Posts: 7
Quote Odinsisfet Replybullet Posted: 16 Dec 2009 at 2:04pm
Yes, I did try that.  The workaround I found is to place the information from the details section into the group footer.  It doesn't duplicate the data then.
Jen
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3701
Quote hilfy Replybullet Posted: 17 Dec 2009 at 6:22am
But your totals are still off, correct?
 
If so, you have to create a formula that eliminates the duplicates.  It will look something like this:
 
If ({table.invoice#} = previous({table.invoice#})) and ({table.amount}) = previous({table.amount})) then 0 else {table.amount}
 
You then do the summary on this formula instead of on the field.  This basically looks to see if the previous record has the same invoice number and amount as the current one, which makes the current one a duplicate.  If it's a duplicate, then set the value to 0 so that it's not added in more than once.
 
-Dell
IP IP Logged
Odinsisfet
Newbie
Newbie
Avatar

Joined: 15 Dec 2009
Location: United States
Online Status: Offline
Posts: 7
Quote Odinsisfet Replybullet Posted: 17 Dec 2009 at 7:33am
Actually, all of my totals were correct.  I did a running total on the Billed amount field, a summary on the Amount paid field and then a formula on the amount due field that took the running total-amount paid summary.  They all came out with appropriate numbers.  I submitted the assignment and got an A, so i figured it was all good!
Thank you for your help!

Edited by Odinsisfet - 17 Dec 2009 at 7:34am
Jen
IP IP Logged
kmeistering
Newbie
Newbie


Joined: 19 May 2010
Location: United States
Online Status: Offline
Posts: 10
Quote kmeistering Replybullet Posted: 13 Oct 2010 at 5:07am
I have used this solution to help me in a report, and it worked to eliminate the duplicates, however, now I cannot sum the fields because the formula is a second pass formula.  I need to eliminate the duplicates and sum the fields - can someone help?!?
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3701
Quote hilfy Replybullet Posted: 13 Oct 2010 at 5:18am
Try using a running total instead of a sum.  Set it to evaluate based on a formula, using the same logic of looking at the previous record outlined above - using the example above the formula would look something like this:
 
{table.invoice} <> previous({table.invoice}) or {table.amount} <> previous({table.amount})
 
You don't need the "If...then..." contstruct because this formula just needs to evaluate to True or False.
 
-Dell
IP IP Logged
kmeistering
Newbie
Newbie


Joined: 19 May 2010
Location: United States
Online Status: Offline
Posts: 10
Quote kmeistering Replybullet Posted: 13 Oct 2010 at 5:29am
If you were right here, I would kiss you.
IP IP Logged
Page  of 2 Next >>
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.032 seconds.