3 Replies Latest reply on Apr 24, 2014 12:08 PM by philmodjunk

    Update Events for Aggregate Calculations are Lost for External Data Sources

    LarryGoldman

      Summary

      Update Events for Aggregate Calculations are Lost for External Data Sources

      Product

      FileMaker Pro

      Version

      FMPA 13.0v2

      Operating system version

      Mac OS X 10.9.2

      Description of the issue

      Suppose a "data separation model" FileMaker solution, where one file contains all data and another contains just the user-interface -- connected to the data file via an External Data Source.

      If the data file contains a calculation Field which uses the "Count" function to count related records, the calculation result updates correctly in data file layouts, but does not update in the UI file until the field is clicked.

      Update events from the data file are not being sent to the UI file via the External Data Source facility. They are lost.

      This problem does _not_ occur for other kinds of fields (numeric, text, etc.) nor for calculation fields that use other functions (simple arithmetic, for example). For those, updates in the data file are properly tracked in the UI file.

      Steps to reproduce the problem

      See my test files that demonstrate the problem:

      http://www.allstarcomputerservices.com/media/fmp/testcount.zip

      Also, see this FM forum discussion:

      http://forums.filemaker.com/posts/a94cc4faae?commentId=287161#287161

      Expected result

      If a calculation field displays a correct result in one layout in one file, that same calculation field for that same record in a External Data Sourced file should display the same result.

      Actual result

      For aggregate functions such as "Count", the correct updated calculation result is _not_ displayed correctly in the UI file until the user clicks in the _calculation_ field there to force an update, or until the user's window is refreshed by some other means.

      Workaround

      Either use a timer to force a window refresh periodically (kludgey, inefficient, inaccurate), or just don't use External Data Sources, because they do not work reliably with aggregate function-based calculations.

        • 1. Re: Update Events for Aggregate Calculations are Lost for External Data Sources
          philmodjunk

               Having seen these files when discussing them in the other thread, I must quibble a bit here.

               These files are not true examples of the data separation model as there is an interface layout in the data file used to produce the needed changes to records in the related child table. In a true use of the data separation model, such a layout would not exist in the data file, it would be placed in the interface file and then such calculations update much more smoothly simply by committing records after making such a change.

          • 2. Re: Update Events for Aggregate Calculations are Lost for External Data Sources
            LarryGoldman

                 Of course the data file has a layout, used for debugging, because it accurately reflects the state of the data there.

                 If you think "working in the data file" is the problem, try this:

                 Copy the "Add" button from the Data file to the UI file. Use it to call the identical "Add Invoice Item" script in the _data_ file, passing "Invoices::Invoice UUID" as the script parameter. If the test files are on on a FileMaker server, try the "Perform Script on Server" step, instead.

                 With the UI file open, press the Add button a few times and observe that the Count value there does not update, does not accurately reflect the state of the external data source to which it is linked -- without resorting to a timer based window refresh work-around. Other calculations and other types of fields _do_ update properly, natively.

                 Do you see the problem?

                 BTW, I appreciate you taking the time to help me refine my understanding of this and for suggesting a work-around.

            • 3. Re: Update Events for Aggregate Calculations are Lost for External Data Sources
              philmodjunk
                   

                        Copy the "Add" button from the Data file to the UI file. Use it to call the identical "Add Invoice Item" script in the _data_ file, passing "Invoices::Invoice UUID" as the script parameter. If the test files are on on a FileMaker server, try the "Perform Script on Server" step, instead.

                   This still performs interface tasks in the data file. From a design point of view, you should not set up this kind of script in the data file since updates to such a script then require deploying new data files and importing data into the new data file--the very thing we try to minimize by keeping all such features in the interface file.

                   But if you are going to use a script in the interface to call a script in the data file, that becomes a very obvious place to put in an added step to refresh the window in your interface file and is an example of what I meant when I posted in the other thread that there were likely ways to automate refreshing this total that did not require Install OnTImer Script.