4 Replies Latest reply on Oct 20, 2010 1:01 PM by scottworld

    Unstored calc doesn't update, but related field does



      Unstored calc doesn't update, but related field does


      FileMaker Pro



      Operating system version

      Mac OS X 10.6.4

      Description of the issue

      There is an issue in FileMaker Pro where a related timestamp field placed on a layout will instantly update on the layout whenever that field's value changes. However, an unstored calculation referencing the exact same related field will NOT update until the user leaves the layout and comes back to it.

      Steps to reproduce the problem

      Create an invoice table, and an invoice line items table. Relate them by Invoice ID.

      In the line items table, create a timestamp field called "Modified Timestamp" which auto-enters the modification timestamp.

      Now create another relationship from the invoices to the line items. Let's call this relationship "Invoice Line Items TIMESTAMP SORT", which relates the Invoice ID in the invoice table to the Invoice ID in the line items table, and the relationship is reverse chronologically sorted by the "Modified Timestamp" field. Therefore , the first related record is always the most recently-modified line item.

      If you're on your master invoice layout, and you place the related field "Invoice Line Items TIMESTAMP SORT::Modified Timestamp" directly on your master invoice layout, it works just as you would expect. The related field ALWAYS updates instantly to show you the latest time that any of the related line items have been modified. It works just fine, and it updates instantly.

      Okay, so go ahead and REMOVE THAT RELATED FIELD FROM YOUR LAYOUT. That is crucial, in order to see this bug in action.

      Next, if you create an UNSTORED CALCULATION in the master table with the EXACT SAME expression (Invoice Line Items TIMESTAMP SORT::Modified Timestamp), and place that unstored calculation on the layout, that field does NOT update instantly. The user has to refresh the layout in order to get the unstored calculation field to update with the latest information.

      Even though that calculation is unstored, and even though that calculation is nothing more than pointing to that very same related field that you had placed on the layout earlier, it doesn't update instantly... until a refresh.

      Funny enough, if you go ahead and put BACK the original related field on the layout somewhere that the user can't see it, the very fact that the related field is sitting on the layout somewhere does actually force the unstored calculation to update instantly. But you shouldn't have to hide a field on a layout just to get an unstored calc to update properly.

      Expected result

      The expected result is that both the related field on the layout AND the unstored calculation referencing that same related field should each update instantly, without requiring a refresh of the layout or without requiring both fields to be on the layout. The unstored calculation should automatically & instantly refresh on its own, without needing any other related field on the layout.

      Actual result

      The actual result is that the related field on the layout always updates instantly, whereas the unstored calculation field NEVER updates instantly (unless the related field is present on the layout).

      Exact text of any error message(s) that appear


      Configuration information



      Workaround mentioned above.

        • 1. Re: Unstored calc doesn't update, but related field does


          Thank you for your post.

          I am unable to replicate the problem.  As long as the portal is sorted, the unstored calculation field does update (for me) without the Modified Timestamp field on the layout.

          Here are my details:

          INVOICES table:
          ID (Text)
          Name (Text)

          LINE ITEMS table:
          ID (Text)
          Amount (Number)
          Modified Timestamp (Timestamp)
          Calc Timestamp (= Modified Timestamp, Timestamp result, Unstored)

          I set a relationship between INVOICES and LINE ITEMS based upon ID.  I set LINE ITEMS to sort by "Modified Timestamp" Descending.

          On the INVOICES Layout, I created a portal into LINE ITEMS displaying Amount and Calc Timestamp, and sorting by Modified Timestamp Descending.

          In INVOICES, I entered one record where ID=1 and Name="TSGal"

          In LINE ITEMS, I created five records with all ID=1.  Nothing in Amount.

          Returning to INVOICES layout, I see the five records in the portal.  In the last listed record, I entered "5" into Amount, and when I committed the record (clicking outside the portal), the record moved to the top in the portal, and the Calc Timestamp field was updated properly.

          Let me know what I'm doing differently than you.

          FileMaker, Inc.

          • 2. Re: Unstored calc doesn't update, but related field does

            Hi TSGal,

            Here's the difference between what you did & what I did:

            1. Do not put that sorted portal on your layout. You can put a "generic" unsorted line items portal on your layout, but don't put the sorted portal on your layout. Otherwise, it refreshes the value properly.

            2. Do not display the "Line Items::Calc Timestamp" field within the line items of your portal. Otherwise, it refreshes the value properly.

            3. You still need to add an unstored calculation field in your Invoices table entitled "Calc Timestamp" which is equal to the value of "Line Items::Calc Timestamp". Put this unstored calculation field on your Invoices layout. This is the field which is not refreshing properly.

            4. Do not switch back & forth between layouts... doing this refreshes the value properly. Simply add or modify new records directly within the unsorted portal.



            • 3. Re: Unstored calc doesn't update, but related field does


              Thank you for the additional information.

              The key point is #3.  If I add an unstored calculation field in the Invoices table that references Calc Timestamp in the LineItems table, then it will only reference the first record in the portal.  Changing the first entry in the portal, the unstored calculation field will update properly while changing the second or other entries will not update.  What you need is to reference all the related records using the Max function.  That is,

              Max ( Line Items::Calc Timestamp )

              Now, whenever you change any related record, the unstored calculation will display correctly.

              FileMaker, Inc.

              • 4. Re: Unstored calc doesn't update, but related field does

                Thanks for that great workaround, which I will use.

                However, the bug still remains because I had a typographical error in my #3 above. I didn't mean to type #3 the way I typed it. Sorry about that.

                What I'm trying to do is exactly what you suggested. I am trying to ONLY show the first record in the relationship. My relationship is:

                Line Items Reverse Sorted By Calc Timestamp::Calc Timestamp


                Line Items::Calc Timestamp

                My mistake. As you can see, I'm specifically trying to show only the first record in the REVERSE SORTED relationship, which should always be the most recent "Calc Timestamp".

                Basically, it should be the equivalent of what you said, which is Max (Line Items::Calc Timestamp).  

                I'll just use the Max workaround for now, but the relationship above should technically work also, but it doesn't.