Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,516 questions
![](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(118.4, 95%, 21%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EML%3C/text%3E%3C/svg%3E)
Would also be interested in the solution for this issue.
ADF Mapping Data Flow Sink to Dataverse Lookup field
![](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(204.8, 45%, 29%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EVT%3C/text%3E%3C/svg%3E)
Vítek Miroslav
11
Reputation points
Hello,
I want to load data from Dataverse to ADF, use Mapping dataflow transformations and at the end of the Mapping data flow sink it back to Dataflow - create new rows in table
I have this entity/table called xx_orders. There are several fields. When I try to create row only with fields from this custom table, there is no problem. But I am not able to create row in mapping data flow and sink it with field, that is Dynamics lookup field to another custom entity: xx_instrument. The lookup field is xx_instrumentid.
When I source the dynamics table, the column loads as xx_instrumentid as a string value (GUID). But when I try to create new row in xx_order with column named xx_instrumentid (abd guid string value) I got errors with: An undeclared property 'kwr_instrumentid' which only has property annotations in the payload but no property value was found in the payload.
I also tried to create column called xx_instrumentid@odata.bind and fill value like xx_instruments(81ad87ab-2b42-ec11-8c62-000d3aabce66). Same error. What column(s) and value(s) should I have in ADF mapping dataflow to be able to fill lookup field when creating new row in sink.
Thank you
{count} vote
-
ShaikMaheer-MSFT 37,896 Reputation points Microsoft Employee2022-03-03T12:09:25.07+00:00 Hi @Vítek Miroslav ,
Thank you for posting query in Microsoft Q&A platform.
Sorry but not able to understand the ask here. You mean you want to create a additional column on top of source transformation data? If yes, you can make use of derived column transformation. Click here to know more about derived column transformation. This video also helps to understand derived column transformation.
What you mean when you say you want to create new row? Exactly in which transformation you are facing this error?
Kindly help by sharing your dataflow implementation step by step by screenshots may be to understand the issue and help with resolution. Thank you.
-
ShaikMaheer-MSFT 37,896 Reputation points Microsoft Employee
2022-03-08T14:14:46.303+00:00 Hi @Vítek Miroslav , - Just checking if you get chance to see above and share details?
-
ShaikMaheer-MSFT 37,896 Reputation points Microsoft Employee
2022-03-14T15:37:31.137+00:00 Hi @Vítek Miroslav , -Following to see if you get chance to see above and share details?
-
Vítek Miroslav 11 Reputation points
2022-05-23T11:59:54.57+00:00 I have simple Data Flow - I load Dataverse table. When I want to update column, in this case Date - there is no problem.
But, how can I update lookup column (relationship do another table). How can this be achieved in ADF Dataflow. For inserting values I am using the "workaround". I sink the result as a csv file a and then in pipeline I am using the Copy Activity step. But, is it possible to update the lookups from data flow (without csv sink and then another copy activity in the pipeline)?
-
![](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(313.6, 99%, 40%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EDG%3C/text%3E%3C/svg%3E)
Dipalee Gawarikar 5 Reputation points2023-08-09T03:46:53.28+00:00 I m going thru same scenario - if we can get explanation of 'how can I update lookup column (relationship do another table)' - it will be good.
-
![](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(9.6, 21%, 10%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ESG%3C/text%3E%3C/svg%3E)
Sumit Garg (MAQ LLC) 0 Reputation points Microsoft Vendor2023-09-01T14:30:01.4266667+00:00 @ShaikMaheer-MSFT Do we have any solution for above? I am facing same problem
-
![](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(131.20000000000002, 95%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EDD%3C/text%3E%3C/svg%3E)
Denis D 0 Reputation points2023-09-28T06:11:14.21+00:00 Vítek Miroslav, you're a legend, man! I had been fighting this issue for several hours until I found your question about adding @odata.bind to the destination column.
It didn't work for you because you used a logical name instead of a schema name... I know...
So the solution is,
- in the sink mapping, you set the lookup's destination column with schema name (which is usually PascalCased), and append @odata.bind so that the column name would look like xx_InstrumentId@odata.bind
- the value you're passing to the column is "/xx_orders(xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxx)" - ensure the entity name is PLURAL and lowercase!
- to generate this value, you can use the "Derived Column" block, putting "/xx_orders(" + your_instrument_id + ")" into the expression field.
Dear ADF team, you're building an awesome product, but could you please
- standardize the names we use for data mapping. It's very confusing where to use logical and schema names working with Dataverse. Plus, the exceptions we got are not clear.
- Simplify the mapping method for Lookup fields in Dataverse.
- Add to your instructions Lookups mapping for Data Flows, because current instructions are about the pipelines only, which surprisingly has a different logic.
Sign in to comment