How to Fix Multi-Target Lookup Errors in Dataverse Using Azure Data Factory and Synapse Analytics Pipelines

Do you know those errors that give you a headache for a while, the ones that make no sense because you’ve done everything correctly? And it gets even worse when you finally find the solution, only to realize it's simple and quick to implement... Well, stick around because I’m going to tell you about the latest situation I had like this, and by the end of this read, you'll know how to avoid this problem quickly.

Understanding the Problem

The case in question occurred while loading records into the Accounts table in Dataverse using a pipeline in Azure Synapse Analytics (also applicable to Azure Data Factory). Everything seemed to be set up correctly, but when performing the upsert operation, I encountered the following error:

“Cannot find the target column for multi-target lookup field: 'ownerid'”

The error occurs because the ownerid field can relate to both the User and Team tables. Although I had configured the mapping to indicate that the value came from a user (owneridtype = "User"), the process kept failing because Dataverse couldn't automatically resolve the relationship.

As shown in Image 1, the initial mapping seemed correct, but it wasn’t enough. After numerous tests and searches, I discovered that the issue lay in how Dataverse requires multi-target lookup fields to be configured. It’s not enough to specify the entity type (owneridtype); the correct format must be used for the target field, which I will explain in the next section.

Image 1. Mapping performed by the Copy Data activity when importing the schema

Ultimately, Dataverse requires additional information to specify which entity the ID in the ownerid mapping belongs to. However, even though it may seem like the solution is simply adding owneridtype, that is not the correct approach.

Modifying the JSON to Set EntityReference in the Mapping

To resolve the described issue, while in the pipeline design tool, you need to access the JSON view provided by Azure tools.

Image 2. Option to view the pipeline in JSON format

After clicking the button that allows you to view the pipeline's JSON and searching for the section related to the owner field mapping, you can see the code representation of what was shown in Image 1.

Imagen 3. Mismo mapeo anterior pero visto desde el JSON de la actividad Copy Data

At this point, the only modification needed to address the described error is to change the owneridtype mapping so that, in the destination (sink), it is ownerid@EntityReference instead of the previously mentioned owneridtype expression. Therefore, the mapping should be set as shown in Image 4:

Image 4. JSON with the correctly referenced mapping in the Copy Data activity.

Conclusion

Finally, it's important to highlight that beyond the owner field, this is the correct way to map any multi-target lookup field that needs to be imported into Dataverse. Once the flow is run again, everything goes smoothly.

I hope this article helps you and saves you the time I had to invest in finding the solution, which, as I mentioned at the beginning, is quick and simple to implement.

Similarly, if you’ve faced a similar issue or need any clarification, feel free to let me know.

See you next time!