Airtable API doesn't filter records correctly

[michal_platek]

Hey Airtable Community!

I have a problem that's been bugging me for some time and I can't seem to solve it, so I decided to ask for your help.

I have an automation on Make.com that pulls data from my bank account, looks for transactions by [transactionId] in my Airtable base and, if not present, adds a new record. The problem though is that the filter formula I'm using in my API call sometimes works and sometimes doesn't.

What I mean is when I perform tests, everything runs fine and after a few days the formula stops working - it doesn't identify correctly the records that are already in the the table and adds them again, effectively duplicating existing records. When I trigger the Airtable API call manually, using the [transactionId] field with filterByformula, it returns records correctly, but when it is executed in the workflow, it doesn't work.

This is the formula in the API call I'm using: FIND("<transactionId>", {transactionId})=1

Any help or guidance about what might be causing this would be greatly appreciated 🙂

[TheTimeSavingCo · Accepted solution]

You mention that it runs fine, but then later on you say that when executed in the workflow it doesn't work.  Does this mean that it has never worked outside of testing?  Can you provide screenshots of your Make setup, data, output bundles etc?

Instead of using "Find" you could also try a "{transactionId} = <transactionId>" too

[ScottWorld]

@michal_platek 

You didn't provide a screenshot of how you’re making the API call, but when using Make’s automations for Airtable, you will want to use the Airtable Search Records module.

The most common mistake that people make is that they specify a “view” when performing their search. Do not specify a view, or else you will be limiting which records you are searching for.

Hope this helps! I am the top Airtable/Make expert in the community. If you’d like to hire an expert to help you with anything Airtable-related, please feel free to contact me through my website: Airtable consultant — ScottWorld 

[michal_platek]

Hey, thanks for the answer and sorry for the lack of context - it passed tests and ran fine for a few days / up to a week, then I checked the database and I noticed that transactions were getting duplicated.

Here's the screenshot of my scenario - it's super simple, it fetches transactions from the past 7 days, iterates over them, then the Airtable module checks if fetched transactions exist in the database by ID and, if not, sends them to the 2nd scenario that adds them to the database. I work with Make a lot, so there's nothing that that could be the reason for this error except the AT module.

As per your recommendation, I changed the "Find" formula into "{transactionId} = <transactionId>" and so far it works well, thanks!

[michal_platek]

Hey, thanks for the answer, I work with Make and Airtable a lot both professionally and personally, so I already learnt to avoid this kind of mistakes.

In this case, it seems to me the problem was caused by the formula - when I switched to "{transactionId} = <transactionId>" from "FIND("<transactionId>", {transactionId})=1" - as suggested by @TheTimeSavingCo  - I stopped getting duplicates. 

It's strange the FIND formula caused this type of error, as the formula output in a 'test' Airtable column was correct 🤔