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