Importing unique ID numbers

[JD2775]

Hi again,

I have an Excel file that I import data from into my DB. One of the fields, called "SIID" has been set up in the data-base as a unique value field, with a custom message appearing if a value is entered that is not unique. It works fine if the SIID is entered right into FMP, the warning pops up "You have already entered....".

An import from Excel however does not catch this. I was able to import the same SIID 3-4 times. Any way around this?

Thanks

[Matthew F]

You could try using the 'Update matching records in found set' and the 'Add remaining data as new records' options during import. I don't think that you'll get duplicate SIID's but you will over-write the current data for any matching records.

If that's not what you want, then I think you'll need to import it all and then do something else to find and the duplicates. This could be a Find using "!" in the SIID field, or using a self join relationship based on SIID.

[comment]

One of the fields, called "SIID" has been set up in the data-base as a unique value field

In the validation settings, select 'Always' instead of 'Only during data entry'.

[David Jondreau]

What do you want to happen when there are duplicates?

As comment notes, you can prevent the records from importing...but you won't know which ones failed. To do that you'd have to import them into a temporary table first to see which SIIDs exist.

[JD2775]

Thanks guys, that does help.