Dear Friends,
I have a Sales Table:
Which provides data such as the start date of an Installment, Commission Rates, and “Invoices Table” calculates the Commission Invoice Amount Payable at the time the Invoices are Created MINUS any Previous Invoices Paid.
When the status of the Invoice changes to Paid, the Revenue field in the Invoices Table gets updated to reflect the Commission Invoice Amount, which gets “ROLL’ed UP” in the Sales Table that adds all the Invoices paid till date against the Sales ID linked to Invoices in Total Revenue Generated.
The “Sales Tables” than minuses the Total Revenue Generated from the Total Invoices Cycles Run To date (Expected Revenue) to give the “Amount to Invoice for the next Invoice”. This Amount to Invoice is LOOKED up in the Invoices Table to give an indication as to when the new invoices can be generated.
The problem is when the Invoice changed to a paid status, the current invoices and all other invoices have wrong Invoice Amount as they all minus the Previous Invoices Paid field in order to calculate the Invoice Amount.
The solution to this is to make the Previous Amount Paid frozen to the date of Invoice which is not affected by the Invoices Status being changed to PAID, also it should add-up all the Previous Invoices Paid at the time of Creating the Invoice and that Commission Invoice Amount should stay frozen
I don’t know how to accomplish that? Please help, as I have been stuck for many days.
