I'm working on a invoice payment function. There are invoices that have been issued, and when a payment is received at head office it can be for the full amount for a single invoice, a partial amount on a single invoice, the full amounts for multiple invoices, partial amounts for multiple invoices.
I've got a Payment Header record that has a field for the amount of the payment. The user records it as one of the first steps.
I've got 1to many Payment detail records that have fields for the related invoice that they payment is for, and the amount that is being applied against that invoice.
I want to verify that the total that has been applied to each invoice is equal to the amount of the payment. For example if the payment was for $100, then the sum of the 1 to many detail lines should also be $100.
However I want to do this BEFORE I commit any records. I'm trying to follow the 'transaction' approach. I'm also creating sync records for mobile devices so the commit and transaction. I also want this to work for both new payment records and editing existing payment records (until we lock the payment record).
I started by just comparing the amount field (payment_header_amount) against Sum (payment_detail_amount) of the related records. Unfortunately when I look at the Data Viewer, I get different behaviours than what I would expect. (i.e. Data Viewer shows one behaviour on New records and another behaviour on Edited records).
Anyone have any suggestions?