Confused about 'line-items' relationship strucuture

[ncasares]

I am working on 'fixing' a part of our company's invoicing program that uses repeating fields. Currently we have a 'payment entry' window where payments can be entered to close or pay against a particular invoice. The fields are 'Date Paid', 'Amount Paid', and 'Payment Details'. All of these are repeating fields. The second file we use is for printing monthly 'Invoice Tracking' reports. A date range is entered (a month) and the reports displays a list of all invoices paid in the date range.

Here is the problem:

The report that gets generated is accurate as long as all of the invoices were closed within the date range. However, if an invoice is NOT closed and only a portion of the total was paid a problem occurs. The report only shows the 'Amount Paid' for the first valued of the repeating field (i.e., if the toal was $100.00 and $50.00 was paid in Jan. and another $25.00 was paid in Feb. only the Jan. value appears in the Feb. report).

I would like to get rid of the repeating fields and replace them with records by creating a 3rd 'Paymets' file. Herein lies my confusion. How do I create one file for each invoice that holds payment entries as separate records but references them with a single 'Payments ID' without resorting to repearting fields.

Maybe I'm really confused here. Any help would be GREATLY appreciated.

Thanks !

[LiveOak]

I'm not sure I understand exactly what you are doing, but here is how to set up a payment file. You already have an Invoice file. Two additional files are needed because the relationship between Payments and Invoices is a Many to Many relationship. A payment can apply to many invoices and an invoice can have many payments. The two additional files are: 1) a Payment file where each record is a payment (one check by number, one charge card transaction, etc.) Parts of this payment may be applied to many invoices. 2) An Invoice/Payment Join file. A record in this file is a Payment/Invoice combination. Each record has a Payment Number, an Invoice Number, and the amount of the payment applied to this invoice.

The advantage of this "Join" file is that almost any type of report can be printed by using subsummaries of records in this file. I know this is a little complex (It just reflects real life! ).

-bd