Upsert as explained by "Upsert"

Let’s talk about Upsert - not to be confused with our company of the same name. Upsert API endpoints are an incredible time saver. Have your integrations become cumbersome? Much like that 90’s band Seven Mary Three, we want to avoid this scenario from happening whenever possible. Luckily for you, Sugar 10.2.0 supports upserting!

What Is an Upsert?

For those unfamiliar with the term, upsert is a concept that unifies two common operations:

  1. Update a record if it exists, or if it does not exist, then…
  2. Insert a new record

How Does This Help Me?

Let’s break down the flow that integrations had to use in the past:

  1. Use GET /<module>/filter to find a specific record by an integration ID
  2. If not found, construct a POST /<module>/ request to create a new record.
  3. If found, construct a PUT /<module>/<id> request to update the record.

Using the new APIs, you can now create or update your records with a single request!

This helps your integrations by having:

  • Fewer API requests to verify whether records exist or not
  • Less developer coding as you no longer have to check endpoint responses to determine whether records exist
  • Less processing on your servers
  • Faster performance with your integrations
  • Record insertions without the risk of creating duplicates (if the create operation is re-run via retry logic or by accident)

How Do I Use The New Endpoints?

Funny you should ask; we have that all right here! Before we begin, it’s best that you are familiar with SugarCRM’s REST API, as you will need to be authenticated with an access token.

Populating a Record’s Sync Key

The integrate endpoints are, by default, based on the sync_key field that you will find in your modules starting in 10.2.0. This field is used to store an external ID for records that you are syncing from another application. It is important to note that this field will need to be populated before you can use the other upsert endpoints. If you would prefer to use a different field as the sync key, we have endpoints for that as well. In fact, the examples in this document are using those. For more information on the the endpoints that use the default sync key, see this document

Please note that if you installed the Sugar Essentials package prior to 10.2.0, you will need to uninstall it. The Sugar Essentials package was originally created in order to add upsert endpoints. Now that they have been added to the core platform code, this package is no longer necessary. In fact, during an upgrade, package scanner will return an error if the package is installed.

To populate the sync field for a record, you can use the PATCH /integrate/<module>/:sync_key_field_name/:sync_key_field_value endpoint:

Request:

PATCH /integrate/Accounts/customfield_c/1234

Response:

{
    "id": "3b645ae2-1fd0-11eb-87c8-063b0cd98a86"
}

Fetching a Record

To fetch a record by its sync field, you can use the GET /Integrate/<module>/:sync_key_field_name/:sync_key_field_value endpoint:

Request:

GET /integrate/<module>/sync_key/1234

Response:

{
    ...
    "id": "3b645ae2-1fd0-11eb-87c8-063b0cd98a86",
    "sync_key": "1234",
    "_acl": {
        "fields": {}
    },
    "_module": "Accounts"
}

Upserting a Record

To create or update a record, you can use the PATCH /Integrate/<module>/:sync_key_field_name/:sync_key_field_value endpoint:

Request:

PATCH /integrate/Accounts/customfield_c/1234?name=SugarCRM

Response:

{
    "id": "3b645ae2-1fd0-11eb-87c8-063b0cd98a86",
}

Deleting a Record

To delete a record, you can use the DELETE /Integrate/<module>/:sync_key_field_name/:sync_key_field_value endpoint:

Request:

DELETE /integrate/Accounts/customfield_c/1234

Response:

{
    "id": "3b645ae2-1fd0-11eb-87c8-063b0cd98a86",
}

Was this "…too heavy, too light? too black or too white?" Tired of reading and just want something that works? Download the postman environment and collection.

Parents
  • Great post for our integration enthusiasts!

    A few clarifications/additions to add a little more colour:

    • sync_key values have to be unique on the same object/module, an exception will be thrown otherwise
    • there is an endpoint specifically used to set a sync_key for an existing record that can be used as well, alternatively for new records, the sync_key will be set automatically 
    • if there is the need to add a new sync_key type field (to deal with multiple integrations on the same module), make sure you follow the structure of the matching vardefs (include/SugarObjects/implements/integrate_fields/vardefs.php), also make sure the field is on the main module's table and has an index/unique clause for performance reasons
    • last but not least, for integrations you can wrap all the upsert calls into a bulk single http call, to improve further the throughput. Given that there is no need to know the "previous record's id" as the integration uses the external records identifiers to identify a record, this can be done easily for a number of operations on the same bulk api call!

    Hope it helps!

Comment
  • Great post for our integration enthusiasts!

    A few clarifications/additions to add a little more colour:

    • sync_key values have to be unique on the same object/module, an exception will be thrown otherwise
    • there is an endpoint specifically used to set a sync_key for an existing record that can be used as well, alternatively for new records, the sync_key will be set automatically 
    • if there is the need to add a new sync_key type field (to deal with multiple integrations on the same module), make sure you follow the structure of the matching vardefs (include/SugarObjects/implements/integrate_fields/vardefs.php), also make sure the field is on the main module's table and has an index/unique clause for performance reasons
    • last but not least, for integrations you can wrap all the upsert calls into a bulk single http call, to improve further the throughput. Given that there is no need to know the "previous record's id" as the integration uses the external records identifiers to identify a record, this can be done easily for a number of operations on the same bulk api call!

    Hope it helps!

Children
No Data