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?
Ditto. I’m blocked with automations without either an upsert or a conditional statement to use.
Ditto. I’m blocked with automations without either an upsert or a conditional statement to use.
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.
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.
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.
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.
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.
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.
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.
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.
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.
It’s still important to add. Could you please add it for us?
You could use a conditional automation to do one thing if the value exists, and another thing if the value doesn’t exist.
Another solution for this is to use Make’s advanced automations for Airtable, because they offer a native upsert function for Airtable.
If you’ve never used Make before, I’ve assembled a bunch of Make training resources in this thread. For example, here is one of the ways that you could instantly trigger a Make automation from Airtable.
- ScottWorld, Expert Airtable Consultant
Yes, I have original automation in Make, and there is that method. But the task requirement is to have automation right in Airtable.
It would be great to have a condition, but I cannot add it while iterating (repeat for each). I am a developer, but it would be more readable in the flow.
Thanks for trying to help.
Yeah, it’s a little annoying, but you can break that up into 2 automations. Do the repeating loop in one automation that marks each record with a checkbox or something like that. Then, do another automation that triggers when the checkbox is checked — you can do the conditionals there.
- ScottWorld, Expert Airtable Consultant
Otherwise you can emulate similar behaviour in a script action (assuming you've got the data model sorted for what the business key needs to be).
Depending on your use case, the latter can actually be more powerful / flexible since you can do arbitrary data handling & transformation inside the function too.
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.