Formula that's working in Google Sheets, isn't wor...

[Richard_Foxwort]

I’m using a Search Airtable module in an Integromat/Make flow, to find a matching Airtable record from an incoming email.

The flow works by watching Gmail using the Make Gmail module, creating an array of the to: addresses, including all of the to, CC, BCC, and ‘delivered to’ address headers, then iterating through that array to find if any of these addresses match a user email address in my Airtable.

I’ve found examples where the Make Airtable Search module fails to find a record, but the record actually does exist, and when I copy and paste the exact same formula into a test formula field in my Airtable, it successfully returns a value ‘1’ for the relevant record.

According to the Make docs:

An Airtable formula used to filter records. The formula will be evaluated for each record, and if the result is not 0, false, "", NaN, [], or #Error! the record will be included in the response. You can find more information about the formula on Airtable’s website.

This should mean that the same formula that returns a ‘1’ value for a relevant record in Airtable should also return a match in Make.

It does not.

Integromat support say that this is an Airtable issue, all they’re doing is throwing the query at Airtable API and listening for a result. I’m still waiting for Airtable support to reply…

Has anyone experienced similar problem with Airtable Search model in Make failing even when there actually is a correct forumula and a match in Airtable?

I’m a bit lost with how to proceed on this one.

[ScottWorld]

No, I have never had that problem. The search module works just fine. Post screenshots of your formula in Make.

[Richard_Foxwort]

Hi @ScottWorld , here’s the formula in Make:

And here’s an instance of that formula in a dummy formula field called ‘test’ in my Airtable that successfully returned a value ‘1’ for a matching record. I’ve obscured email address info from this screenshot. In this particular example, its the REGEX_MATCH that is generating the ‘1’ result in Airtable because one a Value in my array does match the value in my Forwarder match address field :

Here’s the History for this execution in Make showing that this exact formula returned zero matches:

The view window truncates part of the formula, so here’s the text version taken from the ‘Download input bundles’ option in Make. I’ve used ******** to obscure the base ID and email address data in this formula.

[
    {
        "base": "********",
        "view": "********",
        "table": "********",
        "formula": "OR(\n{BHID lower}=\"********\",\n{BHID lower}=\"********\",\n{email}=\"******@*******.com\",\nREGEX_MATCH(LOWER({Forwarder match address}), \"(^|[\\\\s,])\" & REGEX_REPLACE(REGEX_EXTRACT(LOWER(\"********@********.com\"), \".*@\"), \"[-\\\\/\\\\^$*+?.()|[\\\\]{}]\", \"\\\\$&\")))",
        "maxRecords": 10,
        "useColumnId": false
    }
]

I’ve successfully used this Search Airtable technique in Make the past - I actually rely on it quite a lot, but it now seems intermittently broken…

I’m wondering could this be rate limit issue? Maybe some other kind of transient problem? Looking back through my service logs it seems this issue really started emerging after 01:41 UTC 4 October 2022.

I’m hoping some users here may have seen similar issues and found a way to diagnose and prevent it from occurring.

The only information Make provides on this problem execution is that zero results were returned by the Airtable API. Eg the output bundle log from Make shows only this:

[
    {
        "__IMTLENGTH__": 0
    }
]

[ScottWorld]

So that’s good that you’re not getting an error message… you’re just getting zero found records. So that means that your formula is valid.

You didn’t post the screenshot of your entire search module, but I can see from the input screenshot that you specified a view.

Assuming that you are already specifying the correct base & correct table, what about the view that you specified?

Are you 100% sure that the view you’re specifying contains the records that you’re looking for?

The view is optional, so it’s often best to leave it empty.

[Richard_Foxwort]

:pray: thanks Scott!

I had filtered the view that this scenario relies on, for an unrelated purpose.

That explains why the record could not be found. :flushed: oops.

Thanks very much for your reply - your question about View pinpointed the issue for me!

I didn’t realise View was optional - I’ve removed it now

[ScottWorld]

@Richard_Foxworthy

You’re welcome! Glad I could help! :slightly_smiling_face:

If you don’t mind, could you please mark my comment above as the solution to your question? This will help other people who have a similar question in the future, because it will rise to the top of the search results. :slightly_smiling_face:

[Sikwal]

Hi @ScottWorld, I encountered a similar problem but the “View” solution didn’t solve the issue for me.

Here is my search records setup in MAKE:

And my successful test containing the exact same formula in Airtable:

Do you have any idea what’s wrong here?

Thanks in advance for your help.

Take care!

AND(OR(FIND("Chicago, IL", {what is your local market? (from profile info)}), FIND("Yes, I'm willing to travel", {are you willing to travel? (from profile info)})), OR({main skill}="Audio - A1 (Audio Engineer)", FIND("Audio - A1 (Audio Engineer)", {secondary skills (optional) (from profile info)})))

[ScottWorld]

If your formula in Make identically matches your formula in Airtable, then you’ll need to see if you’re feeding Make the right tokens (in blue).

You can also go to your history in Make to view the details of the run that didn’t yield the results you were looking for. Then take a look at what the actual information was that you sent to Airtable.

[Sikwal]

Thank you for responding so quickly.

The problem is that when I look at the run history, I see that the information processed by Make is correct. If I simply copy and paste the information from Make to Airtable, the result is different:

I can’t figure out what’s going on here

[ScottWorld]

If you copied and pasted the formula from Make into Airtable and you get a 1 in Airtable as the result, then I would double-check to make sure you’re referencing the correct base & correct table in Make. Be sure you’re not accidentally referencing a backup copy of your base or another similar table.

If that’s not the issue, then I wouldn’t know how else to guide you. Maybe try starting with a smaller formula or simplifying the field names to help troubleshoot where the problem might lie. Or reach out to Make support and see what they say.