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.
thats exactly what i was looking for, thank you for comforming how it all works.
Here is from a FileMaker hosted white paper ...
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
Hope that helps.
Might have more to say on this later. Testing here.
All the best,
Tony White Designs, Inc.
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
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