Auto-generate ID when import the excel/Sharepoint list to dataverse

(0) Share

Report

Posted on by mmchx

150

Hi All,

I'm working on the Dataverse for MS teams, and want to migrate the data from the Excel / SharePoint list to Dataverse.

I have an issue that when I imported data, it doesn't generate the ID like the SharePoint list. So, I tried creating the column with auto number type before importing the data. The result is it doesn't generate the auto number for me, it will generate it when I create the new row only.

How I can achieve that to import data with auto number generating?

The purpose of auto number column is to create the unique id of each item like in the SharePoint list.

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (10)

Answers (2)

AhmedSalih

6,678 Moderator on at

Like (0)

Report

Hello, @mmchx

Create an ID Column in the Excel file and then map that column to your Dataverse auto-number column. Make sure that ID column has the sequence of numbers from 1-whatever the last record number is. For the Auto-number column in Dataverse, make sure that the seed value is one number after your Excel last records ID value. So when you create new record after the import, it will start after the last ID.

If my reply helped you, please give a 👍 , & if it solved your issue, please 👍 & Accept it as the Solution to help other community members find it more.

I am primarily available on weekdays from 6-10 PM CT and 5-10 PM CT on weekends.

Visit my Blog: www.powerplatformplace.com

Was this reply helpful? Yes No

mmchx 150 on at

Like (0)

Report

Could you please show me some examples?
As I tried, Created 30 records on the dataverse first and my auto-number was HC-0001 to HC-0030 then I imported 2 records with ID HC-0031 & HC-0032 (created on the excel file).
It mapped to the auto-number column correctly, but when I created 1 record directly on the dataverse. It generated the auto-number as HC-0031.

do I do something wrong or do I skip some steps?

Was this reply helpful? Yes No
AhmedSalih 6,678 Moderator on at

Like (0)

Report

@mmchx, You Load the Excel data first, then your seed start one number after the last record in Excel. Excel should be HC-0001 to HC-0030 and then create those in Dataverse. Is this a migration process or will be ongoing integration?

Was this reply helpful? Yes No
mmchx 150 on at

Like (0)

Report

Actually, both of them.
First scenario
1. doing the migration process first which needs dataverse auto-generate the number when migrating the data.
2. User who can add/edit data to dataverse, can add record and dataverse needs to auto-generate the number after the last record from the migration process. but the issue occurs when a user added the record after the migration process. It doesn't auto-generate numbers correctly as I mentioned.
For example
I migrated data(Excel file) and created column "ID" in the excel file (HC-0001 to HC-0030), after the user added the new record directly to dataverse. the expected result is dataverse auto-generate number to HC-0031 but the dataverse generated HC-0001 instead.
Second scenario
1. I manually created 30 records directly to dataverse (HC-0001 to HC-0030)via Power app Excel add-in. (Dataverse auto-generated number correctly).
2. User would like to import the data to the dataverse via data flow by using the Excel file. which the user has already created the ID column and created the seed number in the excel file. (This one is in case the user has multiple records and they don't like to copy-paste.)
For example
I manually created on the dataverse -> the auto-number are HC-0001 to HC-0030, then the user imported 2 record from the Excel file to dataverse and already defined the seed number which is HC-0031 & HC-0032. Once imported completely, Ok it shows HC-0031 & HC-0032. But if the user created the record (directly on dataverse) after importing it auto-generated HC-0031 & HC-0032 so, this will be 2 records that have ID: HC-0031 and 2 records that have HC-0032.

Was this reply helpful? Yes No

Verified answer

AhmedSalih

6,678 Moderator on at

Like (1)

Report

@mmchx, I just ran a test. Here is what I did:

I created this Excel File:

Then I created a DataFlow to load the Excel data into an Existing Dataverse Table.

I created the Dataverse table with only one custom column (Auto-Number)

The seed was Test as Prefix and 1000.

Ran the DataFlow:

The result:

I created the CC record in Dataverse. Records A to E were created via the Dataflow and the flow generated the ID value (of the AutoNumber Column) automatically.

If my reply helped you, please give a 👍 , & if it solved your issue, please 👍 & Accept it as the Solution to help other community members find it more.

I am primarily available on weekdays from 6-10 PM CT and 5-10 PM CT on weekends.

Visit my Blog: www.powerplatformplace.com

Was this reply helpful? Yes No

mmchx 150 on at

Like (0)

Report

appreciated it. seems like I need to create the new ID column(auto-number) on the dataverse, instead of using the Primary column as ID column (auto-number)

Was this reply helpful? Yes No
Verified answer

AhmedSalih 6,678 Moderator on at

Like (0)

Report

@mmchx , That is correct!

Was this reply helpful? Yes No
Az8PnG 85 on at

Like (0)

Report

Hi @AhmedSalih May I know why did u import your excel using dataflow instead of manually clicking on "Import from Excel" button?

Was this reply helpful? Yes No
AhmedSalih 6,678 Moderator on at

Like (0)

Report

Hello, @Az8PnG, the import errors using the import method are not clear sometimes. Also, in the Dataflows, you can do full ETL which I do recommend preparing your data before the load.

Was this reply helpful? Yes No
Az8PnG 85 on at

Like (0)

Report

I see, understood! I already try this formula but it is not working on me.

I am having difficulties in creating an auto increment which starts with prefix.
In the picture below, I wanted to make if the user choose the radio button such as Electronic, the part number will increase 1, which is it will be "E-000005".

I want to make it increment 1 based on the latest part number according to their Stock Type in database (as picture)

This is my code for:
1. OnChange radio button:
UpdateContext({varSelectedStockType: Radio2.Selected.Value});
UpdateContext({varPartNumber: LookUp(Sort(Filter(Samples,'Stock Type'= varSelectedStockType),'Part Number',SortOrder.Descending),true,'Part Number')});

2. Default text input:
Concatenate(Left(varSelectedStockType,1),"-",Text(Value(Right(varPartNumber,Len(varPartNumber)-2))+varCounter,"000000"))

3. On Select Save button:
SubmitForm(Form3);ResetForm(Form3);
Set(varCounter, varCounter + 1)

May I know is there any different?

Was this reply helpful? Yes No