New beta: REST API Upserts

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:

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?

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:

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.

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.

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.

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?

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)?

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.

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.

If multiple records match the composite key, the request will throw an error.

Having multiple records created/updated is unrelated to the new upsert feature. You can update up to 10 records at a time in one REST API call, but all 10 records are different records. All ten records should either have a record ID, or a unique set of values for the composite key.

Is this working on production or should I request access to the beta? I've tried this following the documentation (https://airtable.com/${MyBaseId}/api/docs#curl/table:${TableName}:update)

My request looks like this:

My request answer is: `'Request failed with status code 422',` (If I don't include the performUpsert the request works)

Also, I have a second question, can I use fields that are "link to another record"?

Getting '422' response when I attempt to use this. Do I need to be a part of a private beta?

I don't really understand how this can work properly.

What if you're changing a field value that is in the fieldsToMergeOn? Then it won't find the right record.

When can you guarantee a non-calculated field is going to be unique? All the field types mentioned could easily not be unique between rows: number, text, long text, single select, multiple select, date.

As soon as two rows have one of those fields the same, the API will fail. E.g. if you have a users table and two people had the same name.

Not sure how usable this is. I can only use this myself becasue we have a field that's stringified JSON that will always be unique.