Upserting records

Using upsert to perform GraphQL insert or update operations.

In addition to insert, update, and delete, Skedulo provides upsert<Schema> methods for standalone tenants for inserting or updating existing records in a single GraphQL operation.

An upsert uses a unique ID field to assess whether or not an object already exists, then either updates the record or creates a new object if it does not already exist.

An upsert operation requires the same input as insert operations, as well as the name of the field being upserted.

For Skedulo, the field being upserted must be unique. Creating a unique field using the Skedulo Lens API /custom/standalone/fields endpoint automatically marks the field as valid for upsert operations.

See the Skedulo API Reference Guide for information about using the API to create custom objects and fields.

Create a custom field in Skedulo

Create a custom standalone object with a unique field and track it using the Skedulo API:

  1. Make a request to the /custom/standalone/schemas endpoint to create a custom external object with an external ID field:

    {
      "name": "CustomExternalObject",
      "label": "Custom External Object",
      "fields": [
        {
          "name": "Name",
          "column": {
            "type": "string"
          }
        },
        {
          "name": "ExternalId",
          "column": {
            "type": "string",
            "unique": true
          }
        }
      ]
    }

    This returns the following information, including IDs for the object and related fields. Note that the ExternalId field includes the "upsertKey": true setting, indicating that it can be used for upsert operations:

    {
      "result": {
        "schema": {
          "name": "CustomExternalObject",
          "description": null,
          "label": "Custom External Object",
          "mapping": "__customexternalobject",
          "id": "9c07a34c-7b08-4bfc-a1fb-5b108f6e1cf0"
        },
        "fields": [
          {
            "id": "68ef7fe1-9ce2-429a-bfbc-997cdc494787",
            "name": "UID",
            "schemaName": "CustomExternalObject",
            "label": "UID",
            "description": null,
            "fieldType": "id",
            "mapping": "uid",
            "referenceSchemaName": null,
            "referenceSchemaFieldName": null,
            "required": true,
            "upsertKey": true,
            "accessMode": "read_only",
            "readOnly": false,
            "maxLength": null,
            "precision": null,
            "scale": null
          },
          {
            "id": "701cfcaa-c277-420f-8bfd-c4b4c18f6b5f",
            "name": "CreatedDate",
            "schemaName": "CustomExternalObject",
            "label": "Created Date",
            "description": null,
            "fieldType": "datetime",
            "mapping": "created_date",
            "referenceSchemaName": null,
            "referenceSchemaFieldName": null,
            "required": true,
            "upsertKey": false,
            "accessMode": "read_only",
            "readOnly": false,
            "maxLength": null,
            "precision": null,
            "scale": null
          },
          {
            "id": "96a69056-a923-4f3e-819f-cc09aadb074b",
            "name": "CreatedBy",
            "schemaName": "CustomExternalObject",
            "label": "Created By",
            "description": null,
            "fieldType": "reference",
            "mapping": "created_by",
            "referenceSchemaName": "Users",
            "referenceSchemaFieldName": null,
            "required": true,
            "upsertKey": false,
            "accessMode": "read_only",
            "readOnly": false,
            "maxLength": null,
            "precision": null,
            "scale": null
          },
          {
            "id": "0420b6d0-e062-43f4-a835-128c4b1f7e2e",
            "name": "LastModifiedDate",
            "schemaName": "CustomExternalObject",
            "label": "Last Modified Date",
            "description": null,
            "fieldType": "datetime",
            "mapping": "last_modified_date",
            "referenceSchemaName": null,
            "referenceSchemaFieldName": null,
            "required": true,
            "upsertKey": false,
            "accessMode": "read_only",
            "readOnly": false,
            "maxLength": null,
            "precision": null,
            "scale": null
          },
          {
            "id": "c741a3a7-ac0e-44ff-9082-f362d6ab94ae",
            "name": "LastModifiedBy",
            "schemaName": "CustomExternalObject",
            "label": "Last Modified By",
            "description": null,
            "fieldType": "reference",
            "mapping": "last_modified_by",
            "referenceSchemaName": "Users",
            "referenceSchemaFieldName": null,
            "required": true,
            "upsertKey": false,
            "accessMode": "read_only",
            "readOnly": false,
            "maxLength": null,
            "precision": null,
            "scale": null
          },
          {
            "id": "49c4a0e8-f177-482f-a795-cfa561a7a4e5",
            "name": "Name",
            "schemaName": "CustomExternalObject",
            "label": null,
            "description": null,
            "fieldType": "string",
            "mapping": "__name",
            "referenceSchemaName": null,
            "referenceSchemaFieldName": null,
            "required": false,
            "upsertKey": false,
            "accessMode": "read_write",
            "readOnly": false,
            "maxLength": 255,
            "precision": null,
            "scale": null
          },
          {
            "id": "ae9d248d-12be-483c-a14a-44816b894976",
            "name": "ExternalId",
            "schemaName": "CustomExternalObject",
            "label": null,
            "description": null,
            "fieldType": "string",
            "mapping": "__externalid",
            "referenceSchemaName": null,
            "referenceSchemaFieldName": null,
            "required": false,
            "upsertKey": true,
            "accessMode": "read_write",
            "readOnly": false,
            "maxLength": 255,
            "precision": null,
            "scale": null
          }
        ]
      }
    }
  2. Track the new custom object by its id returned above. This maps the custom field to the object:

    curl -X POST -H "Authorization: Bearer $AUTH_TOKEN" "https://api.skedulo.com/custom/standalone/track/9c07a34c-7b08-4bfc-a1fb-5b108f6e1cf0"

    3. (Optional) Add a webhook for the custom object. A webhook allows you to observe changes to field values. A webhook is not a requirement for using upserts, one is being used here for demonstration purposes only.

    3a. Start a HTTP server and connect to it (this example uses ngrok). See the Webhooks chapter for instructions.

    3b. Create the following file called webhook.js to track the custom object and its custom fields:

    const url = "https://c3f43fbb.ngrok.io"
    
    const json = {
      name: "Upsert custom object",
      url: url,
      type: "graphql",
      query: `
        subscription {
          schemaCustomExternalObject {
            operation
            timestamp
            data {
              UID
              Name
              ExternalId
              CreatedDate 
              LastModifiedDate
            }
            previous {
              UID 
              Name 
              ExternalId 
              CreatedDate
              LastModifiedDate
            }
          }
        }
      `
    }
    
    console.log(JSON.stringify(json, null, 2))
    

    3c. Convert the webhook.js file to JSON:

    $ node webhook.js > temp.json 

    3d. Create a webhook using the following request:

    curl -s -X POST -H "Authorization: Bearer $AUTH_TOKEN"  -H "Content-Type: application/json" -d @temp.json 'https://api.skedulo.com/webhooks' | jq

    For more information about creating webhooks, see the Webhooks chapter.

Insert a new object using upsert

Use GraphQL to insert a new CustomExternalObject. The ExternalId field value can be any value, and can be used to add an ID from your own environment that relates to a record in another system.

For example, if you have a case number associated with a client in another system, such as an ordering system or healthcare provider, you can use this identifier in this field when creating a record for the same client.

mutation upsertNewClient {
	schema {
		insertCustomExternalObject(input: {
			Name: "New Upsert"
			ExternalId: "ClientCaseNumber_ID01"
		})
	}
}

The webhook response shows that a new object was created with ClientCaseNumber_ID01 as the ExternalId value:

Webhook response

{
  "headers": {
    ...
  },
  "body": [
    {
      "data": {
        "schemaCustomExternalObject": {
          "data": {
            "UID": "03ec7f8a-8ac0-4fb7-86c6-5759884721dd",
            "Name": "New Upsert",
            "ExternalId": "ClientCaseNumber_ID01",
            "CreatedDate": "2019-08-07T06:10:05.426Z",
            "LastModifiedDate": "2019-08-07T06:10:05.426Z"
          },
          "previous": {
            "UID": "03ec7f8a-8ac0-4fb7-86c6-5759884721dd",
            "Name": "New Upsert",
            "ExternalId": "ClientCaseNumber_ID01",
            "CreatedDate": "2019-08-07T06:10:05.426Z",
            "LastModifiedDate": "2019-08-07T06:10:05.426Z"
          },
          "operation": "INSERT",
          "timestamp": "2019-08-07T06:10:05.426Z"
        }
      }
    }
  ]
}

Note that both the data and previous blocks are identical, as no changes or modifications have been made to the object yet

Use upsert to update an existing object or create a new one

The following upsert GraphQL mutation is the same as the insert operation in the previous section however, rather than create a new object, the upsert will search for an existing object based on the value in the ExternalId key field, and update the Name field:

mutation upsertNewClientUpsert {
	schema {
		upsertCustomExternalObject(input: {
			Name: "Client Case Test"
			ExternalId: "ClientCaseNumber_ID01"
		}, keyField: "ExternalId")
	}
}

The first part of the webhook response shows the Name field has been updated while the second part shows the previous values:

Webhook response

{
  "headers": {
    ...
  },
  "body": [
    {
      "data": {
        "schemaCustomExternalObject": {
          "data": {
            "UID": "03ec7f8a-8ac0-4fb7-86c6-5759884721dd",
            "Name": "Client Case Test",
            "ExternalId": "ClientCaseNumber_ID01",
            "CreatedDate": "2019-08-07T06:10:05.426Z",
            "LastModifiedDate": "2019-08-07T06:16:39.861Z"
          },
          "previous": {
            "UID": "03ec7f8a-8ac0-4fb7-86c6-5759884721dd",
            "Name": "New Upsert",
            "ExternalId": "ClientCaseNumber_ID01",
            "CreatedDate": "2019-08-07T06:10:05.426Z",
            "LastModifiedDate": "2019-08-07T06:10:05.426Z"
          },
          "operation": "UPDATE",
          "timestamp": "2019-08-07T06:16:39.861Z"
        }
      }
    }
  ]
}

Now run the same query as above, but change the ExternalId field value to ClientCaseNumber_ID02:

mutation upsertNewClientUpsert {
	schema {
		upsertCustomExternalObject(input: {
			Name: "Client Case Test"
			ExternalId: "ClientCaseNumber_ID02"
		}, keyField: "ExternalId")
	}
}

You can see from the webhook output, or from performing a GraphQL query that this has created a new CustomExternalObject object with Name ("Client Case Test"), but with the new ExternalId value:

Webhook output

{
  "headers": {
    ...
  },
  "body": [
    {
      "data": {
        "schemaCustomExternalObject": {
          "data": {
            "UID": "03ecfbba-98de-4ef7-bf87-b3906fd7b302",
            "Name": "Client Case Test",
            "ExternalId": "ClientCaseNumber_ID02",
            "CreatedDate": "2019-08-07T06:38:42.644Z",
            "LastModifiedDate": "2019-08-07T06:38:42.644Z"
          },
          "previous": {
            "UID": "03ecfbba-98de-4ef7-bf87-b3906fd7b302",
            "Name": "Client Case Test",
            "ExternalId": "ClientCaseNumber_ID02",
            "CreatedDate": "2019-08-07T06:38:42.644Z",
            "LastModifiedDate": "2019-08-07T06:38:42.644Z"
          },
          "operation": "INSERT",
          "timestamp": "2019-08-07T06:38:42.644Z"
        }
      }
    }
  ]
}

The following GraphQL query on the Name field that we used for both objects returns two objects and two insert events:

query upsertClientQuery {
	customExternalObject(filter: "Name == \"Client Case Test\"") {
		edges {
			node {
				UID
				Name
				ExternalId
				CreatedDate
				LastModifiedDate
			}
		}
	}
}

Returns:

{
  "data": {
    "customExternalObject": {
      "edges": [
        {
          "node": {
            "UID": "03ec7f8a-8ac0-4fb7-86c6-5759884721dd",
            "Name": "Client Case Test",
            "ExternalId": "ClientCaseNumber_ID01",
            "CreatedDate": "2019-08-07T06:10:05.426Z",
            "LastModifiedDate": "2019-08-07T06:16:39.861Z"
          }
        },
        {
          "node": {
            "UID": "03ecfbba-98de-4ef7-bf87-b3906fd7b302",
            "Name": "Client Case Test",
            "ExternalId": "ClientCaseNumber_ID02",
            "CreatedDate": "2019-08-07T06:38:42.644Z",
            "LastModifiedDate": "2019-08-07T06:38:42.644Z"
          }
        }
      ]
    }
  }
}

Use idAlias to make multiple changes using upserts

You can use an upsert mutation to make changes to multiple objects in a single query using aliases.

mutation upsertWithAliases {
  schema {
    id1: upsertCustomExternalObject(input: {
      Name: "Alias test 01"
      ExternalId: "ClientCaseNumber_ID01"
      }, keyField: "ExternalId", idAlias: "ID01")
    id2: upsertCustomExternalObject(input: {
      Name: "Alias test 02"
      ExternalId: "ClientCaseNumber_ID02"
      }, keyField: "ExternalId", idAlias: "ID02")
    contactId1: insertContacts(input: {
      LastName: "Smith"
      CustomExternalObjId: "ID01"
      })
    contactId2: insertContacts(input: {
      LastName: "Jones"
      CustomExternalObjId: "ID02"
      })
    }
  }

The webhook response shows that both objects were updated successfully:

Webhook response

{
  "headers": {
    ...
  },
  "body": [
    {
      "data": {
        "schemaCustomExternalObject": {
          "data": {
            "UID": "03ec7f8a-8ac0-4fb7-86c6-5759884721dd",
            "Name": "Alias test 01",
            "ExternalId": "ClientCaseNumber_ID01",
            "CreatedDate": "2019-08-07T06:10:05.426Z",
            "LastModifiedDate": "2019-08-07T07:14:10.493Z"
          },
          "previous": {
            "UID": "03ec7f8a-8ac0-4fb7-86c6-5759884721dd",
            "Name": "Alias test 01",
            "ExternalId": "ClientCaseNumber_ID01",
            "CreatedDate": "2019-08-07T06:10:05.426Z",
            "LastModifiedDate": "2019-08-07T07:03:32.704Z"
          },
          "operation": "UPDATE",
          "timestamp": "2019-08-07T07:14:10.493Z"
        }
      }
    },
    {
      "data": {
        "schemaCustomExternalObject": {
          "data": {
            "UID": "03ecfbba-98de-4ef7-bf87-b3906fd7b302",
            "Name": "Alias test 02",
            "ExternalId": "ClientCaseNumber_ID02",
            "CreatedDate": "2019-08-07T06:38:42.644Z",
            "LastModifiedDate": "2019-08-07T07:14:10.493Z"
          },
          "previous": {
            "UID": "03ecfbba-98de-4ef7-bf87-b3906fd7b302",
            "Name": "Alias test 02",
            "ExternalId": "ClientCaseNumber_ID02",
            "CreatedDate": "2019-08-07T06:38:42.644Z",
            "LastModifiedDate": "2019-08-07T07:03:32.704Z"
          },
          "operation": "UPDATE",
          "timestamp": "2019-08-07T07:14:10.493Z"
        }
      }
    }
  ]
}

Run the following query that uses an EQL filter to return entries based on the LastName field values Smith and Jones that we just upserted to the two client cases:

query upsertClientQuery {
  contacts (filter: "LastName == \"Smith\" OR LastName == \"Jones\"" ){
    edges {
      node {
        UID
        CustomExternalObj {
          ExternalId 
        }
        CreatedDate
        LastModifiedDate
        LastName
      }
    }
  }
}

This returns the objects with the information we requested, and validates that the LastName fields have been updated successfully:

{
  "data": {
    "contacts": {
      "edges": [
        {
          "node": {
            "UID": "0004e60e-6ec5-4872-91ad-9f11db1ed21d",
            "CustomExternalObj": {
              "ExternalId": "ClientCaseNumber_ID01"
            },
            "CreatedDate": "2019-08-07T07:03:32.704Z",
            "LastModifiedDate": "2019-08-07T07:03:32.704Z",
            "LastName": "Smith"
          }
        },
        {
          "node": {
            "UID": "00049259-395e-41ea-b685-acbf3f54540a",
            "CustomExternalObj": {
              "ExternalId": "ClientCaseNumber_ID02"
            },
            "CreatedDate": "2019-08-07T07:03:32.704Z",
            "LastModifiedDate": "2019-08-07T07:03:32.704Z",
            "LastName": "Jones"
          }
        },
      ]
    }
  }
}

For more information about using aliases for GraphQL mutations and queries, see the GraphQL Aliases chapter.