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

Like (1) Share

Report

Posted on 24 Nov 2021 03:24:56 by 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.

Categories:

Dataflows

Microsoft Dataverse

I have the same question (0)

All responses (5)

Answers (0)

Craig1 100 on 01 Nov 2023 at 09:31:07

Like (0)

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

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

Was this reply helpful? Yes No
AZWildcat1290 2 on 31 Oct 2023 at 21:28:08

Like (0)

Report

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

@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...

Was this reply helpful? Yes No
Craig1 100 on 19 Oct 2023 at 18:23:37

Like (0)

Report

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

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

Was this reply helpful? Yes No
ar87 353 on 30 Jun 2023 at 09:41:22

Like (0)

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

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

Was this reply helpful? Yes No
kreed11 14 on 23 May 2023 at 18:42:09

Like (3)

Report

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

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.

Was this reply helpful? Yes No