How to Populate Multi-Lookup attribute in CE using Azure data factory

While trying to sync data from sql staging db to CE,  I faced an issue in populating the multi-lookup columns.

In our demo scenario, we will import data from sql to CE in contact entity in following columns:

FirstName, LastName, Email, Company. Here Company is multilookup of contact and account entity both.

Let’s suppose we have a sql table with columns as below:

 select FirstName,LastName,Email,Company,CompanyTypeName FROM [testdb].[dbo].[contactsInbound]

Here CompanyTypeName column have the entity name to which we want to map the “parentcustomerid” lookup on contact entity .

Company is the Guid of that account/contact.

Now if you’re familiar with ADF copy activity, when we do import schema, it shows the columns available in source (sql in our case) and try to map them to target (CE in our case). This mapping we can change.

Now for our columns we’ll do mapping as shown below.

Note that we have mapped Company column which have guid of account/contact to parentcustomerid column.

Now, let’s click on debug button and see the error:

Let’s expand the error which says:

Cannot find the target column for multi-target lookup field: ‘parentcustomerid’

This error means, CE is not able to identify to which target entity it should search the guid in hence throwing the error.

Now, let’s fix this by passing the name of target entity as account/contact.

Note in our sql table we have CompanyTypeName column which will have value as accuont/contact according to the guid belonging to the entity.

We need to add a virtual column, which doesn’t come by default when we do import schema since it is not an actual column but to just tell CE about metadata of that lookup.

In target type “parentcustomerid@EntityReference” (format is “<lookupname>@EntityReference“) and in source select CompanyTypeName column which will have value as account/contact which is the name of target record entity.

Now Click on debug again and see it got succeeded:

Now navigate to CE and see that Company lookup is populated with the account who’s guid we passed in Company column in sql.

Note: If all records in source belongs to same entity, there is no need to create the column for entity name in source, we can create a dummy column in source in adf itself:

This columns will be available in source mapping once we do import mapping:

Just click debug and it should run successfully and will populate the lookup field:

Happy Learning..!!