1 Reply Latest reply on Feb 11, 2014 11:38 AM by philmodjunk

    Verifying a portal summary calculation

    NicholasBauer

      Title

      Verifying a portal summary calculation

      Post

           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?

           Thanks

        • 1. Re: Verifying a portal summary calculation
          philmodjunk

               Sounds like your script needs to execute a Commit Records script step before checking the totals to see if the amount has been exceeded.

               An alternative approach might be to use a script that assigns the values for each invoice and stops when the full value of the check has been accounted for. You would select the client, enter the total check amount and then click a button in order for the script to do the rest.