4 Replies Latest reply on Jun 29, 2016 1:12 AM by RafaelCaballe

    Updating ESS shadow tables

    RafaelCaballe

      Hello,

       

      I'm working on a solution that needs to gather some information from a MS SQL database. I have set up the proper driver on FMS, defined de DSN and get the shadow table in the Relationship Graph. The MS SQL table is very straight forward: a single record with 10 fields which contain numeric values that are updated every second. The numeric values are counters which are update by an SCADA system.

       

      From FileMaker I have set up an OnTimer script, to read, every second, the values of the fields in the MS SQL table. The purpose is to show in a layout the current counter value, as information of the manufactured units (that is what the counter means).

       

      The fact is that the ESS table is not updated, as I expected, so I do not get the correct information.

       

      Is there any means to get this ESS shadow table updated?

       

      Thanks.

      Rafael

        • 1. Re: Updating ESS shadow tables
          coherentkris

          What are you doing in your on timer script?

          • 2. Re: Updating ESS shadow tables
            LSNOVER

            Are you using Refresh data?   ESS data does not self update in Filemaker.  You must refresh it yourself.

            • 3. Re: Updating ESS shadow tables
              RafaelCaballe

              The SCHEMA is as follows:

               

              Tables

              • Table 1: LINE
              • Table 2: DETECTION (ESS shadow table)

               

              Fields

              To simplify lets consider just the following fields:

              • LINE::id (serial number)
              • LINE:counter
              • DETECTION::id (serial number)
              • DETECTION:counter01
              • ...
              • DETECTION:counter10

               

              Records

              • LINE: 10 records
              • DETECTION: 1 record

               

              Relationship

              • LINE - DETECTION: cartesian type ( x ) id-id

               

              The SITUATION is as follows:

               

              • I am sitting on a layout based on LINE.
              • I have installed a OnTimer script on the window of  this layout.
              • What I want to accomplish is to set, every second, LINE::counter with the corresponding DETECTION:counterNN. So, if I am on Line 03 I want to set LINE::counter = DETECTION:counter03.

               

              The PROBLEM is that:

               

              • I get the LINE::counter set but with a non-updated value of its corresponding DETECTION:counterNN. The only way I get it running is to go to a layout based on DETECTION, Refresh Window, return to original layout and then perform the set field script. But, in order to provide an acceptable user experience, I can't change layout.
              • What I have tried is to add a Refresh Window script step in the OnTimer script with both options checked (flush cached join results & flush cached external data). I thought that, due to the relationship LINE - DETECTION defined above, this step will force to update the data on the shadow table. But it does not...

               

              What I need is a mechanism to refresh an ESS table without being required to go to its context...

               

              I hope is there a way...

               

              Rafael

              • 4. Re: Updating ESS shadow tables
                RafaelCaballe

                I found a solution including a Perform Script On Server in the OnTimer script. The script performed on server, does: goes to layout based on DETECTION, performs a Refresh Window [Flush cached external data] and exit script passing a return delimited list with all ten DETECTION:counterNN.

                 

                The OnTimer script gets the script result into a variable which contains all the counters values ready to make all required calculations.

                 

                By means of the PSOS, the user context does not change at all, since the ESS table update is carried on by the server.

                 

                -Rafael