Merged Import Modification Tracking
When developing within FileMaker, there are often times when I'm surprised at how much "figuring out" is required to solve a problem. In this video article I provide a great deal of information about a problem I was having with managing a recurring import of some inventory data.
The data being imported was constantly changing and came from an external source where only a few fields are controlled by the user. With the original FileMaker system, it was structured in such a way where the "clean slate" approach was taken.
The "clean slate" approach, which you can likely guess, is one in which you simply wipe all previous data and simply import a whole new set. While this works well with small record sets, the more you import, and the larger the data set, the more of an impact, in terms of waiting time, you'll feel. You also lose the benefits of being able to assign your own internal key values and maintaining those within the schema.
So, what's the answer to the question of a recurring import where a significant percentage of data may not change? It's record modification tracking. You need to track which records were modified in order to know which should be post processed or updated by the logic of your solution.
This video and the sample file demonstrates exactly how I approached the problem and includes valuable information which I am sure will make you a better FileMaker developer. If you've never understood why you might want to use a "hash" for comparing data, or if you've thought you should really look into performing scripts on the server side then, by all means, jump into this video. It has a ton of great stuff to learn from!
Comments
Deleted Records from the External Datasource
From the current import setup in this solution, what happens to records that have been deleted in the external data source, that already exists with a matching ID in the Filemaker database?
The external data source can... 1) update existing records, 2) add new records, but it doesn't enable deleting 3) current records correct?
What would this current technique need if it were to 3) delete no longer existing records?
Mentioned in the video...
I mentioned this situation in the video, however, I realize it's not super obvious. The trick with dealing with removals is to work on a known field which ALWAYS contains inbound data.
It's a bit of a hack, but, prior to import you simply run a field replace across this known field which upon merge import WILL contain data. The replace you run can be clearing out the field or using a known value which DOES NOT exist within the inbound data.
You can then search for "empties" or the known value you used for the replace and then take action, such as archiving or deleting the records as desired.
If your inbound data set does not have a field which ALWAYS has data within it, then you'll have to use an auto-enter field like a modification date.
However, one oddity (I would call it a bug) with regards to imports, is that FileMaker will always, regardless if there is a matching record or not, update the modification date - even if the import didn't modify the record. So figuring out how to do this might require additional thinking. For my situation, I had a number of fields which always had data within them and could not be empty, which allowed me to use this trick. ;)
One thing I didn't try, which I may need to look into is an auto-enter Get ( RecordModificationCount ).
-- Matt Petrowsky - ISO FileMaker Magazine Editor
Ah ok...
Got it. Thanks Matt!
regarding perform script on server
Hi, you stress the importance of the perform script on server feature of FM13.
I was very excited over this when it came out -- but reality turned out to be very sad.
I think you should have mentioned in this context, that this feature is seriously broken with the current version of FM server.
It only works with one-file solutions.
Perform script on server currently doesn't work over multiple databases (e.g. when using database separation) at all, because they broke authentication mechanisms with it. It's a know bug and it pretty much makes perform script on server unusable for any more serious use as nice as it would be to have it in a working fashion.
Anyway, one should be aware of the broken implementation because otherwise one could waste some time on it trying to figure out why the hell it's not doing what it's supposed to do.
Here's a temporary fix
Yes, I did neglect to mention the bug, which I was aware of.
Temporarily, if you're running a local file with your UI on a mobile device, can't you just call a script from the local file into the data file which simply shows a dialog prompt for running the Perform Script on Server from the context of the data file?
The bug is about the local file passing authentication on to the data file when the Perform Script on Server is supposed to run within the data file. If you simply initiate from the context of the data file then you should be good to go.
Yeah, it's a bit of a hassle currently (and will likely be fixed), but it's not a problem which should prevent you from taking advantage of the feature currently. Just work around it right?
-- Matt Petrowsky - ISO FileMaker Magazine Editor