Updating and Inserting Lookup fields in Dataverse with Azure Data Factory (ADF)
I found an interesting behaviour when inserting/updating Dataverse's lookup fields in DataFlows with ADF. The only instruction for the lookup fields you can find on the internet is about the "Copy" activity in the "Pipelines", which doesn't work for DataFlows. Exceptions you receive don't show any meaningful info, just generic errors. There is no simple solution on the internet. The only one I found was to use DataFlow to prepare and transfer the data to a blob file and then copy this data to Dataverse (by using the "Copy" activity in a Pipeline). But this is overcomplicated and costs extra $.?
So, after several hours of intensive debugging and researching, an easier and a way cost cost-efficient solution was found. Here it is:?
1) 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?[email protected]?. Please note that for all other fields, you have to use LOGICAL NAME! I know...
2) the value you're passing to the column is?"/xx_orders(xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxx)"?- ensure the entity name is PLURAL and lowercase!
3) to generate this value, you can use the "Derived Column" block, putting?"/xx_orders(" + your_entirty_id + ")"?into the expression field.
I hope this will save you many hours and nerves ??
Dear ADF team, you're building an awesome product, but could you please?
1) 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.
2) Simplify the mapping method for Lookup fields in Dataverse.
3) Add to your instructions Lookups mapping for Data Flows, because current instructions are about the pipelines only, which surprisingly has a different logic.
Senior Technical BI Consultant at Barhead Solutions
1 年I still encountering the same issue :( all details are correct don't know the reason
Azure Data Engineer
1 年Hi Denis, Can we accomplish this using ADF web Activity?
Senior Solution Specialist at Deloitte| AWS Certified | ETL Developer
1 年I used the above method and getting the error "An undeclared property 'xx_cw_OverrideCreatedBy' 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.". Does anyone know the reason why? just a note, the target in this case is Dynamics 365
Hi Denis, After a week facing the same issue that you described here (not being able to insert a lookup value and debugging errors not giving me much information about how to overcome it). I came across this post and it works perfectly. Thank you for this!
Senior System Administrator at RVS Technology Group
1 年Hi Denis. I am being hitting my head to the wall for two weeks trying to update a lookup column using an ADF data flow with no luck until now. The inconsistency on names and lack of documentation is surprising. Thank you for your post! ??