cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Medoomi
Responsive Resident
Responsive Resident

CDS - Duplicate autonumbers - How to fix? bug?

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?

6 REPLIES 6
Drrickryp
Super User
Super User

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
Responsive Resident
Responsive Resident

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?

@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
Responsive Resident
Responsive Resident

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?

@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
New Member

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. 

Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Difinity Conference 2022

Difinity Conference 2022

Register today for two amazing days of learning, featuring intensive learning sessions across multiple tracks, led by engaging and dynamic experts.

European SharePoint Conference

European SharePoint Conference

The European SharePoint Conference returns live and in-person November 28-December 1 with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Power Apps Ideas

Changes to Ideas Coming

We are excited to announce a new way to share your ideas for Power Apps!

Top Solution Authors
Top Kudoed Authors
Users online (3,642)