Solved

FM - Recurring Import

Posted on 2011-02-23
14
648 Views
Last Modified: 2012-05-11
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.
0
Comment
Question by:rvfowler2
  • 5
  • 5
  • 4
14 Comments
 
LVL 24

Expert Comment

by:Will Loving
ID: 34960769
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
 
LVL 9

Expert Comment

by:jvaldes
ID: 34961998
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
 
LVL 24

Expert Comment

by:Will Loving
ID: 34962095
@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
 
LVL 2

Author Comment

by:rvfowler2
ID: 34962100
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
 
LVL 24

Expert Comment

by:Will Loving
ID: 34962270
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
 
LVL 9

Expert Comment

by:jvaldes
ID: 34962592
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
 
LVL 24

Expert Comment

by:Will Loving
ID: 34962798
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 2

Author Comment

by:rvfowler2
ID: 34963086
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
 
LVL 9

Accepted Solution

by:
jvaldes earned 500 total points
ID: 34963450
@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
 
LVL 2

Author Comment

by:rvfowler2
ID: 34964070
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
 
LVL 24

Expert Comment

by:Will Loving
ID: 34964444
@jvaldes - thanks for sharing your experience with this. Very interesting.
0
 
LVL 2

Author Closing Comment

by:rvfowler2
ID: 34964554
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
 
LVL 9

Expert Comment

by:jvaldes
ID: 34970605
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
 
LVL 2

Author Comment

by:rvfowler2
ID: 34970900
Thanks.  Really clear.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Problem: You have a hosted FileMaker database and users are tired of having to use Open Remote or Open Recent to access the database. They say, "can't you just give us something to double-click on rather than have to go through those dialogs?" An…
Having just upgraded from Filemaker 11 to Filemaker 12 over the weekend, we thought we would add some tips for others making the same move.  In general, our installation went without incident. Please note that this is not a replacement for Chapter 5…
This video discusses moving either the default database or any database to a new volume.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now