Verifying a portal summary calculation
I am adding payment functionality to the Invoices starter solution. I have a "Payments" table connected to the "Invoices" table via an "Invoice Payments" join table. On the payments layout, checks are assigned to invoices through a portal to the invoice payments table. The result is like having invoices as line items being paid by the check amount.
I have a calculation to keep track of the check's remaining balance as it is assigned to an invoice. As far as I can tell, I have to do this with a Calculation Field, because a Number Field that is auto-filled by a calculation will not evaluate in real time with the portal records being updated. For example, I can add an invoice payment amount and the check balance will not update.
Of course, I want it to stop or display a warning when the check's total amount has been exceeded ( check amount - sum (invoice payment amount) = 0 ). Since I have to keep that balance in a calculation, it can't have a verification accomplish this, nor can it be used real time with a verified number field calculation. Similarly, I would like to have the invoice status change to "paid" once it's remaining balance reaches 0. Again, I can only seem to have a properly updated balance calculation in a Calculation Field, and I can't trigger any other occurrences with its result.
I've also tried making a script that checks the remaining balance triggered by exit from the "Invoice Payment Amount" field in the portal. This seems to run the script before the portal record has been updated, however, as it doesn't catch an overspent check the first time it happens.
Any other ideas on how this could be accomplished? Am I missing some functionality of the Number or Calculation Field, or Portal?