| Author |
Message |
duck
Newbie
Joined: 29 Apr 2009
Online Status: Offline
Posts: 8
|
  
Topic: Compare returned records
Posted: 04 Jun 2009 at 11:18am |
|
Hello,
How would I go about comparing returned records to one another? I'm creating a AR report and need to know if there is more than one invoice per customer with the same invoice number. If there is, then I need to sum the invoice amounts and any payments that have been made. I'm having trouble finding a way to compare the records.
Results:
Invoice # Invoice Amount Payments
123456 $5000.00 $1250.00
123232 $60.00 $0
123456 $5000.00 $1000.00
Invoice 123456 and any payments would need to be summed and show as:
Invoice # Invoice Amount Payments
123456 $5000.00 $2250.00
123232 $60.00 $0
Any help is greatly appreciated...
|
 IP Logged |
|
|
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 04 Jun 2009 at 11:28am |
If it is just a n issue of displaying,
Group on Invoice #
Create 2 formula fields (or Summary Fields) as SUMs on Group 1
SUM(table.invoice amount,table.invoice#)
SUM(table.payments,table.invoice#)
Place both formulas on the group header next to the invoice #.
suppress your detail row
|
| IP Logged |
|
duck
Newbie
Joined: 29 Apr 2009
Online Status: Offline
Posts: 8
|
Posted: 04 Jun 2009 at 11:49am |
|
I like the line of thinking on that solution. There is one wrinkle, the report needs to show invoices for each customer; there can be many customers per report. When I added the grouping as you suggested, the formula summed for all invoices on the report, as follows:
Customer A
123456 $5000.00 // group header, sum of all invoices
123456 $3000.00 // detail
Customer B
123435 $5000.00 // group header, sum of all invoices
123435 $2000.00 // detail
|
| IP Logged |
|
duck
Newbie
Joined: 29 Apr 2009
Online Status: Offline
Posts: 8
|
Posted: 04 Jun 2009 at 11:58am |
|
I'm trying some different combinations on the grouping; I just have to find the right one.
Thanks for the help!
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 04 Jun 2009 at 12:00pm |
NOt sure I am seeing the issue here...Assuming a invoice number always belons under 1 customer you can group at 2 levels.
Group 1 =Customer
Group2 = Invoice #
Details - suppressed
Create SUMs for Invoice amounts and Payments at group level 2 . PLace on GH2.
Result should be
John SMith
123456 $5000.00 $2250.00
123232 $60.00 $0
Jane Doe
123123 1,000,000.00 0.
etc.
|
| IP Logged |
|
duck
Newbie
Joined: 29 Apr 2009
Online Status: Offline
Posts: 8
|
Posted: 04 Jun 2009 at 12:24pm |
|
The last issue is there is one record for each customer and invoice. How can I get all the invoices in GH2 to show under GH1? Thanks again...
This is how it now shows:
Customer Invoice Amount
----------------------------------------
Joe 123456 $5000.00
----------------------------------------
Joe 234556 $3500.00
----------------------------------------
Bob 532342 $1500.00
----------------------------------------
Bob 654231 $2300.00
----------------------------------------
And it needs to be:
Customer Invoice Amount
----------------------------------------
Joe 123456 $5000.00
Joe 234556 $3500.00
----------------------------------------
Bob 532342 $1500.00
Bob 654231 $2300.00
----------------------------------------
|
| IP Logged |
|
duck
Newbie
Joined: 29 Apr 2009
Online Status: Offline
Posts: 8
|
Posted: 04 Jun 2009 at 12:29pm |
|
Got it...it was my record sort. It's my first real report to be used for AR and I'm still getting familiar with CR.
|
| IP Logged |
|
duck
Newbie
Joined: 29 Apr 2009
Online Status: Offline
Posts: 8
|
Posted: 05 Jun 2009 at 6:08am |
|
I also need to show a customer's outstanding balance for a period, say 31-45 days.
//Non-sum field // sum field // formula @31-45days
InvoiceAmount - Payments = OutstandingBalance
How can I calculate the outstanding balance if Payments is a sum field?
Then I need to sum all OutstandingBalance fields for the period. Sum (@31-45days). Currently, if there is one invoice with multiple payments, @31-45days with only show one payment, but sum(@31-45days) will add the payment twice.
|
| IP Logged |
|
|
|
