Just for testing, try to ess to the records directly, bypassing the view ( I hope your dba will not freak out). I think the way the view is working creates the refresh problem. I am surprised Refresh Window script step does not work / works differently from the one triggered by the menu.
Hi nicolai. I have news and some more strange results!
Bypassing the view and accessing the MainJobDetails table directly made no difference which I found surprising.
However, I've done some more testing and here's what I found:
I created 4 new job records on the MIS system (which is running on MS SQL).
Using a 'master' script I ran a subscript using Perform Script On Server which navigated to the MainJobDetails layout, showed all records and then did a Refresh Window with Flush Cached External Data checked. This subscript then returned Get ( FoundCount ) as the Exit Script parameter. The value returned is the CORRECT value - it was 4 more than the FoundCount on the same layout when viewed on a client PC. The server is 'seeing' the new records!
So it seems that the clients aren't refreshing but the server is. What's odd though is that based on the 'Best Practices' document referred to in my original post it says that when using FM Server the ODBC connection should only be created on the server - the clients don't need local ODBC Data Source entries, so how are the clients supposed to update???
There must be something here that I'm not grasping but I just can't see what...
just to confirm, you should not be setting up ODBC on every client, this should be done on the Server only.
Could it be a time issue. Try to introduce a few seconds delay on the client after Refresh Screen.
I have not setup ODBC on every client - it has been done on the Server only.
The Refresh Window script step now works (I had an error in my script!), however it doesn't make sense that I would have to have a script that periodically loops through a list of layouts and refreshes them. This is going to look horrible for the user!!!
I've done some more tests on a different table (essentially a list of Key/Value pairs) and the same thing happens. If I add or delete records on the SQL system they don't reflect in the FileMaker client but they do on the server...
It makes sense as the server local client is headless and not persistent. Every time your request the data it will fetch a fresh set.
How critical for a user to see every single record real time? If it is critical, you can use an off-screen window to refresh your datasets to prevent unwanted layout animation.
Sure, that makes sense about the server.
It's pretty essential that the job data is up to date as my operators need to see what work they have to do. The nature of our business means that we can't afford to lose too much time so it would need to be refreshed at the very minimum every 5 minutes although near-instant updates would be ideal.
I'm not a big fan of off screen layouts but I can see that this might solve the problem so I'm going to try it. Thanks for the suggestion.
I like the sound of the JS idea. It's something I've wanted to look at and Matt Petrowsky did a webinar on using JS a few months ago but I didn't have a use case for it. This could be it...!
Just to close this one off - I discovered that at some point I'd change a relationship to a Cartesian rather than 'is not equal to'. Having fixed this my portal now refreshes immediately