3 Replies Latest reply on Oct 27, 2014 11:21 PM by todd-

    Calculation on related records does not work while entering a record but does after saving the records


      Having a field calculation with Sum for a field in a related table two levels deep doesn't sum correctly until a record is saved/commited, however I need it to calculate before a record is commited. Calculations seem to work 1 level deep but not 2 levels of related tables.


      When I mention save a record/commit a record, I'm referring to all tables & related tables being on one form and the user saving a record from a Filemaker form.


      Related tables (all related by Id's) -

      Payment -> PaymentDetail -> Invoice


      Payment stores info about a payment (date, accout to apply pymt to, ck#, etc)

      PaymentDetail stores Payment::Id & Invoice::Id & Amt or part of Payment applied to an Invoice (Payment can be applied to many invoices so this table acts as a many to many between Payment & Invoice tables)

      Invoice stores all details about an invoice (amt due, job done, etc)


      A Sum calcuation field in Payment of Invoice::Amt returns the wrong result while entering a new record but is correct after a save record/commit. I'm needing to make sure the Sum of related Invoice:AmtDue adds up to Payment::Amt before I allow the user to save/commit a record.


      Any insight into this problem is greatly appreciated! : )


      Thanks in advance for any help.


      P.S. - I've used calculations on related records 1 level deep and it works if the user is entering data or viewing saved data. However I'm assuming Filemaker doesn't like calculation 2 related tables deep.

        • 1. Re: Calculation on related records does not work while entering a record but does after saving the records

          I assume you are wanting to see the progress result in order to prevent a user from leaving out of balance errors in the file. If that is right I think you need to draw a distinction between FM's commit and your user completing a transaction. Obviously FM needs to commit the data that is entered in order to carry out the Sum calculation, so you need to allow that to happen. However your user needs to be prevented from completing the routine until the payment is fully allocated and accounted for. This second part you can achieve by performing the whole transaction in a modal window with a Finalise button which runs a script to check that the transaction is balanced before closing the window.

          • 2. Re: Calculation on related records does not work while entering a record but does after saving the records

            Thanks for your reply but sorry it's not obvious FM needs to save data entered to carry out the Sum calculation and is actually an invalid statement.


            Create two tables -

            Table:  TestA     Fields: Id, Amt

            Table:  TestB     Fields:  Id, TestAId, Amt


            For table TestA create a calc field:  cSumOfTestBAmt = Sum ( TestB::Amt )


            Relate the the two tables  by TestA::Id  and  TestB::TestAId


            Create a layout based on on TestA and portal for TestB


            Now try to enter numbers in TestB::Amt and you will notice Sum works perfectly summing TestB:Amt's before the transaction is commited.


            Note sure why but I have read a ton of post saying the same thing you are saying which is wrong.


            Also a button or script trigger is exactly what I'm wanting to do, compare the Sum to Payment::Amt to make sure they are equal before letting the user commit the transaction or all records on the layouut.


            I know there is an answer I just have to find the right combination how Filemaker works.  I just can't seem to find any info that explains how Filemaker handles things like this behind the senses.  Guess that ends up being a LOT LOT LOT LOT LOT of testing and trial and error.  Even the advanced documention for Filemaker is really simple stuff and doesn't explain the workings of Filemaker so a developer knows to to work with how Filemaker works.

            • 3. Re: Calculation on related records does not work while entering a record but does after saving the records

              If it helps anyone, I was able to get this to work ...


              The problem is when a child record is added (via a layout), a layout table calculated field can Sum fields on however deep is needed (table to table relationships) but since a new record was added in a portal Filemaker has to be told to requery the records in the portal.  Since the portal only sees the records from the last query (Refresh or moving record to record) and not any new records added even though they can be seen in the portal.  Following one of the two methods below does not break a transaction.


              The only way I found to do this is a script trigger OnExitObject for the field that created the new portal record.  The script would contain the RefreshWindow script step.  However this can hurt network performance.

              Or using a cartesian join ( see article http://www.teamdf.com/weetbicks/42/ditch-those-flush-caches-use-cartesian-join-instead ) which does not hurt network performance.


              Also, values have to be passed from table to table in a relationship chain or the Sum will not work. 

              For example if the following tables were linked in a relationship:  Pymt -> Invoice -> InvoiceDetail (line items)

              You could not have a calcuated field in Pymt that Sum's amounts in InvoiceDetail.

              You would have to have a calculated field in Invoice Sum'ing Amount in InvoiceDetail, then a calculated field in Pymt Sum'ing the calculated field in Invoice.


              The above is what I have found from research and testing and I am not aware of other ways to accomplish this goal.

              Hope all the above makes sense.