avoiding importing duplicates?

[GlennC]

Is there a way to have FM import new records only if the record does not exist in the target file? (my source file is a .csv)

Platform: Windows XP

[spb]

You might try importing to an intermediary database, relate it to your main database, and set up a calc field checkbox that is a "one" if it finds a matching record across the relationship, and "zero" if not.

Run a find on this checkbox for a "one" and omit. Your found set is all records not duplicated in the main db. Go to main db & import.

This entire process, starting from the .csv file, could be scripted to a single button to click.

Steve Brown

[Leb i Sol]

2nd DB is not needed....on import through ODBC/DSN (setup to point to your CSV file) then filter that file by SQL:

SELECT DISTINCT (field1), field2,field3 WHERE field1="someting".....

depents on which field/column is the duplicate...they key is using DISTINCT statement.

http://www.w3schools.com/sql/sql_select.asp

All the best!

[GlennC]

Thanks spb. That works fine. I'm curious, how do you get all this to happen with a single click, when the script needs to access a different file?

Thanks Leb i Sol, all that is new to me and interesting - I'll do some learning.

[spb]

Set up a script in the intermediary database that will show all records, delete all records, then import the .csv. Include a Perform find script step on the checkbox as per discussion.

If set up correctly, this script ("ImportNew" will leave you with a found set in the intermediary that you want. Now set up a simple import script (one step) in your primary database that imports from the intermediary ("ImportIntermediary")

Now make a master script in the primary database that looks like:

Perform Script [sub-scripts, External: "Intermediary.fp5 (*)"]

Perform Script [sub-scripts, "ImportIntermediary"]

Add whatever bells & whistles you need, sorts, go to layout, etc. Attach this script to a button in your primary database, click it, and the effect will be that the primary is suddenly bigger by x number of new records. As long as your initial .csv file always has the same name and is always found in the same place, it should work painlessly.

Steve Brown

[GlennC]

Excellent. Perform external script - a fantastic discovery.

Thanks Steve!

[Leb i Sol]

Hi Glen!

Should you need some samples on ODBC/SQL approach let me know maybe I can make a little sample/tutorial......can't know too many ways to do the same thing Good luck!

[GlennC]

Thanks Leb - very kind. I'll muddle around and call for help if stuck.