Simplifying the Upsert Operation in Dataverse

3 min readAug 13, 2023

According to database nomenclature, Upsert is what really means to update or insert a record. During this operation, the resultant record gets created if it doesn’t exist or gets updated if it already exist. By default, Dataverse will do the upset operation while using the Web API PATCH. But we must be aware of when do we need upsert and when it is not needed. Upsert can be used in complex data integration processes where we won’t know the record already exists or not. At this kind of scenario’s upsert would be a great pick. For more details. Please refer Microsoft’s Official Documentation

Fact:

We can even create records with the custom GUID (not the auto-generated one) while performing Upsert Operation. I have tested with the 11111111–1111–1111–1111–111111111111, and guess what? It worked.

Upsert using Power Automate

We can use Power Automate to do the upsert operation with the Dataverse connector. As the ‘update a row’ step actually creates the PATCH request to the Dataverse API. Create an instant flow and add the next step with ‘Update a row’ in Dataverse connector. Use any new GUID and a name. Save and Run the flow. The flow will get executed, and the record is created with that particular GUID we used in the flow.

Upsert using JavaScript

We can do the upsert operation through JavaScript(JScript) for Power Apps. But unfortunately, it is not supported by the Xrm Web API (Client API). But we can use JavaScript’s fetch request to accomplish the same. We need to do the below steps to perform upsert operation using JavaScript.

Create a JavaScript Web Resource under any of your solution.
Add the below function to the Web resource, then save and publish it.

function upsertRequest() {
    let payload = {
        "name": "Test Account"
    };

    let options = {
        body: JSON.stringify(payload),
        method: 'PATCH',
        headers: {
            'Content-Type': 'application/json',
            'Accept': 'application/json'
        }
    };

    // Perform upsert request
    fetch("https://org2ce812b1.crm8.dynamics.com/api/data/v9.2/accounts(5531d753-95af-e711-a94e-000d3a11e605)", options).then(
        response => {
            debugger;
            const responseBody = response.json().then(
                data => {
                    if (data.error) {
                        console.log(data.error.message);
                    }
                    else {
                        var jsonResult = JSON.stringify(data);
                    }
                }
            );
        }
    );
}

Add the function to any of the form event. I have added it in on-load of account form.

Best Practices:

If you are not sure about the existence of the record (thought of doing upsert), you can use PATCH request without any header.
If you only want to update a record in Dataverse, you must use the PATCH request with the header If-Match: *.
If you want to create a record using PATCH request, you must use the header If-None-Match : *.

Upsert using Plugins

We can perform upsert via plugins using UpsertRequest object. In this article, I have created a plugin to perform upsert request on accounts table. The below is the code.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.ServiceModel;
using Microsoft.Xrm.Sdk;
using Microsoft.Xrm.Sdk.Query;
using Microsoft.Xrm.Sdk.Messages;

namespace TestUpsert
{
    public class Upsert : IPlugin
    {
        public void Execute(IServiceProvider serviceProvider)
        {
            IPluginExecutionContext context = (IPluginExecutionContext)serviceProvider.GetService(typeof(IPluginExecutionContext));
            IOrganizationServiceFactory factory = (IOrganizationServiceFactory)serviceProvider.GetService(typeof(IOrganizationServiceFactory));
            IOrganizationService service = factory.CreateOrganizationService(context.InitiatingUserId);

            ITracingService tracingService = (ITracingService)serviceProvider.GetService(typeof(ITracingService));
            try
            {
                Entity account = new Entity("account", "accountnumber", "ABC001");
                account["name"] = "Test Account";
                //upsert request
                UpsertRequest request = new UpsertRequest()
                {
                    Target = account
                };
                UpsertResponse response = (UpsertResponse)service.Execute(request);
                if (response.RecordCreated)
                    tracingService.Trace("Created");
                else
                    tracingService.Trace("Updated");
            }
            catch (Exception e)
            {
                throw new InvalidPluginExecutionException(e.Message);
            }
        }
    }
}

Conclusion

While using Upsert we must make sure that the updation of the existing record will not affect any other record though associations with any kind of relationship(1:N or N:N). Priorly analyse the situation whether to use upsert or not.

Have a great day!

Originally published at https://www.tamilarasu.me on August 13, 2023.