Any suggestions on how to create a payment log to post payments, partial or full, to a particular invoice?
You need to define a new table, named, for example Invoice_Payments. In the new table, include a field named "Invoice ID Match Field". Create a relationship between Invoices and Invoice_Payments with the match Invoice ID = Invoice ID Match Field. Click the "Allow creation of related records..." checkbox on the Invoice_Payments side. Then create a portal on the Invoices layout to allow creation of payments for each Invoice.
In my previous post, I neglected to add: In your new table include an auto enter Payment ID field, a Payment Date (date) field, a Payment Amount (number) field, and a Payment Type (text) field (i. e., cash, check, credit card) if necessary. Put these in your portal. In the Invoice table you can add a Sum (Invoice_Payments::Payment Amount) calculation to make sure the payments equal the Invoice amount.
Do what Allen says. It allows multiple payments of different kinds and all are tagged by date. A nice side-effect is that, from the Payments table, you can generate daily, weekly, monthly payment reports. I have this in most of my client solutions. It makes them happy - and happy clients pay developer invoices.
"I'd like to live as a poor man with lots of money." ~Pablo Picasso
I as well have a separate table for payment entries. Makes everything easier.
I suspect that most experienced developers would agree with me when I say that no matter how hard a client argues, until their blue in the face, that there will only ever be one payment per invoice, add a separate table. Even if you just put the related fields on the invoice layout. When they inevitably come back and say that one of their customers wishes to make a part payment, it's a simple process of replacing the fields with a portal.
We call these things 'BS Factors'. It's when a client tells you one thing and then comes back later and say "But Sometimes...". We always explain BS factors to our clients early on in the development process and it certainly helps as it's always in the back of their mind when detailing their requirements.
I have a Payments table and a "splits" table. An invoice may have many payments. And a single Payment may be for several invoices. A "Split" may be the same amount as the payment, or a portion of a payment. It works!
Retrieving data ...