Searching for duplicates and performing an automation when duplicate is found

[Radhe-iad]

Hi,

I have a table where people insert new records through an Interface, one of these columns needs to be unique.

My issue is that i cannot stop people from inputting the same data by mistake through Airtable's Interfaces (so for example they need to fill the form  with "12345", but they write "21345" by mistake, and "21345" is already there in the database), so i want to catch this duplicate's existence afterwards by sending an email and performing other operations when it happens.

Do you know if this can be done?

[Karlstens]

What happens upon a duplicate Record being matched? Is the matching field a Number, and upon being matched, needs to be incremented - and if it's a number, could you perhaps make use of the Auto-number ID field to keep numbers unique and avoiding any possibility of duplicates?

[Radhe-iad]

Sadly that's not a solution, when a duplicate is found we need to send an email warning to tell us that who put the data did it in the wrong way and we need to manually act on the database to correct a mistake.

This solution is a workaround to not being able to stop users from putting a number that is already in the column that the Form Interface updates

[Radhe-iad]

I think i found a workaround through automations and a new table:

I created a Check duplicates table (Table B) where the only thing i do is linking every single record from the table i want to check (Table A).

I trigger the automation everytime a new record is created in Table A, the automation finds every Record in Table B and with conidionals it checks if the unique field is already in Table B, if it is not it adds the new record, if it is it sends the warning email and checks a checkbox.

It bothers me that i haveto create a new table just to do this as my workspace is becoming increasingly dirty with tables needed only to run automation and set variables but it seems work fine for now.

(obviously the same check cannot be done in the table where the data is sorted because it will find the record that has been added a few moment ago and cannot distinguish between the record you just created and the other ones, so the comparation won't work and will always find an existing record with the same ID)

[Karlstens]

Could you share a couple of screenshots that demonstrate the workflow, the problem and the desirable outcome? Creating a script to find and action duplicates shouldn't require a second table.

Are the users entering in the new record with the unique field having to check if that number already exists prior to creating their record?

[Radhe-iad]

The user CAN check if the number already exists but i don't want to count on it because it's poor design and a hassle.

So here is an image i made to try and explain the workflow and the problem, on the left you can see the column with the IDs, on the right you can see
1. how Airtable works right now: when you fill a field in Interfaces and you input the ID, you save, and you can have a new ID with the same values of a record that was already in the table.

2. how i would like it to work: you input the ID and when you try to save, or even as soon as you deselect the field, Aritable doesn't allow you to save and tells you it's a Duplicate.

3. The workaround i need needs to input a check in another column when a new record is created that has an ID that already existed in the Column (i use che check as a boolean value to send a warning email that this happened).

I talk about ID but it is just a Number table, i'm not talking about Record ID or Primary field column.

[Karlstens]

The way I solved this similar problem for my users, was to have Airtable issue the default ID of ALL newly created records - which covers 99% of new record creations. The user simply don't have a say in which ID is issued next.

However, I did leave one manual method of record creation, which allows the manual entry of an ID number - but it is the users responsibility to make sure they're picking a non-existing number, which as I type this out now, leaves me a little unsettled knowing that they too may not check if their nominated number already exists. Worryingly too, if a user creates a massive unreasonable ID number, that is accepted into the system, this then would confuse the auto-ID number generator as it would then start to create massive numbers for future records. 😂 😱

Knowing that I more-or-less face the same issue that you're talking about, in that, how do we let the users known that the number they've entered before creating a record has already been taken - my immediate solution is to simply take that option away from the user and let the database take care of all new records and automatically generate a non-duplicate ID (no doubt sequential).

I too am keen if anyone has any outside-the-box kind of ideas on how to solve this. One thing to keep in mind, if your users are entering in data via an Interface, then they can easily search for existing IDs through the Interface "Record Review" page that lists out all records.

[Radhe-iad]

That is indeed a solution, but one I cannot apply because the ID they have to input is a number given from another private system (that cannot comunicate with Airtable, sadly), so the ID has to be an arbitrary number.

It's like having to input a name and checking it already exists, you cannot generate an automatic name, you just need to check if it exists or not. 

Also the fact that you cannot have a record picker in a form inetrface bothers me because people have to change interface ust to check for duplicates and cannot do so in the same interface where they input the data.

[Karlstens]

What we’re really needing here is to have the ability to execute an Automation script that returns feedback the to user upon executing or throwing an error. Or, to have a scripting extension available to run from the interface. Both of these are absolutely lacking and very much needed for interfaces - and I don’t know why neither have been developed by Airtable yet.

[lukas_mayd · Accepted solution]

In case anyone stumbles on this issue, this simple automation is what worked for me, and doesn't require any extra columns or tables:

It does the following:

1. Trigger every time a new record is created
2. Search the table to see how many records there are matching the same name / ID / any other field you want to check (make sure to set the condition as dynamic so you can use the input from the previous step!)
3. If the count is more than 1, it means that a record with the same name already existed
4. In that case, send a Slack message informing someone about the issue (as an alternative, you could also use a script to automatically delete one of the entries)

It's not the best solution as it doesn't solve the issue of people submitting wrong data, but at least it gives a notification when it happens. Hope it's of use to someone!