Azure Data Factory: How to Update Entity Reference Lookup Fields

Azure Data Factory

Table of Contents

A man with short brown hair and a beard smiles while wearing a light gray suit, green striped tie, and a boutonniere with orange and purple flowers, ready for a Power Platform consulting or Dynamics 365 Customer Engagement event. Blurred signage fills the indoor background.

By Mike Mitchell, Senior Consultant

Azure Data Factory is a powerful cloud-based data integration service, providing organizations with a robust platform for building, managing, and leveraging their data pipelines. At New Dynamic, we’ve worked with this platform for many years, helping our clients integrate Azure Data Factory into their Dynamics 365 stack and business processes. The Entity Reference lookup field functionality is one that we sometimes get questions about, as there can be some technicalities to handle when integrating Azure Data Factory with Dynamics 365. 

How to Update Entity Reference Lookup Fields 

When integrating Dynamics 365 and Azure Data Factory, an overlooked feature we’ve come across is finding the exact syntax name needed to upload data into Entity Reference lookup fields. We’ve seen this issue before when building Data Factory pipeline mappings to a lookup field, and it usually involves receiving an error message like this: 

A screenshot of a webpage displaying a long string of technical error or log text, with URLs, error messages, and code—some sections redacted by black bars. The content appears to relate to Microsoft D365 authentication issues.

  

This error means one of the lookup fields you are attempting to map in the sink step is not built with the correct API OData formatting.  You will need to find the API lookup field syntax, which can be difficult to do as it’s a backend field that is not easily accessible. 

Here’s how to solve this problem: 

  1. Create a Test Power Automate Flow with a manual trigger. 
  2. Add a Dataverse create row action with the table that contains the lookup field (in this example I’m using Opportunity) 
A flowchart interface in Azure Data Factory showing Manually trigger a flow leading to Add a new row, with buttons for + New step and Save below.

3. Find that Display name of the lookup field and type in a fake value like ‘TEST’ (this will add the field to the parameters section and give you the correct API OData lookup name. 

4. Then, simply peak the code on the Dataverse action. 

A screenshot shows a workflow setup in Power Automate integrated with Azure Data Factory. A form for adding a new row to a Dynamics 365 Opportunities table is filled out, with fields like Name, Opportunity Status, and two potential customers labeled TEST1 and TEST2.

5. At this point, the system will show you the real API OData lookup field name.  You can then copy that name full name into your Azure Data Factory mappings inside your D365 Sink step. 

A screenshot of a Microsoft Power Automate flow integrated with Azure Data Factory, displaying a JSON payload and mapping settings for adding a new row, highlighting TEST1 and TEST2 values in the customerid_account fields.

6. Now, when you check the Data Preview option in your ADF Data Flow, the lookup value will be formatted correctly, and the data will be integrated into your D365 correctly with no errors! 

Succeeding with Azure Data Factory 

With its intuitive drag-and-drop interface and comprehensive set of connectors, Azure Data Factory simplifies the complex process of data integration, making it easier to manage data pipelines. We’ve helped businesses develop this important part of their Microsoft technology stack for many years, and can help you tie the efficiencies you create back to tangible ROI for your business. 

Author

Join the Community

If you found this blog helpful, subscribe to receive our monthly updates.

Table of Contents
Share with your network

What our Customers Say