Filter (airtable populated) list by search term?

I am starting to test out Airtable as a replacement for the Adalo database. Can anyone help with how to create a filter based on the search term value?

I have a field that I have called “Search Index” with a handful of keywords in it. I want the list to filter based on any of the keywords appearing in the search term(search bar value).

I cannot seem to figure out how to create a formula that is CONTAINS instead of =.

My Search Index value looks like this: value1 value2 value3

GOAL: My only goal here is to have a list where the records can be searched by multiple properties. (i.e: first name, last name, title, etc.).

@ldyer Hi Luke, I am using the following:
In Adalo:
Input field Search1 for the search:
image
image

A List with based on external collection image
with following filter:

Note the LOWER in the formula as the Search function in Airtable is case sensitive.

In Airtable
In table Appointments I am using a formula field Search to collect a columns I want to search on:
image

2 Likes

@karimoo You have just saved me SO much time!! This is brilliant. And it allows me to update search parameters without touching adalo and pushing out updates. Thank you so much.

One more follow-up question, since you appear to be super knowledgeable on this. Do you know if it is possible to limit the search to complete property values?

Example: I want to search for the first name ryan and NOT return a value for the first name bryan.

You can use (and adapt to’m your context) the following filterbyformula formula to filter your list :

And(Search(, {Field}) = 1, LEN() = LEN({Field}))

So that you’ll get only the results matching your search parameter. If your value to search is “Ryan”, length = 4 and you’ll get only results with Ryan, if any.

Add the Lower fonction suggested by @karimoo, it’s an excellent idea!

2 Likes

I was expecting this to work, but it didn’t. It is a combination of the search you’ve shown plus a user filter that was working before I added the and() formula. Have you had many problems with ands… what looks wrong with my formula?

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

I opened up Chrome and tried it in there with single quotes, but that didn’t fix it.

Also note that I’ve been able to get both working independently before, but for some reason the AND() complicates things.

I tried this too since Airtable sometimes uses 0 and 1 to indicate true and false.image

I’ve also added the topic to the Airtable forum, but I think it’s fine to keep the discussion here on the Adalo forum.

@bgschust I guess that Search cannot be combined with other filters. What about adding the user ID to the search field?

I think I know what you mean, but that would involve a lot of coding in Airtable.
With what I’m thinking, I would be able to save searches and filters, but it’s going to be another project.

On second thought, what I’m imagining won’t work unless lists within lists are working properly with Airtable formula filters.

Maybe you’re thinking of something simpler?

@bgschust Just one more guess. What if you add two filters in Adalo . One for the Search and a second for the user id. Never tried it with Airtable. But two filters in Adalo are combined with an AND.

Could you please clarify? I built this previously with Adalo bases, but then I switched to Airtable because I needed more analytical and review power. It is not practical to switch everything again in a short amount of time.

I don’t mean to switch to Adalo DB.
I mean: Add one Filter with the Search expression only.
AND add a second Filter with the {user ID} = USer user id

Two query parameters? I’ve tried that before but I can try again.

Now the user ID match is not working by itself anymore.

Maybe I need to write filterByFormula[0] and [1] like the sort functions.

There could be a ghost haunting my app development environment.

I can’t get the user filter to work with the exact same formula I had before. Somehow I lost ground. I can’t get the user’s record fields in the formulas now. This happened before, then started working, and now it doesn’t again.

@bgschust Now back at the big screen and system I did some testing with the Airtable API using Postman.

  1. You an only have ONE filterByFormula. Adding to filter in Adalo will throw an error in Airtable:

  2. It is possible to combine Search with filtering on fields Using AND:

My Example is using Treatments instead of your User ID:
AND(({Treatments}=‘PRP’),Search(LOWER(‘Karim’),LOWER({Patient})))
image

=> You are just missing some brackets for the first part.

Should be:
AND(({User ID}=‘User user id’),Search(LOWER(‘Search 38’),LOWER({Name})))

There’s something going wrong with how values are passed from text input fields and Airtable record properties back into Airtable inconsistently.

There have been days where input fields and Airtable-source dynamic text both work, but now today, the dynamic text is not working. It was working earlier yesterday. If the quotes are causing issues, that would explain why I can’t even get the User ID to match today without even bringing AND() back into it.

I am able to get formulas to work in Airtable, but the problem arises when I apply them through Adalo. Could Custom Actions be modified to enable list retrieval from Airtable?

@jeremy know it’s a big ask, but if you’d be willing to share more screenshots about how Adalo’s back-end works, maybe I could help diagnose the problem there. My interface is too limiting at the moment. You guys can see everything I do, so I feel like it’s fair.

If someone could teach me how to use Postman, I think I could do a lot. I just don’t know how to use it yet. I have it downloaded.

Christof, have you tried doing filterByFormula[0] and [1]?

My whole thing is broken so I can’t test it. I can’t get my basic user filter to work, which was working before.
image

Here’s something interesting:

@bgschust 1. When you say something is broken, what has been working before:
Did you make any changes to the table in Airtable?
You might need to refresh / reconnect the table again.

  1. What is the field type of User ID in airtable? A Single line text or a Link to another record?
    What I found out in the meantime:
    If you use filterByFormula on a Link to another record type, you must send the value for the primary key of the linked record (in simple words the value you see in Airtable) NOT the id in airtable.

  2. Can you use the Java Script Console in Chrome?
    image
    Is there any HTTP error showing up? E.g. HTTP 422 indicates that there is a problem with a field type.

If you want me to screenshare with you, I can do that.

I think we need an Adalo employee to help because I don’t know how else it’s going to get fixed.

The only thing I changed was trying to add a second filter with the and() function. When I gave up on that and decided to go back to just filtering on user, now it doesn’t work unless I put the user into a text input field first. But then someone could change their username in the textbox so that is not a good solution. It was working without that workaround just yesterday.

Here are the people interested in seeing a fix to the Airtable list filter bugs:
You, me, @vjragavan, @pford, @ChristopheHK, @AJAY, @Dim, and @AddyEdwin.

Even if there aren’t that many people pushing for it, this is a really important feature to have working. It would add a lot of value.

1 Like

I guess the filter problems are related to some missing/unclear triggers to request data from Airtable. See Change in dropdown does not trigger refresh of list