CDS - Duplicate autonumbers - How to fix? bug?

[Medoomi]

I have a table in Common Data Services (a non-default entity/table) where the primary name column is an autonumber. Recently whenever a new record is entered, it has started repeating numbers already present in the column.

Obviously this is causing problems for the database.

Should an autonumber column be able to repeat numbers already in that column?

What can I do either to troubleshoot, or to fix this?

[Drrickryp]

Hi @Medoomi 

First, don't use the Primary Name field as an autonumber.  Add a separate ID field and change the data type to Autonumber, and uncheck Required option.  You can use a string prefix number and set the number of digits and the seed value as shown here https://d365demystified.com/2020/03/23/autonumber-field-in-cds-powerapps/  Then, when you use Patch() and Defaults() functions to create a new record, the field would generate a unique autonumber.  

From a troubleshooting perspective, the reason you may have autonumbers is that when you created the autonumber field, you didn't uncheck required, if so, you would have to provide a number and  it may be that you have manually put in duplicate numbers 

[Medoomi]

Thanks @Drrickryp ,

I see the advantage of not using the Primary Name field as an autonumber... However we're in production & can't back-track without a major halt.

My main concern was that the autonumbers being handed out have begun to repeat. Seed value was set to 1000, but after reaching 1068, it has begun handing out 1021,1022,1023 etc.

The environment was migrated, so I can understand if the count became confused...

Would setting the seed value to 1069 fix the problem?

[Drrickryp]

@Medoomi 

That seems like a temporary fix since if it happened once, it will likely happen again.  I think you will need to find a permanent fix for the problem.  

[Medoomi]

Thanks again @Drrickryp 

Do you mind at all explaining: You mentioned using a string prefix number. I can understand this keeping numbers unique between tables, but is this necessary to keep autonumbers unique within a single table? My problem was autonumber handing out the same number within the *same* table. Is not this a CDS glitch?

[Drrickryp]

@Medoomi 

I suppose that depends on what you are using the autonumbers for.  If you are relying on it being the Primary Key for a table, then a primary key should be unique and not able to be changed or reused if the record is deleted.  There is no need for it to have meaning inside the app since there should be no need for a user to ever see it.

Sometimes, I like to create my own ID column for a table and use it as the primary key. I make it a whole number type and whenever I  add a new record, I use the formula First(Sort(table, ID, Descending)).ID+1 as the value I assign to a Default property of a textinput box that I hide from the user. 

[xion]

We are having the same problem. Our DV tables our  maintained both by power apps for manual updates as by dataflow for webscraping data. Within our power app we use rule based logic using this primary key column ('cause why call it that if it is not used for exactly this purpose). However every time the dataflow runs to fetch the webscrapped data it will start counting at 1000.