6 Replies Latest reply on Apr 17, 2010 1:19 PM by JoshO.

    How can I refresh the SQL data, displayed from a remote table? (ODBC)

    gczychi

      Title

      How can I refresh the SQL data, displayed from a remote table? (ODBC)

      Post

      Hi,

       

      how can I let FileMaker refresh the data that is displayed so nicely from my remote database?

       

      The steps that I did so far: I have added the remote table to the Relationships, created a layout based on the remote (MySQL) table, the data is downloaded and displayed, but never refreshed on screen (except when I close the FM file and reopen). I need up-to-date values (in case the MySQL data has been changed in the meantime).

       

      Thanks a lot for any help!

       

      Gary

       

        • 1. Re: How can I refresh the SQL data, displayed from a remote table? (ODBC)
          JoshO.

          They are semi-live.

           

          Whenever the screen refreshes, it should update the data you are seeing.

           

          Some Actions that Refresh the Screen:

           

          • Changing layouts.
          • Changing Records.
          • Refresh Window function.
          • Refresh Window script step.
          • Change Mode (Layout, Browse, Find)

           

           

          There is not a constant broadcast of the data to FileMaker, so it doesn't know that data has changed...like it does with FileMaker native tables.

          • 2. Re: How can I refresh the SQL data, displayed from a remote table? (ODBC)
            gczychi

            Hi,

             

            I have tried all of your suggestions, but the data on screen is not refreshed. Only closing the file and re-opening it from the Finder refreshes the display.

             

            Any clues to what I can do?

             

            Thanks a lot,

             

            Gary

            • 3. Re: How can I refresh the SQL data, displayed from a remote table? (ODBC)
              gczychi

              I have done some more tests and found that even script functions return the value that is shown on screen and not the (updated) value in the remote table!

               

              It seems that FM Pro behaves like this:

              When you open a remote table for the first time, it reads a «chunk of data» from the remote table and displays it. The data stays there never-endlessly, until 1) the current file is closed, or 2) you browse through the records until you reach a record that is not in FM's local copy. Then the new data is retrieved and displayed. The new data is then appended to FM's local (and outdated) data.

               

              An interesting thing happens when the local user changes the local data and it gets committed. FileMaker finds out that there is something wrong and displays the following dialog box:

               

              «This record was modified by another user since you started editing it. Do you want to save your changes anyway and possibly overwrite their changes?»

               

              This behavior would be acceptable, if there was a way to refresh the data displayed on screen at will. But here we are at the same question.

               

              Gary

               

              • 4. Re: How can I refresh the SQL data, displayed from a remote table? (ODBC)
                JoshO.

                What version of MySQL are you using?

                What ODBC driver are you using?

                What version of FileMaker?

                How do you have the DSN setup?  As a System DSN, correct?

                In the Refresh Window step, do you have the "flush cache to SQL" (or whatever it says) option checked?

                 

                We use the ESS feature all the time at our office...and it works fine.  There has to be something that is preventing it from seeing the new data.

                • 5. Re: How can I refresh the SQL data, displayed from a remote table? (ODBC)
                  gczychi

                  » What version of MySQL are you using?

                  mysql  Ver 14.14 Distrib 5.1.45

                  » What ODBC driver are you using?

                  Actual V. 3.0.8

                  » What version of FileMaker?

                  FM Pro 11.0v1 Advanced

                  » How do you have the DSN setup?  As a System DSN, correct?

                  Yes.

                  » In the Refresh Window step, do you have the "flush cache to SQL" (or whatever it says) option checked?

                  No... ...OMG

                   

                  I tried it  --  and now it works! Wow, that was a difficult one.

                   

                  Isn't it strange that this is the only thing that updates the values?

                   

                   

                  Thanks so much,

                   

                  Gary

                   

                  • 6. Re: How can I refresh the SQL data, displayed from a remote table? (ODBC)
                    JoshO.

                    I don't have 11 to test with (I have FMA 10).  So I can't say for sure.

                     

                    In 10, I only need to navigate to a new record and it displays with any changes made to the MySQL tables.  It is possibly a bug, but you will have to get some more input from TSGal or one of the community leaders for that.