UPSERT Endpoint (POST)

UPSERT Endpoint(POST)

Initial Preparation

The Endpoint is accessed through the url {{baseURL }}/CSSAPI/V2/{{entityName}} /Upsert

• baseURL – your company’s ContractInsight URL
• entityName – name of the database table 

The HTTP request type is POST. Content type for this request is set to application/json. 

Introduction

UPSERT Endpoint allows you to add/update records in Contract Insight database. UPSERT first attempts to update records. If there is nothing to update, a new record is created or the target entity. The body of UPSERT request is identical to one used for UPDATE request with the exception of having additional attribute – AllowMultiUpdate. AllowMultiUpdate determines whether to perform update when multiple entries are found based on request parameters or only allow single-record update.

Although UPSERT request format is almost the same as one for UPDATE, we need to keep in mind that UPSERT checks to see if records exist for update. If there are no records found, INSERT is executed. Therefore, we must include all attributes for the record in Tuples collection of the request. In other words, we must construct our UPSERT request as if it is an INSERT to begin with. Otherwise, we might end up with a record with missing values for some columns in the target entity in the case of INSERT. Things to keep in mind when making calls to UPDATE Endpoint:

1. We can only perform a single table update per request. The total number of updated records cannot exceed 500.
2. When AllowMultiUpdate is set to false and REST API finds more than one record for the request, operation will be cancelled, and an error will be returned. AllowMultiUpdate has default value of false in REST API.
3. When no records are found for the request, UPSERT endpoint will perform INSERT operation based on values supplied in Tuples collection. Therefore, it is necessary to include all column names and their values in the UPSERT request.
4. There are two types of responses. One set of responses is the same as the one for UPDATE endpoint. And the other set is the same as the one for INSERT endpoint.
5. Fields that cannot be updated include fields of type GUID (uniqueidentifier), password field, out-of-the-box date fields such as DateEntered, EntryDate, DateCreated, CreatedDate.

Request format

The following format must be used in the request body when making calls to UPSERT Endpoint.

Response

Since UPSERT is a combination of UPDATE and INSERT, successful and error responses are the same as for those two endpoints. Please refer to sections on UPDATE and INSERT for more information.

One new error response that a client will encounter is when AllowMultiUpdate attribute is set to false, and the number of records to-be-updated is greater than 1. 

The error means that five records were returned based on the Clause portion of a request. Since AllowMultiUpdate is false, no records were updated.

UPSERT Endpoint in Action (Postman Examples)

Task 1: Regular Update operation

In the example below we are updating records returned based on the Where clause of our request. There happens to be 5 records that satisfied the criteria. We set AllowMultiUpdate flag to “true”. As a result, UPSERT endpoint executed update operation and returned UPDATE response.

{   

    "Tuples":[

      {

         "Name":"ActiveYN",

         "Value":"false"

      },

      {

          "Name": "Street2",

          "Value": "456 street"

      }, 

            {

          "Name": "VendorName",

          "Value": "Some Vendor"

      }

   

   

   ],

"Clause":{

      "Condition":"AND",

      "Field":null,

      "Id":null,

      "Input":null,

      "Operator":null,

      "Rules":[

         {

            "Condition":null,

            "Field":"VendorName",

            "Id":"VendorName",

            "Input":null,

            "Operator":"equal",

            "Rules":null,

            "Type":"int",

            "Value":"Some Vendor"

         }

      ],

      "Type":null,

      "Value":null

   },

   "TriggerWorkflow":false,

   "AllowMultiUpdate": true

UPDATE tblCustomers SET VendorName = 'Some Vendor', ActiveYN = 0, Street2 = '456 street'

WHERE VendorName = 'Some Vendor'

{

    "TotalAffected": 5,

    "TotalFailed": 0,

    "Message": null

}

 

Task 2: Multiple Updates were set to be disallowed.

In this example, we send the same request as in previous example, except this time, we set AllowMultiUpdate flag to false. An error will be returned.

{   

    "Tuples":[

      {

         "Name":"ActiveYN",

         "Value":"false"

      },

      {

          "Name": "Street2",

          "Value": "456 street"

      }, 

            {

          "Name": "VendorName",

          "Value": "Some Vendor"

      }

   

   

   ],

"Clause":{

      "Condition":"AND",

      "Field":null,

      "Id":null,

      "Input":null,

      "Operator":null,

      "Rules":[

         {

            "Condition":null,

            "Field":"VendorName",

            "Id":"VendorName",

            "Input":null,

            "Operator":"equal",

            "Rules":null,

            "Type":"int",

            "Value":"Some Vendor"

         }

      ],

      "Type":null,

      "Value":null

   },

   "TriggerWorkflow":false,

   "AllowMultiUpdate": false

UPDATE tblCustomers SET VendorName = 'Some Vendor', ActiveYN = 0, Street2 = '456 street'

WHERE VendorName = 'Some Vendor'

{

    "LogId": "39e6367b-2902-43c0-9095-3569cb2215fb",

    "Message": "Number of total records found: 5. Multiple update flag was set to false.",

    "Exception": null

}

Task 3 Insert example

In this example we continue working on Customers table. This time, we keep the same request structure. AllowMultiUpdate will be set to false, but we change our where clause so that REST API gets no records found in the database (we set vendor name to a non-existing vendor name). This will automatically trigger INSERT action. And the response returned will be of INSERT type of response.

{   

    "Tuples":[

      {

         "Name":"ActiveYN",

         "Value":"false"

      },

      {

          "Name": "Street2",

          "Value": "456 street"

      }, 

            {

          "Name": "VendorName",

          "Value": "Some VendorTEST"

      }

   

   

   ],

 

"Clause":{

      "Condition":"AND",

      "Field":null,

      "Id":null,

      "Input":null,

      "Operator":null,

      "Rules":[

         {

            "Condition":null,

            "Field":"VendorName",

            "Id":"VendorName",

            "Input":null,

            "Operator":"equal",

            "Rules":null,

            "Type":"int",

            "Value":"Some Vendor 1"

         }

      ],

      "Type":null,

      "Value":null

   },

   "TriggerWorkflow":false,

   "AllowMultiUpdate": false

--This will be run first. Since no records were found that match the criteria

--UPDATE will not happen

UPDATE tblCustomers SET VendorName = 'Some Vendor', ActiveYN = 0, Street2 = '456 street'

WHERE VendorName = 'Some VendorTEST'

 

--This is the next step: INSERT

INSERT INTO tblCustomers(VendorName, ActiveYN, Street2) VALUES ('Some Vendor', 0, '456 street')

{

    "GeneratedId": 3038,

    "Message": "Success"

}

Partial snapshot of the database record created as a result of the previous request:

As it could be seen from the picture above, some of the columns have NULL values. That is because only those values supplied in Tuples collection are used in the INSERT operation.