The companies don't necessarily create one payment per invoice, they may pay for several in one go.
Isn't it also possible that a payment will only partially pay off an invoice or that they might overpay due to a mistake?
You need a relationship that links payments to invoices and if your answer to my question is "yes", it's a many to many relationship where you can link one payment to many invoices and also one invoice to more than one payment. If the answer is "no, this never happens", then you have a one to many relationship, one payment to possibly man invoices.
Thanks for you response Phil,
The answer is Yes, but...
I can sit and manually try to tie invoices to payments and payments to invoices, use calculations to work out how much is outstanding from a part-paid invoice and how money in compares to payments outstanding, but what I really need is a running total of:
Invoice1 +£1500 +£1500
Invoice2 +£500 +£2000
Invoice3 +£750 +£2750
Payment -£2000 +£750
Invoice4 +£750 +£1500
So I can see at any point each transaction and a running total. This would save all the manual matching of payments to invoices and would give me a nice statement page that I can produce for any companies that want to know their current balance.
I was thinking perhaps of a 3rd table where a record is create automatically from the OnRecordLoad script event trigger in either the Invoices or Payments tables which links the +/- amount based on the creating table and loads the related company ID at the same time. This could then be viewed as a single list of transactions in and out.
But I wonder if this is a smart way to do it? Do you have experience of this kind of solution?