Formula help

I am trying to figure out how to apply 1 incoming payment to many invoices. I have 3 tables Clients, Invoices and payments, each client has many invoices. Client usually makes 1 payment which I need to apply to several invoices at one time.

Can I set up a checkbox that would trigger a formula to divide the payment equally among all the invoices for that client that have a status of balance due?

Forn example, Client A has 10 invoices with a status of balance due totaling $10,000.00 and we receive a single payment of $7500.00 which I want to divide among these invoices via my payments table.

To complicate things, I have another field in my payments table that automatically calculates the write off amount. Then I have a formula field which takes the invoice amount due – payment received – write off amount = $0.00 (all invoices always have to have a zero balance to close them out.

Originally, I had this set up through my Clients table. I had a summary field for all invoices with a balance due and then 2 summary fields – payment amt and write off total from my payments table, which worked fine. However, I have added AR aging to the invoices table and now the payments are not being deducted accurately from the AR since the total AR is calculated in the Clients table. Any ideas on how to tackle this???

------------------------------
Kelly Lyons
------------------------------

Forum Discussion

Complex formulas help