5 Replies Latest reply on Aug 10, 2014 4:01 PM by keywords

    Validating data before committing


      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?

        • 1. Re: Validating data before committing

          Hi Oakbridge,


          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!!



          • 2. Re: Validating data before committing

            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.

            • 3. Re: Validating data before committing

              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.

              • 4. Re: Validating data before committing

                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.

                • 5. Re: Validating data before committing

                  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.