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.