Apply a Payment to Multiple Invoices Automatically

by Dr. Peter Wayne

Here's a common enough problem. You have a typical customer billing application in which you keep track of each unpaid invoice. A check comes in for a customer who owes you for multiple invoices. Do you have to break out a calculator to divide the payment up for each invoice? In this article we'll develop a script that will apply the lump payment to the customer's outstanding invoices in oldest-first order. As always, we'll start with the table structures for Invoice.dbf and Payment.dbf:

Let's enter a few items into the Invoice table:

Figure 1. Entries in invoice.dbf.
Note that in this example we have 4 invoices for customer 000001.
Now, we'll create a form in which to enter payments. We will use the payment table as the base table for the form, but no fields from payment will actually be on the form. Instead, we'll place layout-level variables on the form for the payment date, the number of the check by which the payment is made, and the total check amount:
Figure 2. Define layout-level variables for the payment form.
Place fields and appropriate labels for these variables on the payment form, along with 2 buttons, one for a single invoice payment and the other to pay multiple invoices, an autopay button:

Figure 3. Place the variables and the buttons on the payment form.

Are we done? Not quite - we haven't created any code for the 2 buttons! We'll do the single invoice button first, because it's conceptually simpler. What we need to do is:

ask the user which invoice is being paid
read the payment info. from the form
verify that the invoice exists, and if so:
update the invoice_balance field of the chosen invoice
enter the payment info. in the payment table

Script 1. Pseudocode for the Pay single invoice button.
We can translate this into Xbasic:

dim invno as c
invno=ui_get_text("Enter invoice number","")
if invno="" then
 end
end if
dim invoice as p
dim qry as p
dim payment as p
payment=table.current()

invoice=table.open("invoice")
query.filter="invoice_number='"+invno+"'"
query.order=""
qry=invoice.query_create()
nrecs=qry.records_get()
select
 case nrecs=1
 ' invoice found
 	invoice.fetch_first()
 	invoice.change_begin()
			invoice.Invoice_balance=invoice.Invoice_balance-val(parent:check_amount.text)
 	invoice.change_end(.t.)
 	payment.enter_begin()
 		payment.payment_date=parent:payment_date.value
 		payment.check_number=parent:checknum.text
 		payment.check_amount=parent:check_amount.value
 		payment.invoice_number=invno
 		payment.applied_to_invoice=parent:check_amount.value
 	payment.enter_end(.t.)
 case nrecs=0
 	ui_msg_box("Error","No matching invoice found")
 	goto finish
 case nrecs>1
 	ui_msg_box("Error","More than one matching invoice found")
 	goto finish
end select

finish:
invoice.close()
end

Script 2. Xbasic for the Pay single invoice button.
Error-checking code was added to the Xbasic to be sure that the user enters a valid invoice number. The query looks for the invoice in invoice.dbf and verifies that one and only one record with that invoice number is present. Finally, the script makes a new entry in the payment table and adjust the invoice_balance field in invoice.
The code for the Autopay button is an extension of the code in Scripts 1 and 2. The outline is:

ask the user which customer wrote the check
read the payment info. from the form
create a query in oldest-first order for the customer's outstanding invoices
while there is still money left from the check
 make a full or partial payment for each invoice

Script 3. Pseudocode for the Autopay button.
The Xbasic for this button then becomes:

dim custid as c
dim invoice as p
dim payment as p
dim qry as p
dim funds_left as n
dim checknum as c

payment=table.current()
funds_left=parent:check_amount.value
checknum=parent:checknum.text
payment_date=parent:payment_date.value
custid=ui_get_text("Enter customer id","")
if custid="" then
 end
end if
invoice=table.open("invoice",file_rw_shared)
query.filter="CustID='"+custid+"' .and. invoice_balance>=.01"
query.order="Invoice_Date"
qry=invoice.query_create()
if qry.records_get()=0 then
 ui_msg_box("No invoices","match that customer")
 goto finish
end if
invoice.fetch_first()
while .not. invoice.fetch_eof()
 if invoice.invoice_balance>=funds_left then
 	payment.enter_begin()
 		payment.payment_date=payment_date
 		payment.check_number=checknum
 		payment.check_amount=parent:check_amount.value
 		payment.applied_to_invoice=funds_left
 		payment.invoice_number=invoice.invoice_number
 	payment.enter_end(.t.)
 	invoice.change_begin()
 		invoice.invoice_balance=invoice.invoice_balance-funds_left
 	invoice.change_end(.t.)
 	funds_left=0
 	exit while
 else
 	payment.enter_begin()
 		payment.payment_date=payment_date
 		payment.check_number=checknum
 		payment.check_amount=parent:check_amount.value
 		payment.applied_to_invoice=invoice.invoice_balance
 		payment.invoice_number=invoice.invoice_number
 	payment.enter_end(.t.)
 	funds_left=funds_left-invoice.invoice_balance
 	invoice.change_begin()
 		invoice.invoice_balance=0
 		invoice.change_end(.t.)
 end if
 invoice.fetch_next()
end while

finish:
invoice.close()
if funds_left>.01 then
	ui_msg_box("Overpayment","There is still "+str(funds_left,10,2)+" left!")
end if
end

Script 4. Xbasic for the Autopay button.
Save the form and run it. Experiment with entering a single payment of $30 for invoice "000001", and a bulk payment of $100 for customer "000001". If you like, place debug(1) statements in the Xbasic code for each button to see how the updates are made to the underlying tables.

Download the tables and forms discussed in this article.

7/27/98 - pkw

Don't forget, we need your feedback to make this site better!

Return to home