Mapping to polymorphic field in dynamics 365

Binway 696 Reputation points
2024-06-03T05:37:20.28+00:00

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/

and here https://medium.com/@mleitner9367/importing-data-with-polymorphic-lookup-fields-into-dataverse-using-azure-data-factory-adf-0a5e0779f211

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.

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,925 questions
{count} votes

Accepted answer
  1. Amira Bedhiafi 18,026 Reputation points
    2024-06-05T17:59:30.72+00:00

    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.

    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Amira Bedhiafi 18,026 Reputation points
    2024-06-03T11:56:26.6866667+00:00

    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')
        
      
    • Map DerivedCustomerId to the CustomerId field in Dynamics 365.
    • Map CustomerIdType to a separate field if necessary, but usually, the concatenated DerivedCustomerId should suffice.

    try and tell is :D