Dataflow import to CDS error "missing destination field" on alternate key

[packnology]

Hi,

I'm importing data from an on-premise SQL Server database into CDS via the on-premise gateway. I have already set up my CDS entities with alternate keys to allow relationships (lookups) to be maintained between the source and CDS when synchronizing. I seem to have a bug in my dataflow where it is trying to import to a destination field (alternate key on a related entity) that doesn't exist in the related entity. The error message in my dataflow refresh history detail is: "Missing destination field cr423_gradergrower.cr423_grader in the schema".

The destination field is automatically set to "cr423_GraderGrower.cr423_Grader" but I think it should be "cr423_GraderGrower.cr423_Grader.cr423_SerialNumber" because the "cr423_Grader" related entity has a key "cr423_SerialNumber". Is Dataflow able to use cascading (multi-level) alternate keys in this way, or should I try another way to ensure relational integrity?

[EricRegnier]

Hi @packnology,

Dataflows doesn't support multi-level alt keys and I don't even think it supports alt keys defined with >1 fields. Any chance you can modify your entity model to have a direct link to Grader? Another option is to have a new "staging" entity and use that as your destination entity, then with the help of Power Automate (or another preferred method), create the "real" entities. You can also leverage Power Automate as an alternative to Dataflows if Dataflows doesn't satisfy your requirements.

Cheers
 

[HarryJohn]

Hello, Ran into the same problem here with the following error:

Missing destination field cr915_category.cr915_name in the schema

 The solution for us was as follows:

1. Click the Settings Cog -> Admin Center (Power Platform admin center)
2. Click Data Integration on the left menu
3. Click the Connection sets tab
4. You will find a connection set in here related to the Dataflow, named similar to (in my case): D2E-20201015-223826
5. Click the connection set found in step 4
6. Select the Integration key tab
7. Hopefully, despite adding an alternative key before you got this error, you will find there are no Integration keys assigned to the parent tables. 
8. Click Add key against the parent table and select the same field you have set as the alternative key (what you want to lookup against)

I have not seen this documented or answered anywhere online. Hope it helps

[jodunni1]

Greetings all.  I was running into exactly this problem as well with a CDS custom table (Case) related to a CDS delivered table (Contacts) and HarryJohn's solution resolved it for me (thank you!).  Now, however, when trying to import Note/Annotations associated with the Case, I get a similar error:  

Missing destination field objectid.crf8b_legacycase in the schema

Below are screen shots of my data flow field mapping and the Connection Set Import Key pages respectively.

Any ideas?  Thanks much!