Dataflow - Destination column not present for LookUp Field created from a Relationship
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.