Hi,
I have a situation that I am stumped on how to handle. I have invoices that we track payments against. We use four fields to do this: “Invoices”, “Current Payment”, “Paid To Date”, and “Balance Due”. Here is where I need help:
We report weekly on what we collected to management. When we get just one payment that zeros out the invoice as paid, this works fine and gives me the data I am looking for. It is when we have progress payments (and not just one payment for the entire amount) that I need to figure out.
To complicate things, many times we get multiple progress payments, and we never know how many payments we are going to receive (usually ranges between 1 and 6 - the joys of being an insurance restoration contractor!).
I want to be able to take the “Current Payment” and copy it into another field so I can report on it as collected during this period. I then want to be able to add that to the existing “Paid to Date” figure so I have an accurate “Balance Due” amount. I am hoping once that all happens, I can somehow zero out the amount I entered into the “Current Payment” to get ready for the next payment coming whenever. And this removal of the data has to be done without removing it from the “Paid to Date” amount. I then need to be able to repeat this process until the balance is zero - and as stated above, this could be up to 6 or 7 additional payments like above.
I tried using different fields for each potential progress payments as well as date fields for each of those payments, but the filtering became impossible to get what aI wanted as it was always looking at different fields and was therefore adding things that I didn’t want to see.
Does anyone have any feedback on how to achieve this?
Thanks in advance!
