FM - Recurring Import

I have one table in which I do a weekly import of a tab delimited file.  I thought I could just check "Update existing records" because I didn't want to have to delete all records and reimport.  Updating does update and does add new records, the only problem is it will not delete records that do not match.  Specifically, this is an import of tenants and each month a couple of tenants leave.  

And I just had a thought - maybe I'm thinking about this all wrong.  Maybe the ex-tenants should not be deleted, but marked inactive or moved to a history table so we have a record of tenants.  Any thoughts would be appreciated.  I only have 14 months with FM.
LVL 2
rvfowler2Asked:
Who is Participating?
 
jvaldesConnect With a Mentor Commented:
@Willmcn: I provide the best advice I have with my experience since Filemaker 2.0. I would not depend on the import update routine unless you know a lot about the data you are importing. This approach also does not make the screen blink nor clear the records. I am glad you have never had the issue. God bless you.

@rvfolwler2 Updating matching records is supposed to update the contents of fields in the current dataset if there is a field in the imported dataset that has different field data for the same record. Unfortunately it doesn't create new records

Update existing records will create new records for new entries but will not reliably update field data in all updated fields.

These sections work differently when importing from Filemaker, which is what Updating matching records was designed for. To make cross computer updates. This is filemakers solution to MSSQL remote database update, which is much more robust.

At the risk of being overly redundant, I develop validated applications in the medical field and can assure you that the import and update functionality in filemaker is not reliable, and should not be used for business critical applications. If you are developing a personal database it should not be a big issue. I have expressed this issue to Filemaker over the years and they too claim it works, it doesn't.
0
 
Will LovingPresidentCommented:
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.
0
 
jvaldesCommented:
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.  
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
Will LovingPresidentCommented:
@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?
0
 
rvfowler2Author Commented:
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.
0
 
Will LovingPresidentCommented:
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.
0
 
jvaldesCommented:
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.
0
 
Will LovingPresidentCommented:
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.
0
 
rvfowler2Author Commented:
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?
0
 
rvfowler2Author Commented:
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.
0
 
Will LovingPresidentCommented:
@jvaldes - thanks for sharing your experience with this. Very interesting.
0
 
rvfowler2Author Commented:
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.
0
 
jvaldesCommented:
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
0
 
rvfowler2Author Commented:
Thanks.  Really clear.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.