Automation Upsert

[david_oplinger]

I have a transaction table that is collecting post information through a serverless function. When I get those transactions, I want to run an automation that will look for am email address and, if found, update the contact record, if not found, insert it.

I need an Upsert.

Is there such an animal in Airtable?

[Steve_Siegel]

Ditto. I’m blocked with automations without either an upsert or a conditional statement to use.

[kuovonne]

Welcome to the Airtable community.

Doing this currently requires a scripting action. If you do not code and are not interested in learning to code, but have budget, you could hire someone to write a script for you.

[david_oplinger]

In every API there is a need for an Upsert action. Without it, you have to write a custom script every single time. You have all the parts there and the difference between insert or update is really just a conditional statement.

I have put in several separate requests for this feature change, complete with examples and sample code, on how to accomplish this. I would even work with your developer to build it as it won’t take more than an hour of conversation and less to write.

If you are interested in putting this feature in I would love to assist you. There are a number of requests out there for this functionality to come out of the box. Forcing everybody to have a custom script implementation does not make any sense as it is the last basic feature of your API that needs to be developed. You have search, insert, update, and delete. Being able to insert or update, i.e. an upsert, is the missing link.

Please consider this a top priority feature. The difference between insert and upsert is literally an if statement.

[kuovonne]

You are welcome to make your suggestion directly to Airtable support.

This is a community forum, and most people here (myself included) are Airtable users, not Airtable staff.

[Dominik_Bosnjak]

In every API there is a need for an Upsert action. Without it, you have to write a custom script every single time.

Agreed. But there’s nothing stopping you from copy-pasting your automation script to a different base.

And a half-measure approach here would be to create an automation that links to your email table, then have a rollup field in the original table or a lookup targeting a formula in the email table do the rest of the work. This way lets you avoid coding, so long as we’re pretending formulae aren’t valid JavaScript.

[david_oplinger]

Please pardon my passion! I do understand (now) that I was “preaching to the choir”! When I was responding to the previous comment, I had assumed it came from an Airtable mediator.

Yes, you could copy a script between bases (which I currently do), but that is not my point. Scripting requires a Pro license. Currently, without a full pro license, you have Insert and Update. On an Insert, without coding and without scripting, you can use the output of that step to Update another step. You can also lookup a records with complex logic to determine which one you want to operate on. If there were a step that looked up a record and gave the option of creating a new record if no record were found, then we could do the same thing as an Upsert.

Another option would be a logical IF statement in the standard Automation flow. Currently, it either runs or it fails, there is no failover logic. It would be nice to have Logical error handling because we could do branching logic for automations at that point. The script that I have that does this looks for records and inserts them if they are not found, but that requires… Pro.

It just seems like it is an oversight; if it were a choice, then that functionality would be a new option unlocked by a Pro account, much like running a script.

[Mohamed_Bin_Abd]

I have the same issue, and not knowing how to code or script I just created two automation. One for creating a new record and one for updating. Simply create a view that only shows the existing email addresses and set automation to update the contact record if the email is in that view. And also create another automation that will create a new record the moment it enters the view. I'm not sure if you can adapt this to your use case, but it worked for mine. 

But to be honest I expected Airtable to have this Upsert option.