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!