New beta: REST API Upserts

[Eileen_Xing]

Hi everyone! We’re excited to announce one of our newest features - REST API Upserts.

You can now add an upsert option performUpsert to the Update records API endpoint, so that if a matching record is not found, it will create the record instead.

More details about this new feature can be found in the API documentation, under the Update records section.

If you have any feedback about this feature, please submit via this form.

Example request:

curl -X PATCH https://api.airtable.com/v0/[base ID]/[table name or ID] \
  -H "Authorization: Bearer YOUR_API_KEY" \
  -H "Content-Type: application/json" \
  --data '{
  "performUpsert": {
    "fieldsToMergeOn": [
      "Name"
    ]
  },
  "records": [
    {
      "fields": {
        "Name": "Kick off design work",
        "Status": "In progress"
      }
    },
    {
      "fields": {
        "Name": "New homepage",
        "Status": "To do"      
      }
    }
  ]
}'

Example response:

{
    "records": [
        {
            "id": [record ID 1],
            "createdTime": "2022-10-19T16:52:15.000Z",
            "fields": {
                "Name": "Kick off design work",
                "Status": "In progress"
            }
        },
        {
            "id": [record ID 2],
            "createdTime": "2022-10-17T21:31:06.000Z",
            "fields": {
                "Name": "New homepage",
                "Status": "To do"
            }
        }
    ],
    "updatedRecords": [[record ID 2]],
    "createdRecords": [[record ID 1]]
}

[ScottWorld]

Wow, this is great & very exciting news! :grinning_face_with_big_eyes: :raised_hands:

Make.com has offered an “Airtable Upsert” function for a while now, but it was their own custom creation.

It’s great to have this natively as a part of the product! :cowboy_hat_face:

[ScottWorld]

p.s. Your example request doesn’t include the record ID 2 that the response gives down below as a successful update. Did you mean to include record 2 in the initial request?

[Eileen_Xing]

Thanks for the feedback! With the upsert option, record IDs in the request are optional. We’ll instead try to match fieldsToMergeOn to a unique record :grinning:

[kuovonne]

Thanks for this new feature! It looks like a lot of work went into it.

If a record ID is included, does that mean that the record will always be updated, and a new record never created?

If a record ID is included, but that record ID does not exist, what happens? Will that throw an error? Will a matching record be looked for? If a matching record is not found, will a new record be created?

If multiple records actually match, is there a way to tell which record will be updated? Or will there be an error? (Yes, I know that the three key field should uniquely identify a record, but sometimes bases have weird things going on.)

Do all of the fieldsToMergeOn need to be included as fields in each record without a record ID? What if a field to merge on is supposed to be blank?

Fields to merge on cannot be computed and must be of the following field types: number, text, long text, single select, multiple select, and date.

Thank you for this list of field types.

• when matching using multi-select fields, does the order of the multi-select options matter?

• what about other text-based fields, such as email, phone, url?

• what about other number based fields, such as rating, percent, and duration?

• does “date” include both date-only and date/time fields? (From a code point of view, they are different field types, but from the user interface point of view they could easily be thought of as the same field type with different options.)

• does “long text” include “rich text” fields? (From a coding point of view, they are different field types, but from a user interface point of view they could easily be thought of as the same field type with different options.)

Some people might notice that linked record fields are noticeably absent from the field types that can be used for matching when upserting a record. Trying to upsert with a linked record field has all sorts of complications. Thank you for making this beta available before waiting to work through all of those complex issues.

[ScottWorld]

Eileen_Xing:

With the upsert option, record IDs in the request are optional. We’ll instead try to match fieldsToMergeOn to a unique record :grinning:

Oh, I see! That’s pretty clever.

However, the ID could be specified if we want? The documentation says that fields to merge on cannot be computed fields, but ID is indeed a computed field.

Also, just an FYI that the link that says “Click here to show an example” doesn’t work in Safari for Mac, but it does work in Chrome for Mac.

[Eileen_Xing]

Thanks for the feedback! I’ll follow up on the field type questions.

If a record ID is included, does that mean that the record will always be updated, and a new record never created?
If a record ID is included, but that record ID does not exist, what happens? Will that throw an error? Will a matching record be looked for? If a matching record is not found, will a new record be created?

If a record ID is included, it will always be considered to be an update. So if the record ID doesn’t exist, it will throw an error.

If multiple records actually match, is there a way to tell which record will be updated? Or will there be an error? (Yes, I know that the three key field should uniquely identify a record, but sometimes bases have weird things going on.)
Do all of the fieldsToMergeOn need to be included as fields in each record without a record ID? What if a field to merge on is supposed to be blank?

fieldsToMergeOn can be treated as a composite key, they need to uniquely identify a record. So, if multiple records match, it will throw an error. fieldsToMergeOn don’t need to include all fields in a record.

[Eileen_Xing]

Thanks for the feedback! We’ll update the wording on the API documentation :slightly_smiling_face: I tried on Safari for Mac and it seems to be working for me, could there be any extension that affects it?

[kuovonne]

Eileen_Xing:

fieldsToMergeOn don’t need to include all fields in a record.

I’m asking a slightly different question. Suppose you need to upsert a record where one of the three fieldsToMergeOn needs to be blank. Should that field be included in the field list? For example, suppose a table has a text field for {item name}, date field for {date}, and single select for {used by}. We want to upsert a field for an item on October 31, 2022, and have {used by} be blank. Would the request need to include {used by} in both fieldsToMergeOn as well as in the field map (with a null value)?

[ScottWorld]

Eileen_Xing:

fieldsToMergeOn can be treated as a composite key, they need to uniquely identify a record. So, if multiple records match, it will throw an error. fieldsToMergeOn don’t need to include all fields in a record.

So this new upsert command can only update ONE record, even if multiple records match on the composite key?

In your example API response above, it uses the plural words “updatedRecords” & “createdRecords”, indicating that multiple records can both be updated & created. I don’t see how multiple records can be created, but I could see how multiple records can be updated if multiple records match on the criteria.

Eileen_Xing:

I tried on Safari for Mac and it seems to be working for me, could there be any extension that affects it?

I don’t have any Safari extensions installed, but this happens 100% of the time for me. I can’t post a video here because it will reveal private information about my base, but I’d be happy to share privately.

Whenever I click on “Click here to show an example”, it just randomly scrolls the entire page somewhere completely different — sometimes it scrolls upwards, sometimes it scrolls downward, but it always jumps somewhere completely random. I’ve tried this with several different bases & several different tables.