1 2 Previous Next 15 Replies Latest reply on Apr 25, 2014 10:10 AM by philmodjunk

    "Count" Function Fails to Update for External Data Sources

    LarryGoldman

      Title

      "Count" Function Fails to Update for External Data Sources

      Post

           Re: FMPA 13v2

           I am developing a "data separation model" FileMaker solution, where there is a Data file which contains all records, and a UI file which _references_ the Data file as an External Data Source.

           In the Data file, there is an Invoices table and an Invoice Items table. The Invoices table defines a field which "Counts" the number of Invoice Items related to it. When viewing Invoice records in the Data file, adding and removing Invoice Items properly updates the "Count of Invoice Items" value.

           But, in the UI file, where the layout is displaying Invoice records from the external data source, the "Count of Invoice Items" value is _not_ properly updated when related records are added or removed. The value stays "wrong" until the user clicks in the "Count" field in the UI file -- and then the value suddenly updates.

           How to work-around this update bug in FileMaker 13?

        • 1. Re: "Count" Function Fails to Update for External Data Sources
          philmodjunk

               You should not need to click the count field. Clicking any blank part of the layout outside of the portal should do it. This commits the record and enables the calculation field to update.

               A commonly used adjustment for this issue is to add a script trigger to commit records at the appropriate time.

               But if you really can't get the field to update without clicking into the field, make sure that "number" was selected as the result type for that calculation field.

          • 2. Re: "Count" Function Fails to Update for External Data Sources
            LarryGoldman

                 The problem happens when you are adding and removing "Invoice Item" records in the data file, but viewing the results in the UI file.  

                 Working in the data file, the Count field updates correctly. But the same Count field in the same Invoice record in the UI file is not updated properly -- the UI file's Count value does not keep up with changes to the data file's.

                 Do you see the problem?

            • 3. Re: "Count" Function Fails to Update for External Data Sources
              philmodjunk

                   That is exactly what I understood from your original post. But when I test this, I do not need to click the count field. I only need to commit records by clicking any blank area of the layout.

              • 4. Re: "Count" Function Fails to Update for External Data Sources
                LarryGoldman

                     When you are trying this, you need two windows open: the data file and the UI file, both viewing the same Invoice record. Work in the _data_ file, adding and removing related records. Note that the data file "Count" field updates correctly, while the UI's display of the same field in the other window does not.

                     This corresponds to running a script on the server that adds/removes related records... the count is not correct on the user's screen -- until the (calculation) field is clicked.

                     Can I send you test files?

                • 5. Re: "Count" Function Fails to Update for External Data Sources
                  philmodjunk

                       This is exactly what I did. Please check the result type specified for your calculation field. Please confirm whether or not the count updates when you click a blank area of the layout.

                  • 6. Re: "Count" Function Fails to Update for External Data Sources
                    LarryGoldman

                         I'm not seeing my mistake. The Calculation field is numeric. The count in the data file updates OK, but the UI's view of the same field does not.

                         Here is a link to a zip file of a UI/Data combination. Can you see the problem?

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

                    • 7. Re: "Count" Function Fails to Update for External Data Sources
                      philmodjunk

                           Your interface file is designed differently from mine. Since editing line items data from a layout in the Data file made no sense to me, in my test files, I set up a layout in the interface file that has a portal to lineitems. Thus the same relationship is defined in both files and the changes are made from the context of the UI file not the data file and thus commit records is all that was needed to update the displayed count.

                      • 8. Re: "Count" Function Fails to Update for External Data Sources
                        LarryGoldman

                             Your interface is designed differently because our use-cases are different.

                             In my case, the UI file does not contain a portal -- consider it a manager's screen where the only important number is an up-to-date count of records added or delete by other users. In this case, the calculation field in the data file updates correctly, while the same calculation field displayed in the UI file does not.

                             Can you get my test file to work, where adding and deleting records in the data file displays an accurate and timely count in the UI file?

                        • 9. Re: "Count" Function Fails to Update for External Data Sources
                          schamblee

                               This is a refresh window issue and not really a bug but a design issue.  The Test Count UI windows needs to be refreshed to show the updated count.   I added  Install OnTimer script step to the OnFirstWindowOpen Script, which runs a script to refresh the window.  I tested an interval of 5 and 1.  You may have to play around with the interval to get something that updates at a reasonable amount of time.

                          • 10. Re: "Count" Function Fails to Update for External Data Sources
                            philmodjunk

                                 S Chamblee has illustrated the key issue: There is no event in the so called "UI FIle" to trigger the needed update. Add a timer manufactures that missing element. In your working database copy, there may be other methods that use a script trigger that may serve.

                            • 11. Re: "Count" Function Fails to Update for External Data Sources
                              LarryGoldman

                                   PhilModJunk said:

                                   

                               There is no event in the so called "UI FIle" to trigger the needed update.

                                   This is true, but update events for this calculation _do_ exist in the data file, because the field there gets updated properly. The problem I am reporting here is that update events for calculations involving the "Count" function do _not_ get passed through the External Data Sources divide -- they are lost.

                                   Other types of fields (numeric, text, etc.) work as expected. Other calculations (simple addition, for example), work as expected: updates in the "UI" file track changes made by other users in the data file.

                                   Only certain functions require the kludgey, inefficient and inherently inaccurate work-around of an update timer. Another possible work-around is to avoid the use of External Data Sources, because that is the divide that the update events for certain calculation functions, such as Count, do not pass through successfully.

                              • 12. Re: "Count" Function Fails to Update for External Data Sources
                                philmodjunk

                                     Yes, but to repeat, there is no event taking place in your UI file to trigger the needed update. You can report this as a possible bug in Report an Issue (see tab at top of this screen) if you want, but I'm not convinced that this really qualifies as a bug. The TS personnel that monitor Report an Issue are, of course, free to disagree with me on this. wink (Keep in mind that I don't work for FileMaker. I'm just a fellow user expressing his personal opinion.)

                                     

                                          Other calculations (simple addition, for example), work as expected: updates in the "UI" file track changes made by other users in the data file.

                                     But Sum or count are aggregate functions that refer to multiple related records in a table not even directly referenced in your UI file. This is a much different thing than a "simple addition" calculation field that combines data from different fields in the same record or one that even references single related records as part of the calculation.

                                • 13. Re: "Count" Function Fails to Update for External Data Sources
                                  schamblee

                                       The UI is not passing any information to the external source.  Your UI is retrieving information from an external data source which is being updated when a user makes changes with the database, the UI does not know that the external data source has changed because it is external.  The refresh cause the UI to query the external data source to up date the screen.   I agree with PhilModJunk, this is not a bug.

                                  • 14. Re: "Count" Function Fails to Update for External Data Sources
                                    LarryGoldman

                                         S Chamblee said:

                                         

                                              The UI is not passing any information to the external source. 

                                         Well, that statement contradicts the whole point of the data separation model in which users manipulate the data file exclusively via the UI file. Users pass information to the data file; changes in state of the data file, even if made by other users, _must_ be communicated back to the UI. 

                                         

                                              ...the UI does not know that the external data source has changed because it is external.

                                         This statement is easily disproven by noting that other calculations and field types update automatically, natively. Of course the UI file is notified of changes in state of the data source it is linked to. 

                                         

                                              I agree with PhilModJunk, this is not a bug. 

                                         You can call the program's obvious failure to display accurate and timely calculation results whatever you like. For my purposes, this errant behavior is unacceptable. Timer refreshes are inefficient and inaccurate. My solution is to avoid the use of External Data Sources (because they do not work reliably) and to proceed with my development. 

                                         Thanks, S Chamblee

                                    1 2 Previous Next