In a very high-activity system that syncs with a SQL database to drive a website, I'm trying to build a bullet-proof, non-UI-based way to flag only the records that have been updated in FileMaker and need to be updated in SQL. Here are things I've ruled out:
- Typical audit-log methods would require someone to remember to add any new fields to the log scope.
- Script triggers and explicit script steps aren't practical because there are too many paths to modifying the records in question. (And see #1 about devs remembering to include this in new work.)
- Comparing timestamps doesn't have fine-enough resolution. (If Get ( CurrentTimeUTCMilliseconds ) was host-based, that would work, but it isn't.)
I'm imagining that the script which does the push to SQL sets a flag saying the record has been updated (or clears a flag that says the record needs an update), and then modifying any other field in the record resets that flag to the "needs an update" state.
Unfortunately, any auto-enter calc on the flag field that's based on modification of the record
I.e. Let ( ~update = zModStamp ; ... )
will always set the flag to the same value, including overriding any setting/clearing of the flag field during the push script.
The best solution I have right now is to use #3, but include the stamped second in the search instead of looking only for greater than last sync time. This would sweep up some extra records that have already been pushed and haven't been modified since the push, which is better than leaving them out, but still ends up syncing records that don't need it.
Thanks in advance for any ideas!