Kintone API - On duplicate key update

1

I am trying to sync data from another application to Kintone. I’m using the Kintone API and testing with cURL. I have data imported into Kintone already, and I am doing a full sync from our other data set to update the Kintone App.

How can I do an update similar to an SQL insert query that uses ‘on duplicate key update…’?

When I run this command (found in the “Update Records” API documentation) on a record that does not yet exist:

curl -X PUT "https://{subdomain}.kintone.com/k/v1/records.json" \
   -H "Content-Type: application/json"
   -H "X-Cybozu-Authorization: {authtoken}"
   -d '{
        "app": 2,
        "records": [{
            "updateKey": {
            "field": "company_id",
            "value": 20859
            },
            "record": {
            "name": {
                "value": "SBS"
            },
            "short_name": {
                "value": "sbs"
            },
            "city": {
                "value": "Colorado Springs"
            },
            "state": {
                "value": "CO"
            },
            "zip": {
                "value": ""
            },
            "phone": {
                "value": "1234567890"
            },
            "website": {
                "value": "https://www.testcompany.com"
            },
            "type": {
                "value": "vendor"
            }
            }
        }]
    }'

I get this error:

{
  "code": "GAIA_RE20",
  "id": "...",
  "message": "No records match the conditions specified in the \"updateKey\" field."
}

When I run the alternate command (found in the “Add Records” API documentation), on a record that exists, I get this error:

{
  "code": "CB_VA01",
  "id": "...",
  "message": "Missing or invalid input.",
  "errors": {
    "records[0].company_id.value": {
      "messages": ["This value already exists in another record."]
    }
  }
}

Is there a single command I can run for all records that either update (if it already exists) or adds new (if it does not already exist)?

2

Hello philkelly6,

Kintone does not have an API that can add and update records simultaneously.

As you mentioned, the record update API “PUT” cannot add records.

Even if you specify a non-existing value to a field specified in updatekey, it will try to update records, so if a target update record doesn’t exist, GAIA_RE20 will occur.

On the other hand, the add record API “POST” cannot update records.

Even if you specify an existing value to a field, it will try to add records. Thus, if the field has enabled " Prohibit duplicate values, " it will give you the CB_VA01 error.

However, you can specify queries to filter records in the GET record API.

So the only way is by:

  1. Check whether the updatekey value exists using the GET record API.

  2. Depending on 1., execute PUT or POST

I hope this helps.

1 Like
3

Chris, this seems very inefficient.

I am trying to sync a database from our other software to Kintone. Are you saying the only way to do this is to download all records (from datasource), then download all records from Kintone, compare both data sets to find out which records need to be added and which records need to be updated, then execute appropriate functions?

thanks,
Philip

4

Hi Phillip,

As Chris mentioned, unfortunately, that is the only way at this moment.

Sean