Autonumber isn’t unique?

[Martin_W]

Hi,

I created a column of type Autonumber so that when new items are added to the table, it’d get a new, unique number allocated to it. It all seemed to be working fine but when a colleague added a record to the table a new record was created with a duplicated number? I was under the impression that Autonumber would create a new, unique number?

if Autonumber isn’t the answer, what is?

many thanks 

Martin

[rampprakash]

Hi @Martin_W 

Obviously, yes it's a Unique Number, that's out of the box behaviour.

Please mark as Answer if it is helpful and provide Kudos

Subscribe : https://www.youtube.com/channel/UCnGNN3hdlKBOr6PXotskNLA

Follow me on Twitter : @rampprakashd

Blog : https://microsoftcrmtechie.blogspot.com

[Martin_W]

Hi,

Unfortunately it’s not unique. I set a seed number of 141000 when I created the table. The first set of data I imported (via Excel) created a range of assets starting at the seed value, incrementing up.

my colleague created a new record a few days ago (well after I imported the data), and this created a duplicate 141001.

[Fubar]

Did they import it with the field already populated with a value in the import file?

Autonumber will only populate on create and where the field is empty.  Also, if a value is provided it will not overwrite the value that has been provided.  Similarly, if you re-seed the autonumber you can get duplicates if the new seed is less than the latest number generated.

Behind the scenes autonumber is based on the Database Sequence (transact sql), if setup and used (without re-seeding etc) then the next number produced should be 1 higher than the previous Sequence number (note: there is no uniqueness check at that DataVerse field level when the Sequence number is added to the field - if you want that you also need to implement alternate keys)

[AbdulWahab]

Hi Martin_W,

Read out the behaviour of auto numbers very carefully. If you are looking for a root cause then enable audit history and analyse the behaviour. It helps you a lot. More you can define a key for this auto number so that if somehow the number is going to duplicate, the system starts preventing duplication through the key.

Let me know if I can provide you with more details.

Thanks
Regards,

Abdul Wahab
Power Platform/Customer Engagement Developer/Lead/Solution Architecture/Project Manager
Direct/WhatsApp:+923323281237
E-mail: abdulwahabubit@outlook.com
Skype: abdul.wahabubit
Linkedin: https://www.linkedin.com/in/abdul-wahab-a5b8b011a/

[dpoggemann]

Hi @Martin_W ,

@Fubar is correct on the behavior of the AutoNumber, also you can manually overwrite the number and create a duplicate of one that already exists within the system, the AutoNumber does not guarantee it is an unique value in that column.  Defining an alternate key as that column will enforce uniqueness of the value as mentioned by @AbdulWahab.

[Martin_W]

Hi Fubar,

How would a re-seed happen? It sounds like this might have happened but I wasn't aware.

Forcing this column to become a key has failed, because there are now two duplicate values in there, and, because they are autonumbers they can't be edited.

thanks

Martin

[AbdulWahab]

Hi Martin_W,

If we answer your question then please mark it as verified.

Let me know if I can provide you with more details.

Thanks
Regards,

Abdul Wahab
Power Platform/Customer Engagement Developer/Lead/Solution Architecture/Project Manager
Direct/WhatsApp:+923323281237
E-mail: abdulwahabubit@outlook.com
Skype: abdul.wahabubit
Linkedin: https://www.linkedin.com/in/abdul-wahab-a5b8b011a/

[dpoggemann · Accepted solution]

HI @Martin_W ,

Couple things here...

Update Values

1. Autonumber field values can be changed manually on forms in model apps, in the standard display of the data in the Maker interface it is not editable but on forms it is. 
2. You can also export the data to Excel, update the column values and re-import.

Re-Seed

You can go into the definition of the column and update the seed value at any time.  This will cause the next value to be the new seed value.  Example below I changed the seed. from the initial 1000 to 0100 and the next row utilized this value.

new record generated with new seedreseed the value

[Martin_W]

Hi again,

I edited the duplicated values in Excel and published them back to the source (Dataverse) table.

I've also created a new Key using this option:

on the column that I need to be unique.

I hope now that the values will always be unique?

thanks,

Martin