summary function on new records in a grid

[Jay Talbott]

In an application similar to an Accounts Receivable payment process I would like to be able to accomplish the following:
Record the total amount of the payment and disburse it to the proper invoices, while keeping track of the amount applied to each invoice and the unappliedamount remaining.

The payment itself would be a separate table, and the payments applied to each invoices would be records in a different table.
I do not want to commit the grid until the entire payment has been disbursed completely, and not overapplied nor underapplied.

I tried using the total summary function in a grid, but that will not keep track of the total unless the records are committed (unless I am missing something).
I believe the essence of the problems is trying to total the applied payments before the record is actually saved.

I cannot think how to resolve this using javascript, nor an ajax callback, as the grid will not be submitted until the payments are disbursed completely.

Any thoughts?

Thanks,
Jay

[StephenP]

Re: summary function on new records in a grid

Jay, tell me if this is an accurate understanding of what you are trying to do:

Customer pays $100.00.
Customer has bills for the following three invoices (totaling $110.00):
101 -- $50.00 for bowling ball
102 -- $35.00 for shoes
103 -- $25.00 for bag

You then want to apply:

$50.00 to inv. 101 (full payment)
$30.00 to inv. 102 (partial payment)
$20.00 to inv. 103 (partial payment)

You want to be able to apply varying amounts to each inv., but make sure the entire payment is used (though not necessarily paying each invoice in full). You do not want to commit anything until you have allocated all of the payment.

If this is the case, let me know. I have an idea of how to tackle it, but don't want to put time into it if I'm off the mark here.

[Jay Talbott]

Re: summary function on new records in a grid

Stephen,
You are correct. That is what I want to do.
Of course, one customer might have 2 invoices, another 1 invoice, and another 6 invoices.
But your construct is correct.
Thanks,
Jay

[StephenP]

Re: summary function on new records in a grid

Jay, I have two snippets of code for you to try. First, use this in a free form area of the grid:

<select id="list1">
	<option>1</option>
	<option>2</option>
	<option>3</option>
</select>
<input id="text1" type="text"></input>
<button id="button1">Apply</button>&nbsp;&nbsp;
<a href="#" onclick="a5_AJAX_Callback('misc.ajax.a5w','action=addrow&whatrow=2');">add row</a>
<br />
<span id="newrow2"></span>

Then put this is a new a5w page called misc.ajax.a5w:

<%a5
if eval_valid("action") then
select
    case action = "addrow"
		dim whatrow as c = default "0"
		if whatrow > "1" then
			dim rowhtml as c
			rowhtml = "<select id=\"list"+whatrow+"\"><option>1</option><option>2</option><option>3</option></select> <input id=\"text"+whatrow+"\" type=\"text\"></input> <button id=\"button"+whatrow+"\">Apply</button>&nbsp;&nbsp;<a href=\"#\" onclick=\"a5_AJAX_Callback('misc.ajax.a5w','action=addrow&whatrow="+ut(str(val(whatrow)+1))+"');\">add row</a><br /><span id=\"newrow"+ut(str(val(whatrow)+1))+"\"></span>"
			? "$('newrow"+whatrow+"').innerHTML = '" + js_escape(rowhtml) + "';"
		end if
end select
end if
%>

Publish both and press the add row button. This adds any number of rows. Then use a similar construct to populate the fields in the rows that are being returned via the callback. Since the callback takes place in an xbasic block, you have full access to tables, etc.

In order to know what values have already been used, include something like this in the callback:

a5_AJAX_Callback('action=addrow&whatrow=2&prevlist='+urlencode($('list1').innerHTML)+'&prevtext='+urlencode($('text1').innerHTML));

I know I don't have all the pieces worked out, but hopefully it provides a starting point.

Another (non-ajax) option might be to use two tables called Payments and Allocations.

Payments would simply track the payment itself:

• Payment_Id
• Payment_Amount
• Payment_Remaining (initially same as Payment_Amount)
• Customer_Id
• Salesman_Id
• Payment_Type
• Payment_Date

Allocations would track payment disbursement:

• Allocation_Id
• Payment_Id
• Invoice_Number
• Allocation_Amount

You could use a Post field rule to update the Payment_Remaining with each allocation entry. Although this does not accomplish waiting to commit until all disbursements are made, it seems like a valid method, since you can work them back and forth and the post rule will keep the Amount_Remaining up-to-date.

I hope one of these ideas works.

[Jay Talbott]

Re: summary function on new records in a grid

Thank you Stephen, I will give it a try.
Jay