What's my alternative to repeating fields?

[murray]

I have inherited a database for invoicing which I am trying to clean up a bit. At present there are a number of repeating fields and, by the sound of previous threads, maybe I need to change things. I haven't had any experience with any other invoicing systems so I have no idea whether this is the usual approach.

In the existing solution, a record is created as an invoice for a customer, and when they pay, these payments go into the same record (I have a suspicion that it may be usual to have invoices and payments in separate records or even separate dbs). It is very common for payments to be made in 2 parts eg a cheque from an insurance company, with the resulting balance paid by the individual with cash, credit card etc, not necessarily on the same date). These payments are currently entered into repeating fields that identify the payer, amount, date of payment, type of payment, cheque number and bank. One limitation (not very common but enough to be annoying) is bad debtors who pay a few dollars a month and end up paying in 5 or more installments.

Without a complete rebuild of the database and those that relate to the invoicing solution, is there a better way that a novice like myself can use to modify the existing database? I can't afford to spend too much time on the transition and of course need the calcs based on these repeating fields to be transportable to the modified solution also.

Does anyone have any ideas? If the news is all bad, I'd be grateful if anyone can point me towards a perhaps more typical simple invoicing solution that I can adapt to the rest of our home-made system.

Thanks a lot, Murray

[Kurt Knippel]

Well the easy solution is to simply replace the repeating field with a new database for payments. You would have the same number of data fields as the repeating fields are now. The only modifications you need to make are to replace the references to the repeating fields with the related fields in the new payments file.

[murray]

Thanks Kurt,

I guess that means having a unique ID for each record to relate the payments to, as well as similar IDs to identify specific payments. I suppose using a portal is the likely choice also.

BTW, I must do a search on these forums on the words "easy" and "simple" and see how many of those threads I can understand! I'm not a developer so even some basic things escape me at the moment, but I'll definitely give it a go. My main concern is migrating existing data to the new version.

Thanks for your input.

Cheers, Murray

[bruceR]

captkurt said:

Well the easy solution is to simply replace the repeating field with a new database for payments. You would have the same number of data fields as the repeating fields are now.

Ack! No, that's not the right set up. You need a PaymentItems file with probaby a total of four fields. A unique recordID field. An InvoiceID field so that the payment record can be tied to the Invoice. A date. And an amount. Perhaps you will want some other detail associated with each payment such as check number. Creating a separate payment field for each payment is not the right approach. Separate RECORDS for each payment is what you need.