How do we Handle Data Duplication in Airtable? In Airtable, one of the most common issues you can face is data duplication. For example, when you have a form that saves data in airtable, sometimes you can have respondents fill the form twice, creating duplicates. Let’s explore two ways of handling the issue, based on difficulty and flexibility.
Let’s explore each of the two…
This is the easiest way to handle data duplication in Airtable as you just need to use Airtable automations. With this system, you will be able to mark any new entry as duplicated based on previous data. But before we start, to implement both de-duplication systems, you need an identifier field. The identifier field or column is nothing but a field, unique to each record in your system and used to check if new data is duplicate or not. In this example, I will take the email column as such duplicate identifier.
To begin, you need to click on the “automations” flag which is available on the top right corner of your Airtable base. Once you click on it, you will get many options from which you need to select “Create a custom automation”. Now let’s go through the steps one by one…
Step 1: The first step is to select a trigger by which will prompt your automation to run. Let’s select “When record matches condition” as trigger, which means that the condition we specify is used as trigger.
Step 2: Then, you should select the table on which you want to perform the automation and the specific condition you want to use. The configuration for the trigger is available in the image below.
Step 3: Next you have to select an action that you want to perform. In our case, we will look for the records which are equal to the value of our identifier column and if there are any matches, continue further. For this action, my configuration is as shown in the image below.
Step 4: While we go through the records, if we find any duplicate records, we will mark them as duplicates which is our second action. You need to add a new “Update Records” action and use the image below to mark them as duplicate.
Further you just need to turn on the automation which will mark any duplicate new records as duplicates.
Notes: Some things that you need to keep in mind are that for this option to work, there must be an identifier field and also a checkbox field that will be used to mark the records as duplicate as you can see in the image on step 4.
In this way you can identify duplicates and mark them as such, but this system presents a strong limitation. You can only mark the duplicates as such, and not remove the duplicate records entirely. Also you can mark new record only because update records action support’s only one record id. Let’s learn about a more flexible way to handle duplicates
Following is a more difficult but more flexible and appropriate system to mark duplicates in Airtable. Here, we will be using the scripting app to handle duplication, allowing you to delete, update or perform any other operation on the duplicate records. Go to the “Apps” option on the top right corner of your Airtable base and click on it to install a scripting app.
Now Let me give you an idea of what our script is doing. First, we are getting the records from the respective tables. Then we are going through a loop and finding duplicated records. If they are duplicates, then we update the oldest records with the newest records. We then also delete the duplicates. You need a createdTime field to identify new and old entries, in order to identify records correctly.
This is just an example, we can work together on customising the your script in many ways, depending on your specific business needs. If you found this article helpful, don’t forget to bookmark our website for future, useful articles. Feel free to also schedule a first, free introductory call to discuss your specific needs and how I can help you achieve them.
Click here to schedule free initial consultation session to discuss your specific needs.