9 Replies Latest reply on Jan 5, 2016 6:35 AM by jurgmay

    ESS records not updating

    jurgmay

      Hello,

       

      I have an annoyance which I'm hoping someone can help me resolve.

       

      - I have a Management Information System running on MS SQL. It runs on a dedicated server.

       

      In my FM solution I have a TO based on a 'view' from the SQL data source (let's call it 'dbo.MainJobDetails') and I have a utility layout which shows all of the fields for that view. On a separate layout which the user sees I have a portal which shows the records from the 'dbo.MainJobDetails' TO. The issue I have is that the records don't refresh as jobs are added or deleted from the SQL View.

       

      I've read these documents: 'techbrief_intro_ess.pdf' and 'best_practices_ess_fm13_en.pdf'

       

      On page 18 of 'best_practices_ess_fm13_en.pdf' it states:


      "FileMaker Pro is quick to detect certain kinds of changes to the ESS data set. Any

      operation that changes the number of ESS records, or any of their primary keys, will

      be efficiently detected and reflected within FileMaker Pro. Record creation and record

      deletion, as well as changing the primary key value for a row (if this is possible), will

      be detected relatively quickly and the relevant updates brought down to FileMaker

      Pro."

       

      So based on my understanding I would expect that any new records created within the MIS software would trigger FM to update the records in 'dbo.MainJobDetails'. Trouble is this isn't happening.

       

      If I go to the 'dbo.MainJobDetails' layout and manually select Records > Refresh Window the records get added but it doesn't work when I script it - even with 'Flush cahced external data' checked. I've tried running a 'refresh window' script on the client and on FM Server but neither of them add the new ESS records.

       

      I feel like I'm missing something very simple but I just can't figure it out.

       

      Thanks for any guidance you may have to offer.

       

      Juerg

        • 1. Re: ESS records not updating
          nicolai

          Just for testing, try to ess to the records directly, bypassing the view ( I hope your dba will not freak out). I think the way the view is working creates the refresh problem. I am surprised Refresh Window script step does not work / works differently from the one triggered by the menu.

          • 2. Re: ESS records not updating
            jurgmay

            Hi nicolai. I have news and some more strange results!

             

            Bypassing the view and accessing the MainJobDetails table directly made no difference which I found surprising.

             

            However, I've done some more testing and here's what I found:

             

            I created 4 new job records on the MIS system (which is running on MS SQL).

             

            Using a 'master' script I ran a subscript using Perform Script On Server which navigated to the MainJobDetails layout, showed all records and then did a Refresh Window with Flush Cached External Data checked. This subscript then returned Get ( FoundCount ) as the Exit Script parameter. The value returned is the CORRECT value - it was 4 more than the FoundCount on the same layout when viewed on a client PC. The server is 'seeing' the new records!

             

            So it seems that the clients aren't refreshing but the server is. What's odd though is that based on the 'Best Practices' document referred to in my original post it says that when using FM Server the ODBC connection should only be created on the server - the clients don't need local ODBC Data Source entries, so how are the clients supposed to update???

             

            There must be something here that I'm not grasping but I just can't see what...

            • 3. Re: ESS records not updating
              nicolai

              just to confirm, you should not be setting up ODBC on every client, this should be done on the Server only.

               

              Could it be a time issue. Try to introduce a few seconds delay on the client after Refresh Screen.

              • 4. Re: ESS records not updating
                jurgmay

                I have not setup ODBC on every client - it has been done on the Server only.

                 

                The Refresh Window script step now works (I had an error in my script!), however it doesn't make sense that I would have to have a script that periodically loops through a list of layouts and refreshes them. This is going to look horrible for the user!!!

                 

                I've done some more tests on a different table (essentially a list of Key/Value pairs) and the same thing happens. If I add or delete records on the SQL system they don't reflect in the FileMaker client but they do on the server...

                • 5. Re: ESS records not updating
                  nicolai

                  It makes sense as the server local client is headless and not persistent. Every time your request the data it will fetch a fresh set.

                   

                  How critical for a user to see every single record real time? If it is critical, you can use an off-screen window to refresh your datasets to prevent unwanted layout animation.

                  • 6. Re: ESS records not updating
                    jurgmay

                    Sure, that makes sense about the server.

                     

                    It's pretty essential that the job data is up to date as my operators need to see what work they have to do. The nature of our business means that we can't afford to lose too much time so it would need to be refreshed at the very minimum every 5 minutes although near-instant updates would be ideal.

                     

                    I'm not a big fan of off screen layouts but I can see that this might solve the problem so I'm going to try it. Thanks for the suggestion.

                    • 7. Re: ESS records not updating
                      nicolai

                      The other alternative would be to use a web viewer instead of portal and have JavaScript to refresh it. This should not affect FileMaker. You can ask to set up a SSPS report, it could be triggered with url. Another option is to have a service, WPF or Web API, and some web front end to display data on the web. This could be made dynamic. To be honest, most of this is outside of my expertise and I would ask dba or C# developer to do it for me, which could take them weeks.

                      • 8. Re: ESS records not updating
                        jurgmay

                        I like the sound of the JS idea. It's something I've wanted to look at and Matt Petrowsky did a webinar on using JS a few months ago but I didn't have a use case for it. This could be it...!

                        • 9. Re: ESS records not updating
                          jurgmay

                          Just to close this one off - I discovered that at some point I'd change a relationship to a Cartesian rather than 'is not equal to'. Having fixed this my portal now refreshes immediately