Import only new records - unique values issue

[Joel P]

I have records that have until now been getting pushed from an MS Access databse into FMS databases. It uses the SequeLink client, which frankly has been a pain. What we would like to do is instead pull the information from a SQL Server database into FMS. This would use the ODBC drivers built into Windows, which we've had far less trouble with.

The problem is, while I would rather have all the data stored in the SQL Server tables (avoiding issues of synchronization and redundancy), the existing FMS tables (at least 16 of them) all have a plethora of calculated fields. Recreating those fields as shadow fields in the SQL Server tables and importing all of the FM records into new tables (a lot of them predate the MS Access solution) is not something I am comfortable doing with something I did not design.

My next idea was to simply import new records into the FMS tables using a script. However, if I use the matching fields criteria to avoid importing new records, it will overwrite any legitimate changes made on the FileMaker side of the data. I don't see how I can just use the import records option but require unique values for some fields, because in almost every case there are no entered fields that are unique. There is a calculated field that would be unique (a composite key), but I don't see that it's possible to require a calculated value to be unique.

Does anyone have another idea? I suppose implementing an autonumber in SQL Server and requiring it to be unique in FileMaker is a possible alternative.