5 Replies Latest reply on Jun 7, 2012 11:52 AM by DrewTenenholz

    Shadow tables in FileMaker


      Good morning again,


      Im trying to understand how -up-to-date a shadow table keeps itself.


      Here is what i did so we are all on the same page:


      I created an External Data source in FMP that uses an Oracle ODBC.


      Then, i went into managedatabse elationships and pressed the ADD table button.

      then i selected the Oracle ODBC from the data source drop-dwon menu and selected the right view from oracle that i need data from.


      So i know have 1 table on my relationship diagram page and 1 layout was created by default for that 1 table with populated rows.


      I then moved to the tables TAB and i can also see 1 table showing, with the slight difference that it as a SYNC button.

      The sync button when pressed seams to be able to sync to that oracle table and return any new fields that might of been created since last i checked.

      (im assuming at this point based on mesaage)


      My question is this:


      Is my shadow table in a LIVE-SYNC mode with oracle table? Do i need to refrsh that layout (so-to-speak) in order to keep the data in that shadow table updated?


      Or is it a LIVE-SYNC and any changes made on that ORACLE table (record wise) will right-away show into that shadow table?


      Thank you very much, please dont hesite to ask for more info if you need too

        • 1. Re: Shadow tables in FileMaker

          Hellp patpai.


          The Sync button in the Manage Database dialog is for keeping the database schema (the fields) synchronized with the back-end Oracle table. The data will stay refreshed at all times, with the exception that you won't see changes other users make on the record where you're sitting without something that causes the window to refresh, like switching layouts, switching records, or using a Refresh Window script step.





          • 2. Re: Shadow tables in FileMaker

            thats exactly what i was looking for, thank you for comforming how it all works.



            • 3. Re: Shadow tables in FileMaker

              Here is from a FileMaker hosted white paper ...



              Page 18


              Learning About Changes


              When working with native FileMaker Pro data, FileMaker Pro's persistent

              client-server connection allows all connected users to be notified about

              data changes rapidly and completely.  Deleted records will vanish from the

              screens of other users, and data changes will flow directly into other

              users' data sets.  Much of this occurs because the FileMaker Pro host

              broadcasts notifications of data changes to connected clients.


              Things are different with ESS tables. FileMaker Pro has no means to receive

              notifications from an ESS server, even if the ESS server provided such

              notifications, which they do not. FileMaker Pro clients can only learn of

              changes to ESS data by querying the server and comparing the server's data

              to what is currently cached in FileMaker Pro.  This means that, in general,

              ESS data in FileMaker Pro has the potential to be slightly out of date,

              which is never a concern with native FileMaker Pro data.


              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.


              Record edits are a different matter.  If another FileMaker Pro user, or a

              non- FileMaker Pro user, changes data in an ESS row, other FileMaker Pro

              users viewing that row will not be immediately aware of the change.  The

              change will not be detected until FileMaker Pro has reason to clear that row

              from the data cache and re-fetch it, for example, when a new search is



              Because of the potential for data visible on screen to get out of synch with

              the data on the server, FileMaker Pro 9 or later provides the Refresh Window

              command in the Records menu.  Refresh Window will drop any displayed records

              from the data cache and refresh their values from the ESS server.  The

              Refresh Window script step now also has a corresponding ³Flush Cached SQL

              Data² option.


              Hope that helps.


              Might have more to say on this later. Testing here.


              All the best,



              Tony White

              Tony White Designs, Inc.

              Tel: 718-797-4175



              • 4. Re: Shadow tables in FileMaker

                thx Tony,


                I did incorporate the Refresh window in my script when im updating the ESS table.

                I did notice also that trying to build a Value LIst form an ESS table is not possible since you cant realy indicate that a specific field needs to be indexes, since its not your table per say.


                I did do a mirror image of that said table and on that mirror table, i can build calculation fields that, in turn, can have indexes.


                You guys come across that as well and is there a better way to handle that said limitation?


                Else if i could build index fields on my ESS tables, all i would need is perhaps a refrsh Screen little script and thats it.


                Its a work in progress

                • 5. Re: Shadow tables in FileMaker

                  Patpai --


                  As the article Tony quotes says, ESS table data can quickly go out of sync with the what FileMaker suers will see.  This is also true for your copied table to create indexable data for a value list.


                  It may help to know that the new FM12 Script Step  Execute_SQL() can be used on a FileMaker table OR any Table Occurrence on your relationship graph, including ESS table occurrences. 


                  It might be more efficient to use a short script that runs Execute_SQL() and stores the result into a field in a single-record table in FileMaker.  The field would be indexable, and FileMaker will produce a multi-line value list from return-separated values in a single field as easily as it does for single values in a multi-record table.  You should get the same result, but possibly faster and with a more reliable way to keep the index/data in sync with the ESS table.


                  -- Drew Tenenholz