Script to check for an existing record before creating a new one

[Emily_McLain]

Hello!

I have a large amount of sales records that I need to automatically transfer from an "Ad Chart" tab to another "Production" tab, to be assigned to various designers. I want to be able to check first if a record already exists on the Production tab before the automation creates a new one, to prevent duplicates. I've already tried the "Find Records" action and while it works initially, we simply have too many records for it to check (I've read that the limit is 100 records), so this will not work at scale. 

Looking through forums and help posts, it seems I would need a script to accomplish the search, but I am wholly unfamiliar with scripting. Any suggestions? 

[bruceconsulting]

Hi Emily,

There are 2 extensions in Airtable's Marketplace to deal with duplicate records.  If you start to type "duplicate" in the search bar, you will see them:

If you have any other questions, feel free to schedule some time with me.  Here is my Calendly link:  https://calendly.com/d/ygw-5bw-h4j/30-minute-meeting

Jody

[Emily_McLain]

Hi Jody, 

Thank you for the suggestion. 

I've looked into those and they do look useful in removing duplicates after they occur. However I'm hoping to have something that will check before a duplicate is made. That way I can set up my automation to run on a rolling month basis, and still have it catch something newly added from that time frame. I'd also like to be able to update the "Production" records if the corresponding one on the "Ad Chart" changes.

[TheTimeSavingCo]

> I'd also like to be able to update the "Production" records if the corresponding one on the "Ad Chart" changes.
Could you have the automation trigger every time a record gets created in "Ad Chart" instead?  If so, I would recommend linking the two tables together, and whenever a new record gets created in "Ad Chart", paste its unique ID into the linked field.  This will force the creation of a new record in "Production" that's linked to the "Ad Chart" record

You could then add lookup fields in the "Production" table to display the fields you wanted, and any changes made in "Ad Chart" would show up immediately in "Production"

If not, you could try syncing the "Ad Chart" table to another base, then syncing it back into your original base.  Considerable lag time, but would allow you to essentially "duplicate" the table without a script

--
If you have the time, I'm curious why you'd want them separated into two new tables instead of having them as different views within the same table actually.  No worries if you're busy though!

[Emily_McLain]

Hi Adam, 

Thanks for the feedback!

Without going into the data, we have a couple dozen products,  for 100+ companies, across 3 publications on this "Ad Chart" table, so it makes sense for us to keep that as the central calendar and funnel projects out to the appropriate departments from there. Everything on that table isn't all or only going to the "Production" table.

I thought about directly connecting the two for a long time. I actually already have this set up with the "Ad Chart" and another "Sales" table that feeds into it. The "Production" table is used for other projects though, so I would need multiple fields looking up the same information but linking to different tables. i.e. a "clients" field that is connected to the "Ad Chart" record but then I would need another "clients" field for projects not connected to that table. I could see using automations/formulas to combine all these twin fields, but it's so circuitous. 

Definitely not dismissing this path entirely, but hoping for a simpler method before going into all that set up. 

[TheTimeSavingCo]

Ahh fair enough.  If you were open to doing a per-record automation, you could make it just create the record with the values in "Production" every time a record was created in "Ad Chart", and have another automation that would trigger on update of some specific fields, and it'll update the record in the "Production" table?

So let's say you created record "ABC" on Ad Chart, the automation runs and creates a new record in "Production" with the unique ID "ABC.  When you update said specific fields on a record in "Ad Chart", it'll look for a record in "Production" with "ABC" and update the fields as needed, and if it can't find one it'd create a new record?  

May end up burning through automations really quickly though as you're basically replacing the lookup fields with an automation run though

---
I was thinking about the scripting side of things, and the part where you want data changes in "Ad Chart" to show up in "Production" will be somewhat tedious to handle with a script.  The field names would have to be hardcoded if you were doing this via an automation, so any changes to field names or field additions would require you to make changes to the code directly, which seems pretty painful

[Emily_McLain]

Hi Adam, 

That's what I set up initially in the testing phase and it does work:

However, it doesn't work at scale, there are simply too many records for it to check. From what I read, Airtable limits the Find Records action to 100 records and I need it to search thousands. I could maybe break up the search, but as you said I would burn through automations quickly. Sucks that there can only be 50! 

I currently have a 2 tier automation set-up. 1 that sends a months worth of records, manually triggered with a view change and I toggle it on and off for safety. And then a backup to catch anything new that is added in that particular time frame. It works fine I just wish it didn't have to be manual. And there's always the risk of accidentally creating a thousand duplicates because I set the wrong filter with the automation running. 

I would only need probably one date field to update in connection between the two tables, but same issue here with there being too many records to search to find the one to update.

I'm definitely totally ignorant to how scripts work, I've only just got my head around formulas and automation actions. I was hoping there was a magic script to expand the Find Records function lol. 

[TheTimeSavingCo]

Ah, I was thinking to just create a record in "Production" when a record was created in "Ad Chart" and so there'd always be a one to one record match between the two tables, and as such you wouldn't have to check whether there were duplicates.  It seems like that wouldn't work for you though

---
Yeah...unless you're familiar with JavaScript I don't recommend going down the path of scripting. 

It's fairly simple to do though so if you know anyone that could help you with it it'd probably take an hour or so.  The downside would be you'd end up having to get them to help you with it every time there was a change / issue though, and so I would really recommend you do the lookups thing you were talking about earlier in the thread as that would be more resilient and you could make changes to it yourself