Import only new records

[janet K]

I have a file that I download every morning, that is only "reset" or "cleaned out" every couple weeks. Which means every morning I get the new records in addition to what I downloaded have already downloaded yesterday.

Right now I open yesterdays file and find the last record that I imported yesterday. Then I open my new file and delete yesterdays records out of the file before bringing it into filemaker. Because after I import this data some of it changes I dont want to import and have it update the records putting the records back to what they were when they sent in the original request on the web.

My file contains a serial number I would like to have it match on the serial number and only import the new records, is there a plug in out there for this?

[Josh Ormond]

Look into doing the import via "Matching Records".

• Import Action = "Update matching records in found set
  
• check box "Add remaining data as new records"
  
• Select match field in the top section
  

Does that do what you want? The key is updating records in the found set. Play around with it and see if it works.

[Fenton]

You didn't say what kind of file it is that you are working with. When you say you "delete yesterdays records out of the file" it gives me the impression that it is either a FileMaker file, or a file which could be opened by FileMaker. If it is not a FileMaker then you should open/convert it to a FileMaker file. Then you can use relationships.

If you create a relationship on the serial number between the files you can identify the records in the new file which do not yet have a record in the main file. You can simply put the related serial number field of the main file on the layout of the file to import (non-enterable in Browse mode), and do a Find for * (anything).* Then Show Omitted Only. Those will be the new records. Or, once you Find the ones with records, delete them. One wonders why you only clean it out every couple weeks, rather than after every successful import?

[P.S. No, she can't use Update Matching, because she wants to keep any changes she's already made. There is, unfortunately, no option for "Match, but do not replace existing values (nor trigger modification on these)."]

*Or a "constant = 1" field, and enter a 1.

Edited June 23, 2009 by Guest

[comment]

My file contains a serial number I would like to have it match on the serial number and only import the new records

Validate the serial number field as 'Unique, Validate always'. Then you can import the file as usual (Add new records) and any matching records in the source file will be skipped.

[LaRetta]

There are added benefits to this technique (that Comment discovered) over using 'Update & Add To' when you want to only add new records:

1) Unlike Update & Add To, you don't need to show all records first (because it uses validation, it automatically checks against all records in the table anyway and only adds those that do not exist).

2) It does not change the modification timestamp for records just because they were involved in the import process' record set. This is because, although all records were involved in the comparison (whether in the record set or not), the existing target records were not touched by the import if validation fails.

This adds a dimension which we haven't had before ... a hybrid between adding ALL records (even if duplicates) or Updating the modification timestamps on those that don't need it just because the comparison needs to take place. It is a very useful technique.

[Josh Ormond]

It does not change the modification timestamp for records just because they were involved in the import process' record set. This is because, although all records were involved in the comparison (whether in the record set or not), the existing target records were not touched by the import if validation fails.

This adds a dimension which we haven't had before ... a hybrid between adding ALL records (even if duplicates) or Updating the modification timestamps on those that don't need it just because the comparison needs to take place. It is a very useful technique.

Very useful. I will have to store that in my memory banks.

[Emma in England]

More grateful thanks - I was browsing for the answer to something else, but this is a great tip. Cheers guys/gals!