Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 284
  • Last Modified:

Billing Statement in Crystal Reports with SQL DB - handling a partial payment

I am creating a billing statement from a SQL DB using Crystal Reports, and need to come up with a way to handle multiple payments for one invoice. The attached statement demonstrates my problem. I'm assuming I need to create a view in the SQL DB, but need help. Here is my thought process:
1. Apply the parameter to select the correct job number from [jobs.job_no] (this is working fine in my Crystal select with a parameter)
2. Get the full invoice amount from [ar_invoice.invoice_amount]
3. Check [ar_cash_receipts.cash_receipt] to see if there is a payment
4. If there is a payment in full, then apply the payment value and the resulting balance due is 0
5. If there is a balance due after a partial payment is applied, then create a result with the value of the remaining balance due.
6. Now the invoice_amount should become the remaining balance from the result above, and any future payments would continue to be subtracted until the final balance = 0

Currently the report I've created does not know that a partial payment has been applied, and if a partial payment is made, the invoice repeats at it's full amount when another payment is received.

If there is a better way to do this please advise as well.  Thanks for your help!
0
Gary Demos
Asked:
Gary Demos
  • 7
  • 4
1 Solution
 
James0628Commented:
You mentioned an "attached statement", but I don't see anything attached to your post.

 Are you just trying to display the amount due (original amount - payments) on the report, or are you actually trying to change the db (eg. subtract payments from the original amount, altering the original amount in the db) ?  From item #6, it sounds like it might be the latter.  It might be possible to update the db through CR (eg. by using a stored procedure as the datasource), but it probably isn't a good idea.  And if you did do that, what would happen if you ran the report again with the same parameters?  The payments would be subtracted again, unless you had some way to prevent that from happening.

 And if you don't want to update the db, and just want to show the current total (original amount - payments), that seems simple enough.  Where are you having a problem?

 James
0
 
Gary DemosAuthor Commented:
James,

I do not want to update the DB, I just want to show the current total on the report. I'll try attaching the report again.  Thanks
Partial_Payment.pdf
0
 
James0628Commented:
As I understand your report and data, this shouldn't be a problem.

 Based on your sample report, it seems that you have one record for the invoice, and then any payments are in separate records.  And you're presumably grouping/sorting on the original invoice number or something, to get an invoice and its payments together.

 I assume that you're using a formula similar to {Invoice Amount field} - {Paid Amount field} to get the current balance.  Create a running total for Paid Amt and set it to reset when that invoice group/sort field changes, to get a running total for the amount paid on each invoice.  Then replace the {Paid Amt field} in the current balance formula with the running total:

{Invoice Amount field} - {#running total}


 You can also use the running total to adjust the Invoice Amt column to reflect the payments so far (prior to the current record).  You just have to adjust the result so that the Paid Amt from the current record is not included.  Create a formula similar to the following and use it for the Invoice Amt column:

{Invoice Amount field} - {#running total} + {Paid Amt}

 James
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Gary DemosAuthor Commented:
James - your suggestions worked beautifully. Now the only issue I am having is that the only invoices that show up on the report are ones that have had payment activity. If I remove all data fields that have to do with payments and only report on amounts that have been invoiced, then all of the invoices show up on the report. Could you show me how to avoid this problem. For example, here is my formula for the Running Total:
{ar_invoice.invoice_amount} - {#RTotalInvoice} + {ar_cash_invoice.cash_amount}

When I enter this formula in the report, it works correctly on invoices that have been paid, but it causes invoices that have no payment activity to disappear from the report. What should I do to this formula to get the unpaid invoice to show up?

Thanks!
0
 
James0628Commented:
First, just to clarify something -- The invoices with no payments were showing on the report before, when you included a field from the payment table (the paid amount), correct?  The invoices were there, and then disappeared when you added the running total on that field?

 Assuming that the answer is "yes" ...

 I'm guessing that you have a Left Outer Join to the payment table?  Under certain circumstances, when you use a field in an Outer joined table, CR changes the Join to Inner, which would prevent the report from including anything that was not in the joined table.  That sounds like what you're running into.  Assuming that I'm right about you using a Left Outer Join, you could check by changing the report again so that the invoices without payments don't show, and then go to Database > "Show SQL Query" and see if the Join is Left Outer or Inner.

 Assuming that I'm right, the question is, why is this happening?  I don't think simply adding a running total on a field in the payment table should do that (and it didn't in a test that I just did here).  Did you change anything else on the report, especially in the record selection?

 James
0
 
mlmccCommented:
Are you doing any filtering on the payment table?

mlmcc
0
 
Gary DemosAuthor Commented:
Sorry for the confusion. The missing "unpaid" invoices is a problem when I add any field from the payment table - not just the running total formula. I looked at the SQL statement generated by CR and the joins are all inner joins. Should I manually change them to left outer joins?

I am not doing any filtering on the payments receipts table.
0
 
Gary DemosAuthor Commented:
I tried changing the joins to left outer joins and that did not change anything. I've attached a copy of the SQL statement for review.
CR-SQL.txt
0
 
Gary DemosAuthor Commented:
I just tried changing the joins to RIGHT OUTER JOIN and now the missing invoices appear, but the invoice amount is blank on the newly appearing invoices. Here is the running total formula:

{ar_invoice.invoice_amount} - {#RTotalInvoice} + {ar_cash_invoice.cash_amount}

I assume there is no amount showing in the report because {ar_cash_invoice.cash_amount} has no value on these invoices.
0
 
Gary DemosAuthor Commented:
I think I have it now - I changed the report options to convert DB null values to default and now all of the invoices appear with the prices. Thanks for your help James!
0
 
Gary DemosAuthor Commented:
Appreciate the help and informative answers
0
 
James0628Commented:
You're welcome.  Glad I could help.

 FWIW, I thought about nulls and using that "Convert ..." option, but nulls didn't seem to be a factor in my test (I still got all of the records), so I didn't bring it up.  I'm glad you figured it out.

 James
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

  • 7
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now