Dataflow - Destination column not present for LookUp Field created from a Relationship

[BenGin]

I need some help with populating Dataverse Tables using Dataflow.

I have a main Dataverse Table (Problem Solve Problems) that has One-To-Many Relationships with 3 other tables (Notes, Personnel, Whys). These relationships were created at the main table "Problems" using the "Add relationship" button.
I have a Power App where users will create a new Problem into the main Problem Solve Problems table.
Each of these Problems can then have multiple Notes, Personnel, and Whys.

I want to be able to add data from CSV files (e.g. CSV exported from the Dataverse tables so I can populate the tables in another environment - create in Dev, add into Production).

But in the DataFlow, not all the columns are available to map into (Destination columns for the relationship are not available for example in the "Notes" table).

The lookup column for the relationship exists in the CSV file and appears in the 'Source column" options, but I have no "Destination column" to map it into.

Same goes for that I cannot find the "Destination column" in the main "Problems" table for the Uniuqe ID column that should match with the Relationship column name in the "Notes" table.

I tried creating a key in the "Notes" Table, but it is still not showing in the DataFlow "Destination column".

I also cannot find the matching auto generated Unique Id for the Lookup Relationship column to create the Key in the main Problems table.

That is the only column that will be the same in the "Problems" and "Notes" tables.

If I manually Import Excel file, then the automapping shows all the Table columns including the relationship column.

But the problem is if I have DateTime columns. I keep getting errors with the data formats, and the data will fail to import.

Dataflow allowed me to use Power Query to sort out the DateTime columns, plus make it easier to execute on demand.

[kreed11]

I know this post was a couple years ago, but I had this same issue recently and discovered it was because lookup columns in dataflows must have a key tied to the table that the lookup is referencing. 

1. go to the table that the look up is referencing in dataverse or the solution

2. when on the table object select "Keys" 

here

or here

3. Create a key

4.Wait for the key to become active

5. go back to the dataflow and it should now be a column that you can map to. 

[sdedic]

Hi @kreed11,

I think I have the same issue as @BenGin.  Here is the link to my problem: LookUp Column Values Not Imported Into Solution - Power Platform Community (microsoft.com)

I did try adding a key as you said but it fails with the following error message:

The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.xxx_InitiativeLogBase' and the index name 'ndx_for_entitykey_xxx_keyinitiativelog'. The duplicate key value is <redacted>.

However, inside Keys for that specific table there are no keys:

Could you please provide more details on how you did this?

Thank you.

Best

[Craig1]

I had the same problem and this solution worked for me. Thanks 

[AZWildcat1290]

@sdedic were you ever able to get the key setup issue resolved? I am running into the same issue while trying to setup the keys.  Seems to work for random fields but not ones where there would be unique data...

[Craig1]

Hi,

I have found a process for importing relationships from an external data source into Dataverse that seems to work, although it is a bit of a workaround, in my opinion. The issue is caused by the unusual way that Dataverse handles relationships i.e. via a special type of column which they refer to as a lookup column. This data type is not available in Dataflow. So, this is my workaround (I am sure there are others). By, the way I am importing data from SQL Server where I handle relationships as foreign keys.

1) Step 1 (Dataflow ): I load the foreign table (Table A)  (i.e. the one that contains data that needs to be looked up) from my SQL server database into Dataflow together WITH data. I also flag the column that you want to be looked up as the primary name/key of that table. 

2) Step 2 (Dataflow): I load the table that will "look this data up" (Table B) into Dataverse, but without any data (set the number of rows to keep as 0). This will result in a table with columns in Dataverse but without any data - we just lack the lookup column.

3) Step 3 Dataverse: In Dataverse, select Table A and then add a relationship to Table B (in my case one to many), which will create a lookup column in Table B.

4) Step 4 Dataflow: Create a new dataflow to load data into Table B (which now has the lookup column) 

• data for the lookup column can be added as a custom column by using a merge query
• in the load settings make sure to discard any previous load settings (as these will not contain the look-up column)

So, essentially the method is to load table structures with data for any tables that need to be looked up first. Then load the table structures only for the other tables that reference this data. Thirdly add the relationships in Databererse to create the lookup columns and finally load the data into the other tables including the look-up columns.

It's a bit longwinded but it works. In my case, I had to repeat this whole iteration more than once as some of the other tables also looked up other tables so I had to load the data in tranches.

I hope this helps.

Regards, Craig