FileMaker supports a choice in the Import options to "Update matching records in found set" and another option that can be checked to "Add remaining data as new records."
Using those two together can allow you to import from a data set, matching on the membership number to update existing records, while adding new records for the new membership numbers to the database.
You just want to be sure you have done a "Show All records" before starting the import, so that all exsiting records are part of the found set for updating.
Yes you can script this.
Your fundamental approach is sound:
- import the new data into a "temp" table
- then go through that data evaulate whether it is: new, removed, modified or unchanged
These last 4 decisions are not generic, they depend on your data and business logic. Part of that decision is: is the new data sent down in the master list is truly the master and can always overwrite any local data changes you have made?
I think it's that last decision making process that has me stymied. Sometimes our contact data for a member at the regional level is more current than the parent organization's data that they send out in the monthly update files. I don't want to lose the most current data just because there's a delay between when we send the update to the parent organization and when it gets processed and sent back down the chain.
I was thinking of using a auto calculated modified date field that I can cue off of in order to determine which records should be overwritten and which should not. Would that approach work?