Data Import with Auto-number Field as Alternate Key

(0) Share

Report

Posted on by Alan Roberts

Hi,

I have a table with an auto-number column defined as an alternate key to guarantee uniqueness. So far so good...

The problem I am hitting is when trying to import data. The data I am importing from a CSV doesn't have a field representing the auto-number column. I wouldn't want this to every be included in an import as it needs to be generated by the system.

If I disable duplicate detection when an import runs then everything woks as expected, the records are imported and the auto-number field gets populated.

For a production system though we need to be trapping duplicates during the import so turning off duplicate detection isn't possible. When I turn it on though the imports fail, complaining about the auto-number not being unique...

A record that has the attribute values "<auto-number field name>" already exists. The entity key "<auto-number filed key>" requires that this set of attributes contains unique values. Select unique values and try again.

Is there a way around this issue?

Thanks

Alan

I have the same question (0)

All responses (5)

Answers (1)

Alan Roberts on at

Like (0)

Report

RE: Data Import with Auto-number Field as Alternate Key

OK so a possible workaround I have found is to add a second custom key, this one a composite key containing the selection of columns that should define uniqueness in terms of an import. With this in place, the import will fail if the values for fields making up the composite key aren't unique. This will fail even when duplicate detection is turned off for the import itself.

Is there a better solution than this?

Thanks

Alan

Was this reply helpful? Yes No
Verified answer

Community Member on at

Like (0)

Report

RE: Data Import with Auto-number Field as Alternate Key

Hi Alan,

Your workaround seems to be available.

Based on your description, the auto number field that was auto-generated by system had been occupied by dirty data already，which is the reason for this error.

Maybe you can change the Seed Value and start with other number for the auto number field.

Was this reply helpful? Yes No
Alan Roberts on at

Like (0)

Report

RE: Data Import with Auto-number Field as Alternate Key

Thanks Leah.

The data import was for new data with no auto-number field value specified. The records already in the table all had unique numbers and I weren't in conflict with the next auto-number in the sequence (I could manually create records and have the auto-number assigned without issue). As far as I could tell the de-dup checking was happening before the record was saved so there was no auto-number at that stage. The dedup check was then complaining because as far as it was concerned all of the new records had the same null value for the auto-number field which seemed odd.

Cheers

Alan

Was this reply helpful? Yes No
Alan Roberts on at

Like (0)

Report

RE: Data Import with Auto-number Field as Alternate Key

Based on your comments it seemed like dup detection should have worked so I went back can cleared out the existing records and deleted/recreated the dedup rule. Thinks seem to work as expected now without the need for an alternate key.

I have no idea what was causing the original issue I was seeing but thanks for the assistance

Alan

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: Data Import with Auto-number Field as Alternate Key

Hi,

If my assistance is useful, could you click 'yes' under title to close the case?

Thanks in advance!:)

Was this reply helpful? Yes No