Could anyone shed any light on my little problem?

I have written a billing application, which pays out commissions to sales agents. The agents are paid commissions on received payments only.

Whenever an invoice is printed, the BOOK database is updated with the relevant information needed to provide the customer with a statement of account. I also use this database to calculate the agent payouts.

There are two payment types in the database: PAY (money received) and INV (bills sent). Usually the client would pay an amount that would clear the amount owing for that invoice. I say usually, because he may pay more or less as he wishes. For all intents and purposes, though, each invoice should have its own history (including any payment reminders). This means that invoice 123 for $30 should be cleared by a payment for invoice 123. If he pays $20 for 123, the invoice is unpaid and cannot be offset by a payment on invoice 124, for example.

My problem: I need to be able to calculate a commission for the agent, but only when the invoice (123) has been paid in full. Conversely, if he overpays for an invoice, commissions can only be paid out on the original commissionable amount.

Do you think it preferable to have only one record for each invoice, with, say, a repeating field for payment history? Or should I keep each transaction (INV or PAY) as a separate record, and try to consolidate the two using a script? Or is there perhaps a more elegant way?

quote:

---

Originally posted by timalex:

Could anyone shed any light on my little problem?

I have written a billing application, which pays out commissions to sales agents. The agents are paid commissions on received payments only.

Whenever an invoice is printed, the BOOK database is updated with the relevant information needed to provide the customer with a statement of account. I also use this database to calculate the agent payouts.

There are two payment types in the database: PAY (money received) and INV (bills sent). Usually the client would pay an amount that would clear the amount owing for that invoice. I say usually, because he may pay more or less as he wishes. For all intents and purposes, though, each invoice should have its own history (including any payment reminders). This means that invoice 123 for $30 should be cleared by a payment for invoice 123. If he pays $20 for 123, the invoice is unpaid and cannot be offset by a payment on invoice 124, for example.

My problem: I need to be able to calculate a commission for the agent, but only when the invoice (123) has been paid in full. Conversely, if he overpays for an invoice, commissions can only be paid out on the original commissionable amount.

Do you think it preferable to have only one record for each invoice, with, say, a repeating field for payment history? Or should I keep each transaction (INV or PAY) as a separate record, and try to consolidate the two using a script? Or is there perhaps a more elegant way?

---

This is easy, you simply base the commission on the invoice amount, regardless of the payments. You use a RELATED file (not repeating fields) to store the payment details. Once the invoice has been paid you simply need to update a field to show the commission should/has been paid.

You are making the problem more complex by trying to work the payments into the commission calculation, when it has nothing to do with it.

------------------

=-=-=-=-=-=-=-=-=-=-=-=-=

Kurt Knippel

Consultant

Database Resources

mailto:[email protected]

http://www.database-resources.com

=-=-=-=-=-=-=-=-=-=-=-=-=