Flush cached SQL data

[LaRetta]

[color:blue]Refresh Window

Option: Select Flush cached SQL data to delete the results of queries for related ODBC data source records and cause related ODBC records to be refreshed. FileMaker Pro dumps the internal cache and refreshes record data. Do not select this option if you know your script does not access ODBC data.

Description: Updates the entire contents of the FileMaker Pro document window, including any related records. You do not need to select Flush cached join results in order to refresh the entire window.

My graph (SQL tables to Router table to FM tables)

SQL table::Key = Router::Key

then

Router::RouterID = FMtable::RouterID (RouterID is auto-enter serial)

Router is used as interface between incoming ESS data and FM (80% of FM data arrives from outside sources). I import the SQL Keys only (according to varying criteria) then use the relationships to add/update/delete my FM data. Because I handle everything from the Router (using Set Field[] and Allow Creation), SQL data is never displayed and the process of 'synchronizing' is very fast.

Concerns:

After import, I have a found set which I need to loop through to do the work. Script checks the SQL data and writes accordingly into FM but I'm concerned that not all of the SQL records will be there if I don't first perform a Flush cached SQL data. I could test for related SQL on each looped record but that takes additional time.

Questions:

1) Does 'Refresh of SQL data' only refresh the DISPLAY of the data (which I don't care about) or does FM need the refresh to see changes or new SQL records in the relationship? FM says it 'refreshes record data' and 'updates the entire contents of the document window' and, even though I've read it over and over, it still isn't clear to me. 'Updates document window' sounds likes display only but 'refreshes record data' appears to be at the record level itself. I don't want to refresh SQL between each record; nor do I want to test every record, using IsEmpty(),to be sure FM knows about the related record.

2) If I need to refresh, do I need to refresh each SQL table independently or will one refresh handle it for all tables? I see no option to specify WHICH SQL table data should be refreshed so I'd assume it refreshes them all but I would like confirmation.

3) Regardless of how often I refresh, and since my import will always be real-time, I might hit situation that a record I want to write through isn't there! Whether I use error capture or testing or whatever, I still don't want to assume the work is done on a record if it isn't. How best can I be SURE that this process will not fail?

I tried to keep this very short. Help would be greatly appreciated.

LaRetta

[jgonzalez]

Were you able to resolve your update issue?

I am having a very similar issue with ESS and MySQL. I am using a MySQL View to see records from MySQL in FileMaker. When I add a new record, I switch to a layout the uses a base-table in MySQL. However, when I switch back to the layout based on the 'view', the new record is not there. I have tried the Refresh Window command, with all the options, and I still can't see my new record. What is wierd is that FileMaker (in the status bar) briefly flashes the correct record count, then reverts to the old record count when the screen is re-drawn.

Joe

[vitorc]

Were you able to resolve your update issue?

I am having a very similar issue with ESS and MySQL. I am using a MySQL View to see records from MySQL in FileMaker. When I add a new record, I switch to a layout the uses a base-table in MySQL. However, when I switch back to the layout based on the 'view', the new record is not there. I have tried the Refresh Window command, with all the options, and I still can't see my new record. What is wierd is that FileMaker (in the status bar) briefly flashes the correct record count, then reverts to the old record count when the screen is re-drawn.

Joe

I'm having the same problem with Mysql views.

When I update some data they do not appear in filemaker (even using the Refresh Window (flush cached SQL data)).

Only works if I close and open filemaker.

Has anyone had this same problem? solution exists?

[vitorc]

I'm having the same problem with Mysql views.

When I update some data they do not appear in filemaker (even using the Refresh Window (flush cached SQL data)).

Only works if I close and open filemaker.

Has anyone had this same problem? solution exists?

I found a solution!

When the Refresh Window command is used, it only updates the data in the current table.

I made a script that opens a new window on the table layout that I like, run the refresh window (with flush cached SQL data) and closes the screen.