Summary

Unstored calculation not updating sometimes

Product

FileMaker Pro

Version

13.0.9

Operating system version

Windows Server 2008 R2 as terminal server, hosted on a different server

Description of the issue

Several users enter a purchase order with several line items, and on rare occassions the total cost prints incorrectly. The formula for TotalCost, an unstored calculation, is If(IsEmpty(purchases::POKeyField); 0; Sum(purchases::ItemAmount) + FreightChg + Tax + FuelChg). purchases::ItemAmount, an auto-enter replaces existing, is OrderQuantity * UnitPrice.

On two examples I was sent, the total cost did not include the last item. The item amounts for the two items printed correctly however.

Even stranger is one where there was only one item, and the total cost was equal to the inventory's unit price of $1.78. The purchase unit price does a lookup to the inventory, but that had been overwritten to $2.30 and a quantity of 1500.

I'm not sure when the calculation becomes correct, but when I look at it, it is correct. I believe somebody said they printed it more than once and it was wrong, then they asked somebody else to look at it and it was correct.

I tried adding Set Variable [$TotalCost; purchaseorders::TotalCost] to try to make it recalculate but it didn't work.

Steps to reproduce the problem

I haven't been able to reproduce it, but the print button commits records and goes to the related records and updates some fields (this shouldn't be necessary, but it's a migrated system and I haven't gotten around to modifying the reports, etc. based on this). It goes to a print layout (based on purchaseorders) and prints with dialog.

Expected result

Total cost prints correctly.

Actual result

Total cost doesn't account for all line items or uses old data I guess.

Workaround

I guess making TotalCost a number field and updating it with script triggers.

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?