Error to set lookup field in N:1 relationship while writing data to Dynamics CRM from Azure Data Factory

AnaC 0

While trying to sync data from sql staging table to Account entity in CRM, I faced an issue in populating the multi-lookup column.

The input column is Manager Name that is of string data type. The destination field is dfws_sareportingmanager (GUID data type) in the Account entity. The "Manager Name" should exactly match "fullname" in systemuser entity.

Below is a screenshot with the the relationship lookup in Dynamics 365:

User's image

I created a copy activity in ADF. The normal field mapping should be "Manager Name" in the source to dfws_sareportingmanager in Account entity but the datatypes are different between the 2. The "Target_SystemUser" column contains the logical name of the target entity (systemuser) and represents the entity reference.

I am not really sure how to pass the Manager Name from source to sink Account entity, if dfws_sareportingmanager is of GUID datatype.

User's image

When running the Copy Activity it returns an error

User's image

However, this was passible with KingswaySoft in SSIS:

User's image

Pratik Somaiya 4,201 Reputation points

2023-01-27T17:40:38.6833333+00:00

Hello AnaC

As far as I know about this, the reason why the value is GUID in sink is because the user details are stored in a separate system user table and then its GUID is referred in all entities, so you need to either join GUID from your SQL table (if present) to the GUID in Account entity or find a way to join with system user table in account entity to map it with the name

Not sure if this helps :)

Thank you
KranthiPakala-MSFT 46,422 Reputation points Microsoft Employee

2023-02-14T08:36:29.5+00:00

Hello AnaC,

Just checking to see if the above infromation from @Pratik Somaiya was helpful. And if you have further questions, do let us know.

Thank you