Invoices and Payments

[Papabjoe]

I can't seem to figure out how to something that should be very simple. I have a transactions table. An invoice table. And I have a payments table. I need the ability to create a payment that is related to both the transaction and the invoice.

The problem is that I should be able to post multiple payments to multiple invoices in a single transaction. I have a feeling that I could use a repeating field in the transaction table to achieve this, but I'm not quite sure how to construct.

Can anyone give me some direction?

[CyborgSam]

IMHO, using repeating fields for related data causes more problems than it solves. And it isn't considered good FileMaker design.

Create another table, e.g. PaymentItems. In it are foreign keys to Invoices and Payments (so each item is associated with a single Invoice and a single Payment). The data in this table would be the amount paid towards the invoice. The Payments table then sums the related records in PaymentItems to show the total paid for this transaction.

Clear enough?