Solved

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

Posted on 2016-08-01
12
117 Views
Last Modified: 2016-08-04
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
Comment
Question by:Gary Demos
  • 7
  • 4
12 Comments
 
LVL 34

Expert Comment

by:James0628
ID: 41738350
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
 

Author Comment

by:Gary Demos
ID: 41738884
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
 
LVL 34

Accepted Solution

by:
James0628 earned 500 total points
ID: 41739610
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
 

Author Comment

by:Gary Demos
ID: 41741168
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
 
LVL 34

Expert Comment

by:James0628
ID: 41741294
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
 
LVL 100

Expert Comment

by:mlmcc
ID: 41741654
Are you doing any filtering on the payment table?

mlmcc
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:Gary Demos
ID: 41742267
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
 

Author Comment

by:Gary Demos
ID: 41742393
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
 

Author Comment

by:Gary Demos
ID: 41742454
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
 

Author Comment

by:Gary Demos
ID: 41742475
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
 

Author Closing Comment

by:Gary Demos
ID: 41742480
Appreciate the help and informative answers
0
 
LVL 34

Expert Comment

by:James0628
ID: 41743079
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Showdialog 8 29
Visual Studio 2013 Shortcut (VB) 4 34
Close form "before" open 3 19
VB.net Facial recognition software (time and attendance) 6 13
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
Today, still in the boom of Apple, PC's and products, nearly 50% of the computer users use Windows as graphical operating systems. If you are among those users who love windows, but are grappling to keep the system's hard drive optimized, then you s…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

919 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now