How Do I Use Automations to Compare Two Tables

[Brandon_Buchana]

How do I use automations to compare overlap between two tables of products?

I wanted to trigger the automation with TBD, have it look at the table of 2021 products. If matching model number was found, it would mark the corresponding one in 2022 as REPEATED or if it couldn’t locate a match, it would mark it as NEW.

Below is my attempt, but it isn’t working. Is what I’m trying to do possible with automations?

[TheTimeSavingCo]

Yeah, this should be possible with automations. Could you provide some screenshots of the update record actions that are erroring out?

[ScottWorld]

Welcome to the community, @Brandon_Buchanan!

Your automation’s conditional actions should be looking at the “records length” of the “find records” action to determine if records were found or not. If the records length is 0, that means that no records were found.

[Bill_French]

Brandon_Buchanan:

How do I use automations to compare overlap between two tables of products?

I always get a little nervous with an approach like this because I think (and @ScottWorld can correct me here) that this automation would force a seek against 2022’s data set for every record in 2021’s data set.

In a purely no-code approach, this is probably the only way to do it, but it will absorb some energy doing it. And to be clear, automations can easily bring Airtable to its knees for other users. In this case, even if this works, it will get slower as the data sets get larger or depending upon the triggered pace of change.

In a script automation, you would read the target data set once to fetch the expected record and it would return true or false - literally a few milliseconds per inspection.

[Alexey_Gusev]

If you want to do a single compare action, you don’t need automation. Temporary set table permissions “who can create records” for 2021 to nobody, then in 2022 create new linkied field (to 2021) and copy whole model column there.
Like this

Now you can return permissions. Records not linked are new.