+11I 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:
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.
+11I 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.
+11Try changing all single quotes to double quotes.
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.
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.
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?
+21As @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)
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)
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.
Is it possible to do filterByFormula[0] and filterByFormula[1] for multiple filters?
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.
Is it possible to do filterByFormula[0] and filterByFormula[1] for multiple filters?
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.
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.
+11Indeed, 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.
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.
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().
No account yet? Create an account
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.