This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(80, 10%, 17%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EB%3C/text%3E%3C/svg%3E)
Hello Team - I am loading data into the Incident table of a Dynamics 365 environment from an Azure SQL DB using the Azure Data Factory.
the Incident table has lookups to the Account table on AccountId and Contacts via the ContactId. I have populated these two tables and obtained their GUIDs using a Data Flow so the mapping is straightforward up to this point.
There is a field called CustomerId that is a polymorhpic field.
Schema Name:
CustomerId
logical name:
customerid
Account relationship name:
incident customer accounts
Contact relationship name:
incident_customer_contacts
I am not certain I have the correct code for mapping this field.
I reviewed the info at this url https://xrmdynamicscrm.wordpress.com/2020/06/25/dynamics-365-setting-multi-entity-lookup-field-while-writting-data-from-azure-data-factory/
but they do not appear to work.
I had created a derived field in the source table as a test where it has account(d0f6bg41-2s2e-5b67-8765-78092e0g67c) or contact(sdgsdde-5r5y-8765-73456h67y) mapped to the target tables customerid column. But this returns an error that the customerid can only have a guid value of 32 digits with 4 dashes suggesting my value is not correct. After reading some info on micrsoft at https://learn.microsoft.com/en-us/azure/data-factory/connector-dynamics-crm-office-365?tabs=data-factory#writing-data-to-a-lookup-field I changed the mappings to customerid to customerid (guid) and to have a target field with the values of account and contact mapped to the field customerid@EntityReference which appears to have gotten me a step closer as one of the errors I got was the only valid entities are account and contact eg not the plural name. Now I get an error that says "Dynamics operation failed with error code: -2147220891, error message: Cannot find record to be updated" and can't figure out where this is coming from as the GUIDs for customerid are in their respective tables and this is new data into the incident table. Any thoughts as to what would be giving me this error.
Hi
Thank you for posting query in Microsoft Q&A Platform.
It sounds like you are trying to load data into the Incident table of a Dynamics 365 environment from an Azure SQL DB using Azure Data Factory (ADF), and you are having trouble mapping the polymorphic CustomerId field.
Based on the information you provided, it seems like you have tried a few different approaches to mapping the CustomerId field, but you are still encountering errors. Here are a few suggestions that may help you resolve this issue:
Make sure that the GUIDs you are using for the Account and Contact entities are correct and exist in the respective tables. You can verify this by querying the tables directly in Dynamics 365.
Check if the CustomerId field is required in the Incident table. If it is, make sure that you are providing a valid value for the field in your data source.
Try using the customerid_account and customerid_contact fields instead of the customerid field. These fields represent the relationship between the Incident table and the Account and Contact tables, respectively.
Make sure that you are using the correct entity names and relationship names in your mappings. The entity names should be singular (e.g. "account" instead of "accounts"), and the relationship names should match the names defined in Dynamics 365.
Check if there are any customizations or plugins in Dynamics 365 that may be interfering with the data load. You may need to work with your Dynamics 365 administrator to troubleshoot this issue.
If none of these suggestions work, you may need to provide more information about the specific error message you are seeing and the mappings you are using in ADF.
Thanks ShaikMaheer - I have filtered my source data down to 1 record with an accountid that is in the account table and has the value in the Target field for the mapping Target -> customerid@EntityReference set to value account. customerid is a required field in the incident table and the data i have to insert has values for all the required fields only in the incident table. customerid_account doesn't work anywhere. i am going to get the sys admin to add a record just to check that it is doable - i'll also check the values I am inserting to ensure they are correct as the error i am getting Dynamics operation failed with error code: -2147220891, error message: Cannot find record to be updated" does not specifically say it is the customerid giving the grief.
Thanks ShaikMaheer - I have filtered the source data down to one record that defintely has the accountid being mapped to the customerid. I tried customerid_account in both the mapping for the customerid which returned an error thats not a valid field in incident. also tried customerid_account in the Target mapping but it is an error that the field is not correct. I have data to migrate into all the required fields which includer customerid. I'll work with the sys admin to see if we can get a record in manually and also check the data i am migrating as the error Dynamics operation failed with error code: -2147220891, error message: Cannot find record to be updated" does not specifically state customerid is the problem.
I have used the powerapps import wizard to import the data successfully into the incident table, the wizard asks for the Contact or Organisation (Account) table, once all mapped the data went in so there is no issue with the data that I am using, I then deleted this record. I did a few more alterations to the mappings attempting to look for different ways of adding the data but keep getting the same error that the record can't be found. I did another import with the wizard so this record is in incident table. Then the ADF copy activity worked fine which indicates that it was looking for the existing record and I have the sink incorrectly configured, which is odd as I have this sink configured the same as the other dynamics 365 tables i have already loaded. The write behaviour for the sink is Upsert and there are no other options.
I am glad to find that you resolved your question.
As you cannot accept your own asnwer, let me reformulate the answer and don't forget to validate it:
Initially, the incident table was empty. After adding a record using the Dynamics import wizard, I was able to successfully upsert this record.
I think the error is because of the system which cannot find the referenced record, which typically suggests an issue with how the GUIDs are being referenced or formatted.
Try to create two derived columns:
DerivedCustomerId:
iif(isNull(AccountId), concat('contact:', ContactId), concat('account:', AccountId))
CustomerIdType:
iif(isNull(AccountId), 'contact', 'account')
DerivedCustomerId to the CustomerId field in Dynamics 365.CustomerIdType to a separate field if necessary, but usually, the concatenated DerivedCustomerId should suffice.try and tell is :D
thanks Amira - Yes I tried the input format of account(d0f6bg41-2s2e-5b67-8765-78092e0g67c) or contact(sdgsdde-5r5y-8765-73456h67y) mapped to the customerid but the error returned says customerid will only accept a guid that is 32 digits and 4 dashes. By adding the string account it causes the error. The closest i have come is by mapping the cusotmerid guids eg d0f6bg41-2s2e-5b67-8765-78092e0g67c to d0f6bg41-2s2e-5b67-8765-78092e0g67c and having a derived field that returns the Target entity eg account and it maps to the table via a bit of code customerid@@EntityReference which retunrs the error that it can't find the record to be updated even though this guid is in the account or contact table.
In the Data Flow, try to add a derived column transformation to prepare the data for Dynamics 365 and see what it gives :
concat(CustomerId, '@@', CustomerIdEntityType)
Thanks Amira - I got the migration working using the info as per the writing-data-to-lookup field link in my question. My problem was that the incident table was empty, once I put this record in using the dynamics import wizard then i could upsert this record - however this brings a new question as to why I have to have an existing record in there in the first place. That will be a new question I will put on here.