Error migrating data with lookup fields between environments using a Dataflow

[Anonymous]

I'm trying to move data between Power Platform environments using a dataflow but cannot get it to work with lookup fields.

For example I have 2 Dataverse tables: -

Table name: GroupMembers	Table name: Group
GM_ID (Primary Name Column)	group_ID (also setup as a key)
group_ID (Lookup to Group)	Description
person_ID (Lookup to People)

The Groups data transfers without a problem but the GroupMembers fails with: -

{"error":{"code":"0x80060891","message":"A record with the specified key values does not exist in hdc_group entity"}}

I've followed the advice below to migrate data between the environments and this works: -

Migrate data between Microsoft Dataverse environments using the dataflows OData connector - Power Ap...

I'm also following the advice below but when data includes a lookup field it fails with the 0x80060891 error above: -

How to map a Lookup field in a Power Platform Dataflow (dynamicscitizendeveloper.com)

I've stripped this down to the bare essentials and tried just about everything but with no success.

Any suggestions massively appreciated at this point 🙂

[timl]

Hi @Anonymous 

Thanks for tagging me in the other post on this topic.

Unfortunately, I'm not sure what the cause of this problem is.

The important thing to do here is configure group_ID in your group table as a key, which I see that you've done.

I'm just tagging some of the other experts here who might be able to comment or to offer some advice on this issue.

@EricRegnier @jlindstrom @HSheild @ChrisPiasecki 

[Anonymous]

Thanks, it really has me stumped.

[Anonymous]

Might it be possible to move data between environments and retain relationships between entities using the lookup capabilities in Power Query through data flow? @EricRegnier @timl @jlindstrom @HSheild @ChrisPiasecki

Meanwhile a limited workaround I thought I'd mention in case it's relevant to others that enables you to move data with lookups between environments is to use the Copy function from within the Environment Admin Portal. Although this provides a way to copy all data from a source to a target environment it erases the target environment prior to copy so not a solution for me.

[EricRegnier]

Hi @Anonymous, were you able to solve this? In short, yes it possible to migrate/import data from 2 Dataverse environments. For lookups as the article in your original post suggest is to create alt keys in both the source table(s) and destination table(s). Once the alt keys are active , create a blank Dataflow with oData asthe data source. In the mapping step, ensure you enable the "Delete rows that no longer exists" option and map to your existing table... Hope this helps!

[Anonymous]

Hi @EricRegnier  , thank you so much for the response but despite best efforts I just can't get this to work. It fails every time with: {"error":{"code":"0x80060891","message":"A record with the specified key values does not exist in crbc9_grouptype entity"}}

In case I'm misinterpreting something the steps I've taken that result in the error are: -

1. I created a Solution in the Development environment (source) and created and added the groups and groupTypes tables to the solution.

2. I created Alternate keys for the groups and groupTypes table's Name columns.

3. I checked the System Jobs and confirmed the keys had been created successfully.

4. I added some data to the groupType and groups entities.

5. I exported and imported the solution (unmanaged) from the Development (source) to the Trial (target) environment and confirmed the both entities and their keys had been duplicated successfully.

6. I created a Dataflow in the Trial environment using the oData connector to migrate the groupTypes data and groups data from the Development environment.

         I loaded the source entities to the existing target entities.

         I enabled the "Delete rows that no longer exist" option.

         For the groupTypes entity I mapped _crbc9_grouptype_value to crbc9_groupType.crbc9_Name.

        Otherwise I used Auto map (which mapped crbc9_name to crbc9_Name on both entities).

7. I ran the DataFlow.

I received the error above.

[EricRegnier · Accepted solution]

Hi @Anonymous,

I was very curious so I tried it from my side and it worked. In my case I have 2 tables, Car and Make. Cars has a lookup to Make and Make has an alt key defined on the Name text column. I imported those entities to my destination Dataverse environment.
Here are some screenshots of my Dataflow steps:

1. After connecting to the Dataverse environment and selecting the table, I expanded the lookup column (click on the little double arrow icon in the header) to select the alt key value for the mapping later:
   
2. In the mapping step, map the expanded alt key column from previous step and map it to the detected destination alt key:
   
3. Save and run and it should import successfully!

Hope this helps!

[Anonymous]

@EricRegnier legend! thank you so much.

For anyone following in these footsteps the key element I was missing was Eric's step 1 above: modify the transform to include the lookup keys.

For clarity, the process below finally worked for me: -

1. Create a Solution in the source environment (Development in my case) and create or add the Tables containing the data you wish to migrate (groups and groupTypes) to the solution.

2. Create an alternate key for the Primary Name Column field (Name) on the lookup table (groupTypes. It was not necessary to add any other keys on any other tables).

3. Export and Import the Solution (managed or unmanaged as required) from the Source (Development) to the Target (Trial) environment. This will transfer the Tables and keys metadata.

4. Create a Dataflow in the Target (Trial) environment using the oData connector to migrate the Source lookup table (groupTypes) from the Source (Development) environment.

    a. Under Transform, expand the Lookup (groupType) column (syntax is '[prefix_table]_PrincipalObjectAttributeAccesses') and select the Primary Name Column field (Name) from the lookup table (groupTypes).

    b. Under mapping, select the existing target Table, no need to enable "Delete rows that no longer...". Select 'Auto map' (for the groups table this mapped 'crbc9_grouptype.crbc9_name' to 'crbc9_groupType.crbc9_Name' and 'crbc9_name to crbc9_Name').

5. Run the DataFlow.

6. Now create a DataFlow for the other Tables (groups) and run them.

Re steps 4 to 6 you may choose to include all tables in the one dataflow but this will generate the "A record with the specified key values does not exist..." error on the initial pass(es) until such time as the Tables with the lookup data have been populated.

[Scott13]

Thank you for the follow up message @Anonymous this helped a lot. Really appreciate you closing out the thread.