Updating Dataverse Id Column From Text to Autonumber Where Id Field Already Has Data?

[cmanning]

I have a Dataverse table where the Id column is a text datatype and I need to update it to an Autonumber datatype. 

Before I update that column to autonumber, I want to understand the ramifications.  As shown below, I already have over 2000 records that I migrated from a SharePoint source and in my Flow I copied the SP Id into the DV Id column. 

Here are the options and I just need an auto-incrementing Id that will increment even if when I use a Patch and don't provide a value for the Id column.

My Questions:
1. Which options should I select to ensure I have an auto-incrementing Id column?

2. Will the existing values in the Id column resulting from migrating SP data using a Flow, get overwritten and re-ordered per the creating date?
3. Are there any blind spots I have missed? 

[AhmedSalih · Accepted solution]

Hello, @cmanning, 

My Questions:
1. Which options should I select to ensure I have an auto-incrementing Id column?

Use the Data type Autonumber and the seed to be 2001 if you are sure that the data from SharePoint has only 2000.

I usually like to give some space so i would go with 2500 as my seed value for and 4 minimum digit. 

2. Will the existing values in the Id column resulting from migrating SP data using a Flow, get overwritten and re-ordered per the creating date?

The ID from SharePoint wont be effected and it will enter as a number.
3. Are there any blind spots I have missed?

No, and you also can use patch with the need to pass the ID to create new records.

Regards,
Ahmed
If my reply helped you, please give a 👍. And if it has solved your issue, please consider Accepting it as the Solution to help other members of the community find it more.

[cmanning]

@AhmedSalih - Thank you for the quick response!  This is a work-in-progress as I am converting a canvas app from using Sharepoint data sources to using DataVerse data sources. The sharepoint canvas app is live while the DataVerse canvas app is being developed as a separate app. As a result, the DataVerse canvas app is not quite ready to go live so there will be a final data migration from sharepoint to DataVerse using the flows I have already written. 

I have a few follow-up questions below. 
1. Since I want all the records to be renumbered, what if I set the seed value at 1 since retaining the sharepoint ID's is a non factor?
2. Once the ID column data type has been changed to autonumber, when I execute a patch to insert a new record into the table, do I have to explicitly list the ID column and if so what value do I give it since it is an auto-incrementing column? I'm thinking that I don't have to provide an ID column in my patch and that column will auto increment, but is that how it works?

[AhmedSalih]

@cmanning, to answer your questions:

I have a few follow-up questions below. 
1. Since I want all the records to be renumbered, what if I set the seed value at 1 since retaining the SharePoint ID's is a non factor?

Yes you definitely can do that and you don't need to even bring in your SharePoint ID column in the Dataflows. 

2. Once the ID column data type has been changed to Autonumber, when I execute a patch to insert a new record into the table, do I have to explicitly list the ID column and if so what value do I give it since it is an auto-incrementing column? I'm thinking that I don't have to provide an ID column in my patch and that column will auto increment, but is that how it works?

The Patch function when creating new records, you don't need to send the value of the ID. Any where you create a record in a Dataverse table, the Autonumber columns will generate automatically e.g. EditForms in Canvas Apps, or in the Model-Driven Apps forms, or Power Automate). Dataverse table Unique Identifier is GUID.

Regards,
Ahmed
If my reply helped you, please give a 👍. And if it has solved your issue, please consider Accepting it as the Solution to help other members of the community find it more.

[cmanning]

Thank you @AhmedSalih for the detailed solution and answering my additional questions!