You can write a script for this and there are ways to use a validation rule to keep new duplicate data from overwriting the old.
Once your script is written, you can just run the script each time. There are details that can be handled if you need to such as ways that an Open file dialog can pop up at the right time if the actual file names aren't the same and aren't predictable.
The script will preserve your field mapping, but you will need to be careful to make sure the format of the excel file does not change. You may want to make a practice of always saving a back up copy of your current file just before performing the import script. (Or the import script can, in some cases, save the backup copy for you just before importing.)
Do you know how to create a script?
What data in the xls file makes it a "duplicate" of an existing record?
Scripts are an area of interest that I know nothing (almost nothing) about. I'll dig into them today and see what I can figure out.
Once a file is in my database the records in it are subject to significant editing and changes. When duplicate records, prior to editing, are presented again for upload they are not wanted and need to be eliminated. There is a unique number in an ID field associated with each record. My intention is to use match-ups of this unique ID to reject duplicate additions to the database. If I can do this in one step at the time of addition to the database it will be extremely efficient and helpful.
For sorting purposes, two fields that do not exist in the upload file need to be added to each new record as it is entered into the database, a "source field" and a "date field". Every week, File A, File B, and File C will be added to the database. They are named by this protocol "File A 2011 04 16". The following week the same file - now named "File A 2011 04 23" and containing new and duplicate information will be added to the database. Idealy, when the file is uploaded to the File Maker database, it will automatically add information to source and date fields for each record, i.e. Source Field = File A, Date Field = 4/23/2011.
After this the plan is to modify the Contact Management Starter Solution to opperate the database. I hope once I figure out scripts I'll be close to getting here.
All input and suggestions very much appreciated.
There is a unique number in an ID field associated with each record
And does this same number exist in a column of the spread sheet file? If so, this part is easy.
- Open up Manage | Database | Fields.
- Find the field definition for your ID field and double click it.
- On the validation tab, specify Unique Values and validate always.
- Now, use Import Records | File, and when you click the Import button after matching fields, one last dialog pops up asking if you want to enable auto-enter options. Click this check box and the duplicate records will not be imported, only the new records.
- Immediately after import, your imported records will form the found set. You can then click into each of your two empty fields and use Replace Field Contents to assign the same value to all your records.
- Once you can do this by hand, you can try doing it with a script.
Both Import Recors and Replace Field Contents have the potential to add and/or modify very large numbers of records. You cannot use Undo to reverse the changes. They also have a number of different options to choose from. Thus, you should read up on both these tools in FileMaker help and make back up copies of your file before trying them out.