In this blogpost, we will learn to use Upsert & UpsertMultiple request to create or update table rows in Microsoft Power Apps / Dynamics 365 Customer Engagement. Before we begin, ensure you subscribe to CRM Crate to remain informed about the latest developments in the Power Platform field.

An upsert operation in the context of a data table is a combination of “update” and “insert.” It’s a convenient way to manage records in a database, ensuring that you maintain data integrity without having to write separate logic for inserting new records and updating existing ones. Here’s a more human-friendly explanation:

Imagine you have a list of contacts in a database, and you regularly receive updates or new entries for this list. Sometimes, these updates contain information about contacts that are already in your list (like a phone number change), and sometimes they contain entirely new contacts that need to be added.

With a traditional approach, you would have to:

1. Check if each contact already exists in your database.
2. If it does, update the existing record with the new information.
3. If it doesn’t, insert the new contact into your database.

This process can be cumbersome and prone to errors, especially if you’re dealing with a large dataset.

An upsert simplifies this by doing both actions in one step:

1. Update if the record already exists.
2. Insert if the record doesn’t exist.

So, when you perform an upsert, you provide the data to be inserted or updated, and the database system takes care of checking for the record’s existence and then either updating it or inserting a new one accordingly. This operation is particularly useful in scenarios where data synchronization and consistency are critical, such as in real-time applications, data integration tasks, or when handling batch updates. In terms of Power Apps / Dynamics 365 CE, the Upsert updates an existing row or creates a new row in the system.

---

Using Upsert request to Create or Update a table row in Power Apps

You can simplify data integration by using the Upsert message. When importing data into Microsoft Dataverse from an external system, like during a bulk data integration, you might not know if a record already exists in Dataverse. This uncertainty makes it unclear whether to use the Update or Create message, typically requiring you to check if the record exists first. By using the Upsert message, you can bypass this step, reducing complexity and making the data loading process more efficient.

It’s important to note that using Upsert incurs a performance penalty compared to using Create. If you’re certain the record doesn’t exist, opt for Create message instead.

Upsert messages are handled on the server, and the .NET SDK classes utilize the same objects as the server. The following diagram demonstrates the server’s processing logic when it receives an UpsertRequest for a standard Dataverse table:

SDK for .NET Sample code (Upsert Request):

Your client application utilizes the IOrganizationService.Execute method with an UpsertRequest instance, where the Target property is set with an Entity instance containing the data for either a Create or Update operation. Typically, the Entity instance will have the Entity.KeyAttributes property set with values to identify the record using alternate keys.

The UpsertResponse.RecordCreated property indicates whether the record was created, while the UpsertResponse.Target contains a reference to the record that was either created or updated.

The below given .NET Console Application code demonstrates the usage of Upsert request where CRMCrateUpsertOperation method is used to apply the UpsertRequest message on the contents of an XML file to create new rows or update existing ones.

public static void CRMCrateUpsertOperation(CrmServiceClient service, String File)
{
    Console.WriteLine("Executing upsert operation.....");
    XmlTextReader tr = new XmlTextReader(File);
    XmlDocument xdoc = new XmlDocument();
    xdoc.Load(tr);
    XmlNodeList xnlNodes = xdoc.DocumentElement.SelectNodes("/cataloglist/catalog");

    foreach (XmlNode xndNode in xnlNodes)
    {
        String catalogCode = xndNode.SelectSingleNode("Code").InnerText;
        String catalogName = xndNode.SelectSingleNode("Name").InnerText;
        String catalogCategory = xndNode.SelectSingleNode("Category").InnerText;
        String catalogMake = xndNode.SelectSingleNode("Make").InnerText;

        //use alternate key for catalog
        Entity catalogToCreate = new Entity("sample_catalog", "sample_catalogcode",catalogCode);

        catalogToCreate["sample_name"] = catalogName;
        catalogToCreate["sample_category"] =catalogCategory;
      catalogToCreate["sample_make"] =catalogMake;
        var request = new UpsertRequest()
        {
            Target =catalogToCreate
        };

        try
        {
            // Execute UpsertRequest and obtain UpsertResponse.
            var response = (UpsertResponse)service.Execute(request);
            if (response.RecordCreated)
                Console.WriteLine("New record {0} is created!",catalogName);
            else
                Console.WriteLine("Existing record {0} is updated!", catalogName);
        }

        // Catch any service fault exceptions that Dataverse throws.
        catch (FaultException<Microsoft.Xrm.Sdk.OrganizationServiceFault>)
        {
            throw;
        }
    }
}

Important note related to Elastic tables

In elastic tables, Upsert behaves differently compared to standard tables. It doesn’t decide between Create or Update based on the record’s existence. Instead, it directly applies changes to the entity.
1. When the record exists: The existing record’s data is completely replaced by the data in the entity, without triggering an Update event.
2. When the record doesn’t exist: A new record is generated, but without initiating a Create event.

---

Using UpsertMultiple request to Create or Update a table row in Power Apps

Upsert is handy for integrating data from external sources, especially when you’re unsure if the table already exists in Dataverse. It often relies on alternate keys to pinpoint records. If you need to do Upsert operations in bulk, consider using UpsertMultiple for efficiency.

You can use UpsertMultiple with tables that have CreateMultiple and UpdateMultiple capabilities, like all elastic tables. If you’re using standard tables, you can check if a table supports both CreateMultiple and UpdateMultiple using the below given query. Basically, if a table can CreateMultiple and UpdateMultiple, it can also do UpsertMultiple.

/// <summary>
/// Detect whether a specified message is supported for the specified table.
/// </summary>
/// <param name="service">The IOrganizationService instance.</param>
/// <param name="entityLogicalName">The logical name of the table.</param>
/// <param name="messageName">The name of the message.</param>
/// <returns></returns>
public static bool IsMessageAvailable(
    IOrganizationService service,
    string entityLogicalName,
    string messageName)
{
    QueryExpression query = new("sdkmessagefilter")
    {
        ColumnSet = new ColumnSet("sdkmessagefilterid"),
        Criteria = new FilterExpression(LogicalOperator.And)
        {
            Conditions = {
            new ConditionExpression(
                attributeName:"primaryobjecttypecode",
                conditionOperator: ConditionOperator.Equal,
                value: entityLogicalName)
            }
        },
        LinkEntities = {
            new LinkEntity(
                linkFromEntityName:"sdkmessagefilter",
                linkToEntityName:"sdkmessage",
                linkFromAttributeName:"sdkmessageid",
                linkToAttributeName:"sdkmessageid",
                joinOperator: JoinOperator.Inner)
            {
                    LinkCriteria = new FilterExpression(LogicalOperator.And){
                    Conditions = {
                        new ConditionExpression(
                            attributeName:"name",
                            conditionOperator: ConditionOperator.Equal,
                            value: messageName)
                        }
                    }
            }
        }
    };

    EntityCollection entityCollection = service.RetrieveMultiple(query);

    return entityCollection.Entities.Count.Equals(1);
}

SDK for .NET Sample code (UpsertMultiple Request):

The below SDK for .NET Console Application code demonstrates the usage of UpsertMultiple request. Here the method CRMCrateUpserMultiple depends on a crmcrate_bankaccount table that has a string column named crmcrate_accountname configured as an alternate key. It also has a string column named crmcrate_description. This code utilizes the Entity constructor, which establishes the keyName and keyValue to indicate an alternative key value.

/// <summary>
/// Demonstrates using UpsertMultiple request with alternate key values
/// </summary>
/// <param name="service">The authenticated IOrganizationService instance</param>
static void CRMCrateUpserMultiple (IOrganizationService service)
{
    var tableLogicalName = "crmcrate_bankaccount";
    //crmcrate_accountname string column is configued as an alternate key
    // for thecrmcrate_bankaccount table
    var altKeyColumnLogicalName = "crmcrate_accountname";

    // Create one record to update with upsert
    service.Create(new Entity(tableLogicalName)
    {
        Attributes =
        {
            {altKeyColumnLogicalName, "Record For Update"},
            {"crmcrate_description","A record to update using Upsert" }
        }
    });

    // Using the Entity constructor to specify alternate key
    Entity toUpdate = new(
            entityName: tableLogicalName,
            keyName: altKeyColumnLogicalName,
            // Same alternate key value as created record.
            keyValue: "Record For Update");
    toUpdate["crmcrate_description"] = "Updated using Upsert";

    Entity toCreate = new(
        entityName: tableLogicalName,
        keyName: altKeyColumnLogicalName,
        keyValue: "Record For Create");
    toCreate["crmcrate_description"] = "A record to create using Upsert";

    // Add the records to a collection
    EntityCollection records = new()
    {
        EntityName = tableLogicalName,
        Entities = { toUpdate, toCreate }
    };

    // Send the request
    UpsertMultipleRequest request = new()
    {
        Targets = records
    };

    var response = (UpsertMultipleResponse)service.Execute(request);

    // Process the responses:
    foreach (UpsertResponse item in response.Results)
    {
        Console.WriteLine($"Record {(item.RecordCreated ? "Created" : "Updated")}");
    }
}

Output of the above code:

Record Updated
Record Created

In conclusion, integrating Upsert requests into Power Apps can significantly enhance data management and streamline workflows. The ability to update existing records and insert new ones in a single operation not only saves time but also ensures data accuracy and consistency. By leveraging Upsert requests effectively, Power Apps users can optimize their applications for better performance and user experience, ultimately driving productivity and success in their projects.

---

• LinkedIn
• Facebook
• Telegram