Upserts and external IDs

Overview

An integration that creates records blindly will eventually create them twice - a retried request, a replayed message or a second run of the same sync is all it takes. The cure is the upsert: one call that creates the record when it is missing and updates it when it exists, keyed by an identifier that your side of the integration owns.

That identifier lives in an external ID field - a custom field on the Salesforce object, marked as "External ID" in its setup, holding your system's key: an ERP order number, a CRM code, a partner identifier. With one in place, your integration never needs to remember Salesforce record IDs at all.

Upserting a record

An upsert is a PATCH on the path that names the external ID field and its value - not the record ID:

# -*- coding: utf-8 -*-

# Zato
from zato.server.service import Service

class UpsertCampaign(Service):
    name = 'crm.upsert-campaign'

    input = 'campaign_code', 'name', 'segment'

    def handle(self):

        campaign_code = self.request.input.campaign_code

        conn = self.salesforce['My Salesforce Connection']

        # The fields to create the record with or to update it to - the external ID
        # itself is in the path, not in the body ..
        record = {
            'Name': self.request.input.name,
            'Segment__c': self.request.input.segment,
        }

        # .. one call, whether the record exists or not.
        path = f'/sobjects/Campaign/Campaign_Code__c/{campaign_code}'
        response = conn.patch(path, record)

        # A create answers with the new record's details, an update with an empty response.
        if response:
            campaign_id = response['id']
            self.logger.info('Created campaign %s', campaign_id)
        else:
            self.logger.info('Updated campaign %s', campaign_code)

The two outcomes are distinguishable by the response. A create returns HTTP 201 with the new record:

{"id": "701RO00000BpkfMYAR", "success": true, "errors": [], "created": true}

An update returns HTTP 204 with no content, which conn.patch hands back as an empty dict.

Run the service twice with the same campaign_code and the second run updates the record the first one created - no duplicate, no lookup, no branching logic on your side. This is what makes upserts the right primitive for syncs and message consumers, where processing the same input twice must be harmless.

Referencing a parent by external ID

Salesforce objects link to each other through parent-child relationships, and creating a child normally requires the parent's record ID. External IDs remove that lookup too - reference the parent through its relationship field, passing a nested object with the parent's external ID:

# -*- coding: utf-8 -*-

# Zato
from zato.server.service import Service

class UpsertComponent(Service):
    name = 'assets.upsert-component'

    input = 'serial_number', 'name', 'plane_code'

    def handle(self):

        serial_number = self.request.input.serial_number
        plane_code = self.request.input.plane_code

        conn = self.salesforce['My Salesforce Connection']

        # The parent is referenced by its own external ID - no record ID lookup anywhere ..
        record = {
            'Name': self.request.input.name,
            'Plane__r': {
                'Plane_Code__c': plane_code,
            },
        }

        # .. and the child itself is upserted by its serial number.
        path = f'/sobjects/Component__c/Serial_Number__c/{serial_number}'
        _ = conn.patch(path, record)

Plane__r is the relationship field of the custom Plane__c object - custom relationships use the __r suffix, standard ones use the object name, e.g. Account for a Contact's parent. The nested object names the parent's external ID field and value, and Salesforce resolves it to the right record server-side.

The parent must already exist - referencing a missing parent fails with an error explaining that no matching record was found, in the error array format all failures use.

When not to upsert

Upserts shine when your side owns the key. When Salesforce owns the record and you only ever modify it - a status field updated by a workflow, a counter maintained by a sync - a plain update by record ID says what it means and cannot accidentally create a half-empty record from a typo in the key.

Learn more