Active TopicsMemberlistSearchHelpRegisterLogin

Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design
Message Icon Topic: Duplicate Records Post Reply Post New Topic
Author Message
Odinsisfet
Newbie
Newbie
Avatar

Joined: 15 Dec 2009
Location: United States
Online Status: Offline
Posts: 7
Quote Odinsisfet Replybullet Topic: Duplicate Records
     Posted: 15 Dec 2009 at 7:27pm
I am creating a report in Crystal 2008.  I have several fields regarding an invoice ie: invoicenum, date etc.  When I add the payment amount to the report it brings in multiple payments as individual fields.  I want to combine them as one.  How do I go about doing this?  I am grouping by Account number and Invoice number already.

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

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

210110231201

      00666        2/13/2003  12:00:00AM          1,000.00                   900.00

 

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


HELP!

 
Jen
IP IP Logged
Freek
Newbie
Newbie
Avatar

Joined: 16 Dec 2009
Location: Netherlands
Online Status: Offline
Posts: 8
Quote Freek Replybullet Posted: 16 Dec 2009 at 2:46am
I think the easiest way to solve this is use groups (group by customer number) and sum the amounts (make a formula for this).
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 5:04am
Originally posted by Freek

I think the easiest way to solve this is use groups (group by customer number) and sum the amounts (make a formula for this).


I already have two groups; account number and Invoice Number, and I have tried using a formula that sums the payment amount by Invoice Number Sum({Payments.PAY_AMT},{Invoices.INUM}).  So unless there is another formula I can use, I'm still stuck!
Jen
IP IP Logged
Freek
Newbie
Newbie
Avatar

Joined: 16 Dec 2009
Location: Netherlands
Online Status: Offline
Posts: 8
Quote Freek Replybullet Posted: 16 Dec 2009 at 6:52am
I think you want to sum the PAY_AMT by customer number.

The way you did your sum() it stops summing PAY_AMT's as soon as the invoice number changes, while you want it to stop summing when the customer-number changes.
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 7:46am
No.  I want it to sum by each Invoice Number and then another sum for the Account number which I already have.  Unfortunately the Invoice Number sum shows up multiple times and the Account number sum is not accurate.  If you look at my first post I did the Sum() function to combine the payment amounts that were showing up on the report.  Once I applied the formula it combined the amounts shown, but still shows them in multiple lines.  How do I get rid of the multiple lines so my summary by account number is accurate?  This is what I want it to look like, but with appropriate summaries.

INVOICE NUMBER INVOICE DATE AMOUNT BILLED 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
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
00205  3/23/03 $222.00 $222.00 $0.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 $1,000.00 $1,223.00
45646  4/17/03 $3,334.00 $3,600.00 $-266.00
55544  1/19/03 $4,546.00 $6,000.00 $-1,454.00
56342  12/21/02 $1,112.00 $1,112.00 $0.00
88844  12/24/02 $4,545.00 $4,500.00 $45.00

Customer Balance $75,789.32 $59,887.00 $52,791.32

Jen
IP IP Logged
Freek
Newbie
Newbie
Avatar

Joined: 16 Dec 2009
Location: Netherlands
Online Status: Offline
Posts: 8
Quote Freek Replybullet Posted: 16 Dec 2009 at 8:02am
Ah now i think i'm starting to understand. You could try editing the query used to generate the records and using 'distinct' in it, so you will only get each line once.
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

HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | PRIVACY | CLICK PLAYS | BAEZL

© 2004-2026 Brian Bischof. All Rights Reserved.