I am trying to add a payment for invoices. Similar to the way quickbooks handles applying new payments for invoices.

Many times I receive One Check for Several Invoices.

I want to open a layout and:

(Select) One Customer

(Enter) Payment Amount

and see List of invoices for this one customer

Attached is my layout. Can someone who has done this direct me and let me know if I am heading in the right direction. Do I need any other fields or tables or additional relationships?....Any input would be great.

Thanks. Jay.

Payment Receive Setup.zip

Hi Jay,

Lots of thoughts, in no particular order.

First thing, you mention invoices, and yet you do not have an Invoice table, you have an Order table. Are there invoices against an order?

Also missing is an invoice line items table. I see _kf_lineitem (id), _kf_widget_id, and _kf_product_color_id in Order, which is odd. They belong in a line items table.

Basically, I put a New Payment button on a customer form. This creates a new payment record and populates Payment::_kF_CustID. Now you can have a portal of open invoices for that customer from which to apply this payment. Therefore, you need another table that joins Invoices and Payments and stores the amount from the payment applied to that invoice. User selects an invoice, and enters the amount from the payment to apply. You calc remaining balance on invoice, remaining amount of payment not applied, etc.

You may wish to also allow an invoice to close even if it has not been paid in full.

How about posting an ERD, and we could help you with the basic data model.

hth,

Barbara

Hi Barbara, thanks for the response. Orders Table is my invoice table. Those _kf you referred should not be there they were basically created when I started this project for test and trial I just havent got around to deleting those. There is a lineitem table for the orders. By the excuse my ignorance on abbrv... What is an ERD? Whatever that may be I get it for you.

Jay.

An ERD (Entity Relationship Diagram) is NOT the Relationship Graph. See here: http://www.getahead-direct.com/gwentrel.htm

Here is the ERD...professionally laid out.

J.

ERD.zip

Here's a very simplified approach that we use.

Document Header can be any type document, sales order, PO, Invoice, credit memo, receiver, Return Merch Auth, etc. Some but not all of these require posting to the financials in the form of receivables or payables.

Voucher handles transactions, payments or receipts and adjusts posted rcvbles, payables and also posts to the Ledger.

FinSim.zip

Rod,

Terrific input! Hopefully, it's not too high level for beginners to translate to a table structure.

Jay,

This is a good start, but your diagram does not support your stated business requirements in that you said, "Many times I receive One Check for Several Invoices," and your ERD shows that a payment is applied against one and only one Invoice. Modeling pays off right away!

Perhaps Rod would be willing to translate his model to show you how it allows for payments to be applied to one or many invoices?

Rod,

Terrific input! Hopefully, it's not too high level for beginners to translate to a table structure.

Jay,

This is a good start, but your diagram does not support your stated business requirements in that you said, "Many times I receive One Check for Several Invoices," and your ERD shows that a payment is applied against one and only one Invoice. Modeling pays off right away!

Perhaps Rod would be willing to translate his model to show you how it allows for payments to be applied to one or many invoices?

Thanks for putting me back on track with the OP question. From the perspective of voucher you could have a portal to rcvbls, oldest first. One additional field in the rcvbls table, lets call it applied amount. This is what I'd call a scratch pad field, such that it will be used only to replace the actual rcvbl amt in a duplicated record once it is posted. The total received amount would reside in a global. Via scripting you can apply the received amount to each portal row, calculating the balance as you go down the rows, finally applying the remainder to the last receivable amount. You now have the scratch pad field filled, you can modify these at will from the portal, as long as they total the received amount. Once your satisfied with the scratchpad total, you can post the result, scripting this to add a transaction to the receivables most likely by doing a gtrr then set the scratchpad to a variable, clear the scratch pad amt, dupe the record and apply the new transaction amount from the variable to the duplicated record, changing any other fields as necessary. Reference the voucher id in the rcvbls transaction in another field related back to the voucher header and now you have your voucher line items. Hope this makes sense, perhaps more complex than you'd like to take on.

Boy...no kidding that is little too complexed for my tiny brain cells to handle at this time.

Thanks everyone for the information.

J.

Well, Jay, go back a reread my first reply. I believe that's all you really need, and isn't much different from Rod's solution. His model simply incorporates all types of entities that have line items and nicely scripts transactions. Well worth getting your mind around. See Todd Geist's blog on Transactions (Link), in that you do want to ensure an all or nothing post of any transaction.

So my relationship would be:

Invoices------>PaymentLedger<------Payment

Does this look correct?

J.

Sorry, can't followup as well with this new interface. Yes, that's what you need, and join table between payments and Invoices, if you intend to allow a payment to be applied across more than one invoice.

To reference Ron's ERD, this is the equivalent of his Document Header->Receivables<-Voucher structure.