New CRM SDK Feature - Upsert

Upsert is a term that's often seen in a DBA's textbook, but not in a CRM developer's. Upsert stands for Update/Insert, it indicates that it could take either an Update or an Insert action depending on whether a match can be found when writing to the target system.

At KingswaySoft, we have offered Upsert functionality since our very first release of SSIS Integration Toolkit product, which has proved to be a very useful feature, as it significantly simplifies data integration development. Many of our clients rely on this robust feature to implement their ongoing data synchronization from external systems to CRM. Upsert was not a platform feature at the time, so we had to implement it using our own approach, which is to first make a CRM query service call to figure out if the incoming row is an existing record in CRM system or a new one before performing the write action (Create if not exists, otherwise update). This has worked really well, however it does come with an upfront cost, which is the query that involves a round-trip service call to the CRM server.

With the introduction of Upsert support to the new CRM release, it makes the platform even better.

Prerequisites

To use the Upsert capability, you need to:

• Have a CRM 2015 Update 1 (v7.1) instance (CRM Online only at this moment) in order to utilize this new feature.
• Use CRM SDK 7.1.0-preview to compile the code.
• Create the Alternate Keys in CRM system using its customization tool or SDK (please refer to my other blog post for more detailed instructions on how to create Alternate Keys).

Code in Action

The following code snippet shows how to make a CRM Upsert service call using its UpsertRequest message.

using (var service = new OrganizationService(crmConnection))
{
    // Use alternate key (accountnumber) field to identify an account record
    var account = new Entity("account")
    {
        KeyAttributes = new KeyAttributeCollection
        {
            {"accountnumber", "ACT-12345" }
        }
    };

    account["name"] = "Microsoft";
    account["creditlimit"] = new Money(100000);

    var request = new UpsertRequest() { Target = account };
    var response = (UpsertResponse)service.Execute(request);
}

When you run the code, CRM server will first check if there is an account record that has an "ACT-12345" accountnumber. If it finds such a record, it will use the latest incoming values to update the record, or otherwise it will create a CRM record using the values provided.

Note that there is no Upsert plugin message for CRM Upsert requests, it will either be Create or Update message depending on which action is going to be taken.

References

For more details about CRM Upsert capability, please check the following links the CRM SDK documentation team has provided.

• Use Upsert to insert or update a record
• Sample code on code.msdn.microsoft.com: Insert or update a record using Upsert

Hope this helps.

Read the full New SDK Capabilities Blog Series:

• Part 1 - Alternate Keys
• Part 2 - Upsert
• Part 3 - No more special messages for special fields 
• Part 4 - Plugin Trace Logging 
• Part 5 - Entity Change Tracking 
• Part 6 - Transactional Batching 
• Part 7 - Optimistic Concurrency 
• Part 8 - New Query Operators
• Part 9 - Detect SDK Capabilities by Checking CRM Server Version