Hello,
Recent months, i get a chance to explore the Azure based integrations. Its really cool and got excited to try it out for data refresh project.
Today, we discuss something important in terms of integrating with PowerApps / Dynamics 365. Most of the data types work straight forward, but the lookups are tricky. As we have lookups pointing to single table (simple lookup field) and lookups supporting multi tables (i.e. owner, customer), the Dynamics connector expects the data to be formatted in certain way.
I was using data flow for anonymizing some data in accounts, contacts table. Its bit tricky when we try to map the lookup column (simple lookup) to the target. Obviously, have tried look at the documentation, below link.
It does work for copy activity but not for Data flow. 🙁
You may get one of the following errors depends on how you configured the mapping
Error identified in Payload provided by the user for Entity
Microsoft.OData.ODataException: An undeclared property ‘lookup_logical_name’ which only has property annotations in the payload but no property value was found in the payload. In OData, only declared navigation properties and declared named streams can be represented as properties without values
Microsoft.OData.ODataException: The navigation property ‘core_BookingStatusID’ has no expanded value and no ‘odata.bind’ property annotation. Navigation property in request without expanded value must have the ‘odata.bind’ property annotation
How do we do then?
Use a derived a column to modify the source value to be in this format
entity_plural_name(record_guid)
Mapping in the Sink
Add the mapping for lookup output column like below, use the schema name rather than logical name.
bidsl_ProjectId@odata.bind
It is exactly same as passing payload to Dynamics/PowerApps WebApi calls. I was expecting that it should work as described in the document or may be am looking at the wrong place 🙂
Anyway, if someone scratching head how can we do this !! here is the answer 🙂
PC: ChatGPT
