2 Replies Latest reply on Nov 30, 2011 4:14 PM by philmodjunk

    Looked up value fields do not look up sub totals if they refer to a summary field

    philmodjunk

      Summary

      Looked up value fields do not look up sub totals if they refer to a summary field

      Product

      FileMaker Pro

      Version

      11v3

      Operating system version

      Windows XP, SP3

      Description of the issue

      In all other parts of FileMaker (as far as I can tell), a reference to a summary field in a related table returns the total of the related records--not the total of the entire table.

      Here is a demo file illustrating the bug: http://www.4shared.com/file/uPqzfV2I/ProdRptJobSummDemo.html
      create a new record on the job summary layout and enter either a 1 or a 2 in the salesOrder field and note that two columns correctly display the subtotal but the "failedOrderTotal" field displays the total of all the records in the table instead of the expected subtotal.

      Expected result

      If I enter a SalesOrder of 1, I should see a total of 55 and a total of 12 if I enter a 2.

      Actual result

      In both cases, the looked up value field returns 67--the total of all records in the related table.

      Workaround

      Use an auto-enter calculation that refers to the summary field instead of a looked up value field option.

      LookedUpSummaryBug.JPG

        • 1. Re: Looked up value fields do not look up sub totals if they refer to a summary field
          GianandreaGattinoni

          Phil,

          I think there is somethong wrong in the approach.

          despite the fact that there are better ways to get the total (like a calculated fiedl equal to Production Report::sTotalQty), I added other records and the FailedOrderTotal does not change (it remains 67).

          I think that you create the FailedOrderTotal field having already the 2 records in product. FM enter the totals but never updated it.

          ciao

          Gianandrea

          • 2. Re: Looked up value fields do not look up sub totals if they refer to a summary field
            philmodjunk

            With all looked up value fields, changes to the source table (production report) do not automatically update values in this field in the Job Summary table. This is standard, expected behavior for looked up value fields, been that say since fileMaker 3 was released and we could finally link tables in relationships. To update the displayed totals in such a looked up value field after such changes are made, you'd need to put the cursor in the key field (salesOrder) and choose relookup from the records menu (updates all records in current found set) or re-edit the key field (updates just the current record).

            Keep in mind that this file demonstrates an unexpected result. It is not intended to suggest a best approach for the design of any specific database. Sometimes you need such subtotals to update as the source data changes and sometimes, (such as product prices in a products table), you do not. FileMaker gives you both options. You just have to pick the method that works for you.

            You may find this discussion of both approaches useful: Auto Fill