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:
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:
- Create a Test Power Automate Flow with a manual trigger.
- Add a Dataverse create row action with the table that contains the lookup field (in this example I’m using Opportunity)
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.
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.
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.
