How should I set up a Payments table?

[jcoffey]

In my db I need a payments table that will allow me to enter partial and over-payments to invoices. I would like to be able to manually allocate payments to specific invoices. How should this be done? My attempts so far are not entirely successful. Also, what fields should be included in this table?

 

Thanks to all who attempt a solution.

 

John Coffey

[LingoJango]

Offhand, I'd say __kp_PaymentID (serial number, can't modify), PaymentDate, PaymentAmount - I hope you don't need PaymentCurrency, cause I do and it's a nuisance complicating things greatly - _kf_InvoiceID, _kf_ClientID.

 

Manual allocation - do you want to do it from the Invoice perspective or from a Payment perspective? In other words, will you have a list of unassigned payments that you want to assign the right invoice number(s) to (perhaps imported from an Excel file obtained from your online banking facility), or a list of pending invoices that you mark as paid? You can have both, but each will require a different layout.

 

Do you want to allow a single payment to be made for two invoices, or will that seldom happen and can you live with having to split the amount into two payment records?

[jcoffey]

Lingo,

 

Thanks for your help. To answer your questions, I would most likely enter the payment from the Invoice table as most payments will match a particular invoice. Sometimes the payments do not match an invoice as I get monies to credit future invoices, payments covering multiple invoices, and payments that are partial payments.

 

During my reading, I came to think that a join table is in order since I'll have a single payment covering multiple invoices as well as multiple payments on a single invoice. Sounds like a many-to-many relationship. I have not been able to implement a solution that allows me to allocate a payment to multiple invoices. My portals don't show a list of invoice in which I can allocate payments.

 

Did I make myself understood? did I answer your question? Thankfully, I don't have to deal with different currencies. Of course setting up my sales tax information from payments has been a nightmare. But that's another story.

 

John Coffey

[LingoJango]

You need to think about interface next, I think.

 

BTW, this is helping me because I haven't yet got to to the payments part of my own solution, but I think I'll just allow two payments to be recorded for one where necessary, thus sparing the need for a join table. KISS.

 

If you're entering payments from the Invoice table, I'll assume your workflow is

1) see incoming payment with payer and amount, and perhaps invoice ID ("perhaps" comes from personal experience)

2) navigate to appropriate invoice record

3) enter payment date and amount, and perhaps payment method and payment comments, in a portal

4) invoice balance is automatically updated

 

Now, if you want a many-to-many relationship, there's a tricky decision in terms of calculations: you have a client with two oustanding invoices, an older one for $100 and a newer one for $150. You get a payment of $120. You enter the payment for invoice 1. Should the system automatically assume that 100 dollars are for invoice 1 and the balance for invoice 2, or do you want to be able to say that 50 dollars were toward invoice 1 and 70 toward invoice 2?

 

You say your portals don't show a list of invoices to which you can allocate payments. That's kind of vague. How did you set up your relationships? What kind of portals have you set up, and from which other table? What kind of interface do you want to build?

 

You need to mull it over. (So do I!) My thinking this minute - but I had a champagne-lubricated lunch today, so don't trust me - is that you might want an unpaid invoice portal and a reverse-sort-by-payment-date payments portal in a Client table layout, as presumably every payment is linked to a single client (but then again, that might not be the case, if you're using factoring or reverse factoring services!). And you'd have some kind of scripted method to update the payment balance field in the invoice record (or some other INDEXED field you will need for a portal of unpaid invoices).

 

I still have to think through the calcs. Hope someone else can help, I'm a bit fuzzy there right now. But you should definitely have some sort of functioning portal.

[jcoffey]

I've finally set up a Payments table that allows me to allocate monies to multiple invoices as well as have multiple payments to a single invoice. I've done this with a join table, allocation payments. I had so much trouble getting it to work but after reading "Special Edition; Using FileMaker 8" by Lane, Love and Bowers and then looking at a sample join table file, Militia, I saw how it is supposed to be set up! The gist of it is to set up a portal (allocation Payments) in the Payments table, add Invoices: invoice_ID, Invoices:date, Invoices:balance due, AllocationPayments: allocation amt, AllocationPayments:allocationbalance(calc.) I set up a value list for invoices_ID so I can select from the invoices. This last part may get unwieldly when the records for invoices gets too large. If I could narrow down the invoices to either previously unsatisfied invoices or only invoices from the client who made the payment, that would be helpful. Something to work on on a rainy day!

 

As is usually the case, once you've gotten one thing to work, some other problem to the "perfect solution" crops up. This one may be more appropriate for another category so I'll post elsewhere later.

 

LingoJango, thanks for your help. I'd post my solution but you're a guest member and don't have download privileges.

 

John Coffey

[LingoJango]

That's ok, your description's enough.

 

The filtered value list should be relatively easy to sort out - I would personally go for all pending invoices because payments are sometimes made by an unrecognizable paying agency and detective work is required to narrow it down.

 

My personal priority (already in place) is a button that will enter the total balance due as the amount paid, since that is what happens 90% of the time.