This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](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(108.80000000000001, 44%, 20%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EMK%3C/text%3E%3C/svg%3E)
Hi,
I would like to perform an Upsert based on two fields.
Example
Account Entity : Alternate Key Name (new_accountnumber) - consist of new_accountnumber field
Contact Entity : Alternate Key Name (new_contactkey)- consist of new_contactnumber(Text) and new_accountid(Lookup)
new_accountid is a lookup field to Account Entity.
I would like to perform Upsert using text and lookup field in ADF. I am not able to find a correct way to set value for the lookup.
Please find a similar solution implemented using C#
https://xrmdynamicscrm.wordpress.com/2020/09/14/cds-developer-quick-tip-set-lookup-field-using-altername-key-during-create-and-update-record-from-c/
Hi @Mihir Kadam ,
Welcome to Microsoft Q&A. Thank you for posting your query here.
"alternateKeyName" filed in Sink helps ADF to define alternate key name on your entity to do an upsert. Click Here to know more about Dynamic Sink Dataset property details.
Are you looking for multiple alternate key names to define there?
Between, If you would like to have C# code implemented and executed as part of your ADF pipeline then you can go with Azure Function activity.
@ShaikMaheer-MSFT Thank you for the Inputs.
I am looking for a solution to perform Upsert directly from the ADF, not from the C#(Azure Function).
I am able to perform Upsert on Text Field but not for the two data types (Text and Lookup).
I am not sure how to construct the value for the Lookup Upsert request.
Hi @Mihir Kadam ,
We are checking this internally. We will share updates soon. Thank you.
Hi @Mihir Kadam ,
We are still awaiting on updates from product team. We will let you know once you get response from them.
Thank you @ShaikMaheer-MSFT . I will wait for your response.
Hi @Mihir Kadam ,
Below is the response from Product team.
"The upsert can work as long as the source contains the columns which are defined by the alternate key."
So, If your source dataset doesn't have columns which you want to be defined by the alternate key then its not possible directly from ADF. Thank you.
Hi @Mihir Kadam ,
Could you please see if above provided answer helps you? Please let us know if any further quires. Thank you.
![](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(265.6, 6%, 35%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EFO%3C/text%3E%3C/svg%3E)
I was worried about the same stuff how one should specify 2 keys (composite Primary key) in ADF when one of them is not showing in the ADF. Should i write the different logic etc.. But !!!
You don't need to specify in case of Dataverse. ADF take care of sink table composite keys by itself.
you might need to specify it for SQL SERVER etc.
![](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(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
The same issue here, if I designe the table in Dataverse using simple text column as alternative key, everything works as expected and upsert is working as expected within the Dataflow.
If I do the same but I will create composite key using e.g. standard text field and a lookup Im not able to save anymore the data as datflow is throwing errors that the lookup field value is missing in the payload. The oinly workaround that works for me is to sink data to dummy json file from Dataflow and in next pipeline action use CopyActivity to upload json to Dataverse. I already opened a case within MS to get a clear message why this is happening, they are still investigating the issue.