Why is this calc unstored?
(Note to future readers: Rick had to post a new thread because he couldn't edit his post on an iPad)
The calculation is from the context of purchaseorders, which is related to purchases with a primary key=foreign key POKeyField relationship.
I've decided to leave TotalCost alone and create a TotalCostPrint number field that is set when they click the print or email buttons.
Yet what you describe as your original setup should work and if it didn't, this section of the forum would be "flooded" with issue reports as this is a much relied upon feature of FileMaker datbases.
Might this have been an auto-entered calculation?
Otherwise, it might be a "context" problem.
The workaround didn't work; it seems for some reason Sum(purchases::ItemAmount) wasn't calculating properly there either. However, I was finally able to reproduce the problem (although that leaves me possibly even more confused).
Create a new purchaseorders record. Enter into the portal something for ItemDescription (which validates based on a value list from itemcatalog), OrderQuantity, and Unit Price. Commit the record.
Enter another purchase into the portal and commit. The TotalCost is incorrect.
Create another purchaseorders record and a purchase. Now the TotalCost for the first purchase order is correct.
There are (at least) three strange things about this sequence: 1) If you remove the validation for ItemDescription, it works fine.
2) If you remove the auto-complete using value list from the ItemDescription field, it works fine.
3) It only happens with the first record you create in your session, and if you create a second record, the first record is fixed.
Adding in a Refresh Window [Flush cached join results] seems to fix the issue (I hope). Otherwise I'll have to do something relating to items 1 and 2 above. (I'm not sure I like the current setup anyways...)
Am I correct that you have a looked up unitCost and thus this sum function sums the looked up unit cost * Qty to compute an invoice total? Can you see if the unitCost has correctly auto-entered on each line item when this sum function fails to correctly sum values?
The auto-enter calc has nothing to do with it; it works the same with a regular number field. I found a different way to reproduce the error: 1) Add one related record, commit.
2) Add two related records (it's interesting how the sum calculation is temporarily correct), commit
3) Add something invalid in another related record's description, revert, revert record.
Entering something invalid in the description and overriding it works the same as entering something valid.
Here's a demo file: https://www.dropbox.com/s/8hezrbvm290ll3p/test2.fmp12?dl=0