Unanswered

Bulk Update Dataverse Table with new record

(0) Share

Report

Posted on by FelipePanza

I have created a Table in Dataverse with columns; name of employee and employee number. The data is imported from an Excel files. The primary key is not set to employee number because this is not an option when creating Table, I only got text field. Dataverse for Teams do not have a option to create alternate key. I was hoping I could do this in Teams, but I can use regular Dataverse if needed.

I will get a new Excel file next month that have to be checked against Dataverse Table.

How do I do this

1. Use the import function= will it find new record in the new excel file and not import duplicates?

2. Do I have to make a flow?

I am not an administrator and cannot set duplicate rules as I read in Microsoft documents.

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (16)

Answers (1)

AhmedSalih

6,678 Moderator on at

Like (0)

Report

@FelipePanza, Answering one point you mentioned "he primary key is not set to employee number because this is not an option when creating Table, I only got text field. Dataverse for Teams do not have a option to create alternate key. I was hoping I could do this in Teams, but I can use regular Dataverse if needed."

To create a Primary Key as your Employee ID, create an Alternate Key and assignee it to the Employee ID column.

This way you can create a DataFlow and run as needed or setup an schedule daily/weekly run. The DataFlow will only update the existing records if other columns values changed (Not the Employee ID), or when new records added.

Check my Tutorial here for some insight about the Alternate Key and Dataflows.

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

Drew Poggemann 9,287 Most Valuable Professional on at

Like (1)

Report

Hi @AhmedSalih ,

I think the challenge here is that Dataverse for Teams does not support alternate key creation (https://powerusers.microsoft.com/t5/Power-Apps-Ideas/Dataverse-for-teams-alternate-keys-support/idi-p/881347).

@FelipePanza, if this is the case you could leverage Power Automate Flow to update your records in Dataverse for Teams. https://powerautomate.microsoft.com/en-us/blog/automation-made-easy-with-dataverse-for-microsoft-teams-and-microsoft-power-automate-2/ . You should be able to create your Excel file and store in OneDrive or something like that and then kickoff the flow, have it loop through each record in the Excel file, Search for the record with the same employee number and if it finds it, do an update, otherwise add the employee.

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

Like (1)

Report

@dpoggemann, good point, I missed that it was a Dataverse for Teams. Thanks

Was this reply helpful? Yes No
FelipePanza 84 on at

Like (0)

Report

Hi and thanks. I will not use Dataverse for teams. But my issues now is how to make a relational between two tables.
I tried the documentation and I keep getting empty values.
Tried this simple case:
Table1:
Primary Column: Employee ID = Text field = 1
Second Column: Name of employee

Table 2:
Primary Column: Employee ID = Text field = 1
Second Column: Favorite Coloe = Blue
Lookup column related to Table 1

View in Table 1 do not show Table 2 as related Table.

View in Table 2 do show Table 1 as related Table but with no value in the related columns

Was this reply helpful? Yes No

AhmedSalih

6,678 Moderator on at

Like (1)

Report

@FelipePanza, if you are not using Dataverse for Teams, then follow those instructions:

Table1:
Primary Column: Name of employee (Primary Column doesn't mean it is the Column you should use for your Primary Key)
Second Column: Employee ID > Whole Number
Make sure that your Second Column (Employee ID) is set as an Alternate Key.

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

AhmedSalih

6,678 Moderator on at

Like (0)

Report

@FelipePanza, Now for the Relationship, I would highly recommend that you follow my steps in the Tutorial I shared in my previous reply. Create the Dataverse Tables, the Alternate Keys, and the relationship before you load the data from Excel.

For tutorial >> https://powerplatformplace.com/f/excel-to-dataverse-migration---part-1

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

FelipePanza 84 on at

Like (0)

Report

Where should I have the lookup column?

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

Like (0)

Report

@FelipePanza, In the Table two. Make sure it is Whole number as well.

Was this reply helpful? Yes No
FelipePanza 84 on at

Like (0)

Report

Do the tables have to in a solution?

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

Like (0)

Report

@FelipePanza , as best practice yes, but it is not required for your use case to work.

Was this reply helpful? Yes No