6 Replies Latest reply on Aug 5, 2015 6:29 AM by Timothy Bentley

    Unstored calculation not updating sometimes

    Timothy Bentley

      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.

        • 1. Re: Unstored calculation not updating sometimes
          RickWhitelaw

          Why is this calc unstored?

          • 2. Re: Unstored calculation not updating sometimes
            Timothy Bentley

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

            • 3. Re: Unstored calculation not updating sometimes
              philmodjunk

              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.

              • 4. Re: Unstored calculation not updating sometimes
                Timothy Bentley

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

                • 5. Re: Unstored calculation not updating sometimes
                  philmodjunk

                  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?

                  • 6. Re: Unstored calculation not updating sometimes
                    Timothy Bentley

                    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