Map A Multi Target Lookup Field In Azure Data Factory – Dynamics 365 Data Import

Dynamics 365 has these special lookup fields which can reference multiple entities. Meaning, you can select record not just from one entity but other as well. One of the typical example is an Owner field where you chose a user or a team as well, A customer field where you can chose an account or a contact as well. 

These fields have sister string fields which holds the entity schema name of the record in the main lookup field. i.e. for ownerid field, it will be owneridtype where it will be “systemuser” if owner is an user or “team” if team is the owner. These fields are often referred to as Virtual fields as well. 

In my case, I will be talking about Notes(Annotation) Entity. Notes can be associated with any entity in the system using the objectid or regarding field to be clear. The corresponding field for this lookup field is objectidtypecode which holds the target entity schema name. 

if you simply do the mapping to objectid in your target dynamics instance, you will error like below:

Operation on target Create Annotation failed: Failure happened on 'Sink' side. ErrorCode=DynamicsMissingTargetForMultiTargetLookupField,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Cannot find the target column for multi-target lookup field: 'objectid'.,Source=Microsoft.DataTransfer.ClientLibrary.DynamicsPlugin,'

Even if you map objectidtypecode to objectidtypecode directly, you will get error.

Solution: 
It was not possible to work with multi target fields at all earlier with ADF but now its possible by doing below. so now, you will have to map objectid to objectid(the main lookup field) from source to target. 
Along with that, you have to map objectidtypecode or any field which holds the schema name of the lookup field to an expression as “{lookup_field}@EntityReference”. So in my case it will be objectid@EntityReference.:

And there you go. It will work flawless if you do this. To explore more refer to https://docs.microsoft.com/en-us/azure/data-factory/connector-dynamics-crm-office-365

I hope this helps!

Leave a comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.