AND() formula breaks down in API filterByFormula

[Brian_Schuster]

I am using the AND() formula to apply multiple filters through filterByFormula query in the API via Adalo. Each formula within the AND function works independently, but when combined, neither works.

There is a discussion open on the Adalo forum with details:

Adalo – 22 Jul 20

Filter (airtable populated) list by search term?

@bgschust Try to change from double quotes “ to single ‘ in the first part of the formula =‘User user id’

[Bill_French]

Brian_Schuster:

I am using the AND() formula to apply multiple filters through filterByFormula query in the API via Adalo

I recently fixed one of these for an Adalo developer last week, If you’ll share the code that contains the filterBy parameter, I’ll take a look at it.

[Brian_Schuster]

[Bill_French]

Try changing all single quotes to double quotes.

[Brian_Schuster]

I was using double quotes before. Didn’t work.

Is there a way to do a lookup or rollup with a formula? I could pull search queries into Airtable and then process them there with the right filtering functions. Maybe that would require the scripting block again.

[Bill_French]

Brian_Schuster:

I was using double quotes before. Didn’t work.

I’m almost certain this is related to quotes. I note that there are quotes around the string passed into Lower(). Are you certain this is necessary? Isn’t Search 38 in Adalo’s world a variable not unlike a javascript variable? If so, quotes would be unnecessary and that could cause issues.

Also, quotes around booleans - is that also necessary?

[Justin_Barrett]

As @Bill.French pointed out, quotes around the boolean values aren’t necessary. In fact, those aren’t even the correct ways to represent those values in Airtable. If you need to insert Airtable’s booleans (which, in this case, you don’t; see below), they are functions: TRUE() and FALSE().

The other issue is that your formula is more complex than it needs to be. Each comparison, and the AND() function, will automatically return a proper boolean value on its own, so wrapping them in IF() functions and manually returning booleans—even if entered correctly—is redundant. Try this instead (I wrapped double curly braces around those inserted values from the app you’re using):

AND({User ID} = '{{User user id}}', SEARCH(LOWER('{{Search 38}}'), LOWER(Name)) > 0)

[Brian_Schuster]

I’m able to get the formulas to work in Airtable, but something’s going wrong when I use the API through Adalo. Do you have any recommendations on the API front? We have a thread going on the Adalo forum.

Adalo – 23 Jul 20

Filter (airtable populated) list by search term?

@bgschust Now back at the big screen and system I did some testing with the Airtable API using Postman. You an only have ONE filterByFormula. Adding to filter in Adalo will throw an error in Airtable: It is possible to combine Search with...

Is it possible to do filterByFormula[0] and filterByFormula[1] for multiple filters?

[Bill_French]

Brian_Schuster:

something’s going wrong when I use the API through Adalo.

Indeed, while simple filters seem to work in Adalo, this is either inconsistent, or unable to work with complex filters. Here’s how I have overcome this.

• Adalo calling…
• Custom Google Cloud Platform Webhook Server

I built the webhook server in Google Apps Script to listen for requests from Adalo and returning the filtered data. The webhook server connects to Airtable via the API and performs the required requests with complex filterByFormula arguments.

[Brian_Schuster]

Let’s say I want to filter Airtable records in Airtable using an Airtable formula to generate a list of record IDs that match the query. Can I do that? In Excel, I would probably be able to do it with VLOOKUP().