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

Discussion created by todd- on Oct 22, 2014
Latest reply on Oct 27, 2014 by todd-

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.