FM - Recurring Import

Hey Randy - When you do you an update import, FM automatically creates a found set of all updated records. You could easily have your import script then do a "Show Omitted" and mark the remaining records as Inactive. From a business standpoint, it's probably best to retain your list of tenants for a time, especially if they want to use you for future referrals/references.

You forgot to mention one more thing, if there is a change in a record that does not make the index due to the size of the string, FM will not see the record difference on import.

The way I have solved this problem is to import into another table. Then use the lookup option to update the changes when related by a record identifier. Also at that time you can lookup a field to see if IsEmpty() gives you a true value . Telling you that record is missing in the other table. Use this approach to delete the records that didn't come in on the import. Use it in the other direction to identify new records that don't exist in the current table. This is fast and reliable, I use it on a number of autoupdate databases.  

@jvaldes - please explain what you mean by "if there is a change in a record that does not make the index due to the size of the string". Which string are you talking about, the record identifier?

Thanks.  Will, your solution may not work because often tenants have no changes to their records.  So, though they are not updated, I wouldn't want to mark them inactive.  Will try jvaldes though I've never done anything like this.  Thanks, I'll wait until the end of the day for any other suggestions.

I just ran a test and using the Update on Import function will cause ALL records that match to be placed in the found set regardless of whether they contain any new data.

Regarding jvaldes comment about indexing, record identifiers (auto-incremented serial numbers) should be short enough that there is no problem with indexing.

What I meant, is that if upon import you have a field that has an edit beyond the 24th character of a string the import facility will fail to see the change. The only way to get a bulletproof import on changing record contents and add new records and remove deleted records is the way I described it. Since I don't know which version of filemaker rvfowler is using I don't know what import looks like. This has been an area where filemaker has made many adjustments over the years. Import with record matching does not always work as advertised. That is why I have adopted the technique above for all imports. I have more than 60 tables being updated every 10 minutes, I don't want the data to disappear while other users are using the data and I want to catch all changes.

That said you can do this many ways and find the pitfalls and over time you will end up doing as I described, it's more work but it is bulletproof.

My point really was that in 22+ years of using FM, I've never found an instance that would require a matching field to exceed 24 characters, or even 10 for that matter, as in earlier versions of FM that only indexed that far.

Will, nice to see all records show up in the Found Set; I was about to check that myself.  I use FM11 if that helps.  jvaldes, I actually don't use update matching records, but I checked "update existing records in found set."  Is that wrong.  What is the difference between these?

Thanks for the warning.  Based on my past experience with MS, etc., I'm inclined to believe you rather than the creator's.  Two final questions before awarding points:
1. When identifying new records in the other db, a) do you do this by doing a lookup from the secondary table back to the main table? and b) how do you import only those records over to your main table?

2. What problems has an update import caused?  Could I do an update import, omit the found set, mark all recs left as inactive and export them to a history table, then do a delete all records and import all?  Or would your secondary table way be better.

@jvaldes - thanks for sharing your experience with this. Very interesting.

I think my last question was beyond the scope of the original question, so I'll just leave it as voluntary.  Rarely do this, but giving all points to jvadez as the other option has an inherent risk to it.  Thanks to all.

Remember these tables will be related by a record identifier. Use the secondary table to hold the new data. Go to the first table and for every record in the first table check which related record identifier field is empty. That would be a sign that no record exists in the second  table. That would be an indicator that the record does not exist in the second table. If you want to delete those records then do that.

The opposite view , in other words looking from the second table will tell you which are the new records in the second table.

Then running a lookup from the second table to the first for every field will update the field contents to the new data.

and you are done

Thanks.  Really clear.