Getting duplicate records when importing

[LDH]

I'm new at FileMaker...this will be elementary to most...

I made a flat file database with a runtime engine for a client to enter data at two sites. Now, I'm trying to write an import script so that a third party can keep a copy of the database with all the data from both sites. When I've tested the script, and I import the exported file, everything seems fine, but if I import the exact file again, it duplicates all of the records even though I've checked "update records" and "add new records" on the field mapping screen. My match field is an ID number that is a calculation field--concatenation of serial# plus left two letters of the location. I checked "don't store data" in the field definition so that this field will import.

I'm sure that I'm missing something really basic....Can anyone tell me what?

Thanks so much.

Libby

[Anatoli]

Are you using the same import?

What Import Action did you used the second time?

[LDH]

Yes, same import both times. A script:

Show all records

Set user abort(off)

Import Records (Restore)

If I keep importing the same file over and over again, my record count keeps going up by the number of records in the source file. And I checked to make sure I have a match field, but I have a feeling it has something to do with this field and its origin. The ID number is a calculation. I've seen others successfully use this ID as the match, but I wonder if they calculated it differently, or indexed or stored....?

Libby

[Fenton]

>My match field is an ID number that is a calculation field--concatenation of serial# plus left two letters of the location. I checked "don't store data" in the field definition so that this field will import.

This is probably the problem. It can't use the ID as a match, at least not on the imported file, if it is Unstored (not sure about the left side, good question). This would be like using an Unstored field as the target of a relationship. But I guess the Import dialog is not sophisticated enough to tell you that is an error.

In your situation, since the calculation comes directly from 2 other fields, it will calculate after the import. Make it stored. You can't import into a calculation field anyway.

You may want to check what happens if you have lookups based on that field. You may need to Relookup after the import. Or maybe only do that for the new records?

[LDH]

Thanks for your help. I actually checked, and the ID number was stored afterall. But now I may have solved the problem somehow and I have no idea why. Based on what I saw someone else do in a similar database, I changed my ID Number calculation from "left [location, 2] & serial" to "left [location, 2] & NumToText(serial)".

Now it's not duplicating on the import, but I have no idea why so that I can learn from this. Does anyone know why this would work?

Thanks both of you who answered my original post.

Libby

[Anatoli]

Why you are surprised, when for the same operation you have the same result?

You must choose something like Update records and not straight Import.