The constraints that you need are all built into an accounting package, which is why we use MoneyWorks with their free FM Plugin for recording all Invoice Payments (& for Debtor Management) alongside our FileMaker system for invoicing.
Accomplishing that same functionality in FM is demanding, as you've discovered. My only suggestion is to copy each customers unpaid invoices into an empty temporary table where you can apply the necessary constraints. Once they are met there, then copy the payment data back into the original table, where the data can be locked.
You also have to make provision for a payment that exceeds the sum of outstanding invoices. MoneyWorks handles that by creating an Overpayment in the Customer's account. It is presented as a dialog when you next enter a payment for that Customer. You can choose to allocate that overpayment or enter a new payment. If you enter a new payment, the Overpayment remains. If you want to use both the Overpayment as well as the new payment, then you need to process these as two separate payment transactions.
I hope this helps somewhat. Good Luck!!
I have built a FM cashbook in which I applied the principle that users do not touch the cashbook entries directly. All data entry is done via a temporary entry data table and then applied to the main table via scripts. This way you can validate before the live table is addressed and offer the user options if need be.
I'm aware of the MW integration options as I have developed solutions in the past that used them. However for this specific customer, they are looking at FM to handle just handling the invoices and payments. They are still using another accounting package.
I think I've figured out a solution using script triggers on both the entry and exit points of the child amount fields.
I think that is very dangerous, using triggers like this and will very l ikely not fully cover a true "undo" if the user wants to back out of the transaction.
The key is in giving the user a scratch pad / sandbox to enter all the data, away from the real underlying data. Then when the user clicks "post transaction" that scratch data is applied as a true transaction to the underlying data, with full back-out functionality if something is wrong.
I agree with you; what I call an entry data table you call a scratchpad/sandbox. And as for script triggers, a weakness with these is that they apply at the level of specific field instances, not within the field schema itself.